Data Partitioning questions

[Phil_Waite]

Hi,

I have 25k outlets/customers in my main table and another 25k in another. I don’t use the second table as the outlets are in counties that we aren’t interested in at the moment. I found that removing the unused ones greatly inproved speed and stopped the app “snapping” so much.

I would like to make the app evan more efficient. Can I split the outlets into tables by county and use data partitioning to direct the app to the ones in use?

Each user on the app can assign them selves counties in a User Assign Table at the moment.

Unique Id	User Id	Outlet Rating	Counties To Include
Call List 1	Bob@email.com	Special Attention , Prospect , Inactive , Inactive But Revisit , Active HQ , Active , Prospect with contact made , HQ , Barred	Aberdeenshire , Avon , Berkshire , Dorset , East Sussex , Essex , Hampshire , Kent , Middlesex , Surrey , West Sussex , Wiltshire

This is used to create a slice that creates a call list at the moment so the user can customise call lists by county, outlet rating etc.

Can data partioning be used to stop the app searching counties that are not allocated to the user?

I can create a button/user input on the app that would allow the user to maually switch between tables that the app is pointing to for instance - if user input cell contains “Kent” then use patition/worksheet “Kent”.

But can appsheet point at more than one table/tab on my googlesheet as though it is one and can the combination and number of tabs be determined by an expression.

For instance I would like the app to only use partitions that match the counties in the list by user id in the above table.

So Bob@email.com is using an app that is only searching data in these counties - Aberdeenshire , Avon , Berkshire , Dorset , East Sussex , Essex , Hampshire , Kent , Middlesex , Surrey , West Sussex , Wiltshire.

Can I just split the main table (with the exact same columns) into another worksheet/table and set up the data partitioning? Will all my expressions work exactly the same as though the moved data is still sitting in the main table?

Otherwise have I misunderstood using data partitioning?

Total number of outlets I have is 71K so it would be a pretty big table/worksheet to have them all on there.

Thanks

Phil

[Grant_Stead]

Yes. You’re correct.
However, you would need to split each into different tables. Have you explored security filtering instead?

[Phil_Waite]

Hi Grant,

I haven’t as it looks as though that limits what a user can see rather than which rows are being searched and therefore taking time.

Thanks

Phil

[Grant_Stead]

You may have a misunderstanding.
The security filter will remove those records during a sync so that they are not even sent to the device. Thus, the app itself will move way faster… Sometimes if you play with your sync settings, and your app is designed for it you can leverage them which is way easier. (It’s worth exploring before moving toward the complexities of data partitioning.)
I utilize data partitioning extensively.

[Grant_Stead]

One more note, if they have multiple counties then they would have to be flipping around a lot, data partitioning only works well one at a time for these types of things…

[Steve]

I agree with @Grant_Stead.

[Phil_Waite]

Hi guys, that’s great.

Thank you. I have had a play with it and it has made things a lot faster. I’m trying to determine which rows each user can see based on a list of counties with the following expression.

IN( [County], SELECT( User Assign Details[Counties To Include], [unique id]=“call list 5” ) )

Looking up the list of counties from this table

Unique Id	User Id	Outlet Rating	Counties To Include	Call Next Run
Call List 5	phil@listersbrewery.com	Active , HQ , Active HQ	Aberdeenshire, Hampshire

The problem is that it only works with one entry on the counties to include column - in this case aberdeenshire. When I add more nothing is shown.

I want to user to be able to choose what counties they can see

thanks

phil

[Steve · Accepted solution]

This is because the Countries to Include column value appears to be a List or EnumList type. SELECT() produces a list of column values. When the column values it provides are themselves lists, the result is a list of lists, which doesn’t behave as you might expect. What you need to do is “flatten” the list of lists into a single list (i.e., merge the lists). To do so, replace this:

SELECT( User Assign Details[Counties To Include], [unique id]=“call list 5” )

with this:

SPLIT(SELECT( User Assign Details[Counties To Include], [unique id]=“call list 5” ), " , ")

[Phil_Waite]

Perfect

That works

Thank You

Phil