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
Solved! Go to 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โ ), " , ")
Yes. Youโre correct.
However, you would need to split each into different tables. Have you explored security filtering instead?
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
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.
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โฆ
I agree with @Grant_Stead.
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
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โ ), " , ")
Perfect
That works
Thank You
Phil
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |