Power Apps & SharePoint | How to deal with >5K lists

Sometimes we need to work with large lists in Power Apps like Products or Customers. And when we work on mobile we maybe like to put those items into a collection and store it on our device for performance reasons.

If this is our use case then we run into the challenge of Delegation limitation for one and if we would be able to fix this we need to find a way to optimize the load performance, right? I fix both in my video on YouTube.

In this video, I first share with you my approach to handle delegation issues and how to update existing lists with this approach. In short, the approach is to add an extra number column and update this with the ID. Where the ID column has delegation limitation if we want to filter on greater than, a number column has none. We use a simple formula to loop through the items and update this number column.

When we have finished this task we are able to retrieve as many records as we like into our App, by using the Filter formula with a greater than condition. We use ClearCollect() formula on the first line to retrieve the first batch of 2.000 items. Then on the second line, we use the ForAll() and the Sequence() formula to determine the number of extra batches we like to retrieve. Inside of the ForAll loops we use Collect() and 'greater than' condition to add items to our collection, each loop 2.000 items. For example, after five loops we now have 12.000 records in our collection.

Of course, we could leave it at that, but then every time the app starts we will collect all the items. Performance-wise this will be not very effici?nt, surely when we do this on a mobile connection. So the second part of the video I share my approach on how to optimize your app performance even further.

The approach I use is similar to my approach in an earlier on how to implement data from a third-party system. Here also we will store all the records from our list as a CSV formatted string inside of a multiline column that lives in another list. On each day, when the app starts the first time, we collect all the items from our list. Then use the Concat() formula to build a CSV formatted string and we update our item in the target list with today's date and the CSV string. All the other times the Power App is used during that day it will only load this one item, takes the CSV formatted string, and use some formulas to convert the CSV to the collection we need. Nice is, that even if our list grows to 20K+ records the impact of these extra items is minimal.

I hope you like to learn my approach. Please let me know.