One thing I noticed is "IN" statement works great when you have data already loaded in collection!
I have data loaded in CachedInstruments4 and I am using checkbox check, uncheck to filter records based on countries selected.
Collect(CachedInstruments4,Filter(CachedInstruments3,Country in ThisItem.Country))
Remove(CachedInstruments4,Filter(CachedInstruments4,Country = ThisItem.Country))
Yes, indeed that is the case for the in operator as a test of membership.
We really need to get this operator properly delegated when used a test of membership.
I have written this post, but have had no luck in getting clarification yet:
Below are some metrics with my new method. The internet connection used is not as quick as my school internet which my users use.
5 entities = 10,803 records total = 34 iterations of up to 500 records = about 22 real groups of 500
Elapsed time trials: 44s, 39s, 40s, 44s, 42s
These tests were run on the same network as a previous test I ran. Below are some comparisons:
Therefore, the changes to PowerApps version 2.0.590 and this method have up to a combined 42-65% improvement in performance. Since I no longer need to write a column identifying which block of 500 a record belongs to, there are also improvements in writing. That represents about 50% less writing time since I previously wrote new records twice: once for the record with the main information, and second for calculating n as RoundUp(PrimaryId/500,0).
Although there is a performance improvement, I would still judge 40+ seconds of caching a long time for end-users even if it's a one time block at the start. If it is 40s right now, then with more usage of the app, the time will gradually increase to unsustainable levels. If Sum() or CountRows() could operate on any one of the entities in its entirety or if there were a one-click Cache() function, I could easily cut out at least 10s or more.
thanks for the update, very useful.
yesterday I added this idea, please upvote:
not sure it will help, but at least they cannot say we did not ask
I am using an excel file as a database in powerapps and i am trying your logic but it is not working, powerapps doesnt read the content where recordid is more than 500. It is only showing me 500 records only. Please help!!! Thanks.
Excel is not a delegable data source (yet) so that you will only obtain the first 500 records.
See here for some ideas:
Hoping for some help here. I have a SP list with just over 1000 items. OnStart, I would like to filter to only pull in items if the Date field is this year or last year. Is this possible?
The best way to have your cake and eat it (i.e. filter and have the filter operation delegated) would be to create a calculated column in SharePoint as Year([Date]), then use this column (Let's call it DateYear) to filter. So you can have this expression in the OnStart:
ClearCollect(MyFilteredItems, Filter(MySpList, DateYear=Year(Today()) || DateYear=Year(Today())-1))
If this is not delegated because of the indeterminacy of Today(), then try this in the OnStart:
Set(CurrentYear, Year(Today())); ClearCollect(MyFilteredItems, Filter(MySpList, DateYear=CurrentYear || DateYear=CurrentYear-1))
Please let me know how you get on