“A good app shouldn't collect a lot of data.”

Following a request from a customer, we debated this unwritten rule. After spending hours looking for the best solution for them, we decided to go against the grain. Still, we had to find the right balance between uploading all the data from the start on the one hand, and having users request smaller batches of data a few times an hour on the other.

This exercise gave us a solid understanding of how to best handle large datasets. We're happy to share our solution with you.

Let's start with an alternative approach from my colleague Hans Santens. He wrote an interesting article about parsing large datasets with Power Automate that's definitely worth a read. He explains how you can use Power Automate to collect large datasets (+2K) and load them into a Power App.

I took the same route during development, but then chose a different approach going forward. Eventually, the Power Automate solution became slower, not just because of the amount of data, but also because of every extra column added to the query.

We ran into a number of tricky issues we wanted to solve:

• Extra short round-trip time to and from Power Automate on top of Power Automate's own execution time. Every second counts 😊

• Everything got slower with every extra record and column, because the returned JSON had to be typecast per column (in a loop).

• Many lines of code were needed to typecast all the fields.

• It was difficult to work with more complex data types.

• The Power Automate script required extra maintenance.

• We still had to create separate collections matching the fields in SharePoint for patching purposes.

That's not to say my colleague's approach isn't worth considering. It's definitely a valid solution for collecting larger datasets.

The approach I want to focus on here works only in Power Apps and collects +30K records in a single collection. Network speed can affect how fast you're able to collect the data. I'd recommend restarting the app during a break, just to be safe.

For my solution, I used a loop that retrieves a maximum of 2K records (the default maximum limit for a collection) for each run of the loop.

First, I tried filtering the list based on the ID of each item. You need an in-between variant, or a greater-than/less-than equivalent, but '>=' and '<=' trigger a delegation warning. So I kept searching, not willing to risk missing data.

I decided to add my own integer value column per record. All records with an ID < 2000 got 1, the next group of 2K records got 2, and so on. And that's the key to solving this problem – while shrinking it at the same time.

With this integer value, you can use '=', which avoids the delegation warning. However, you do need to calculate the value when you create the record, via an extra patch/edit.

You have two options here:

There are two key topics we need to focus on:

• Making sure every record is saved with the correct integer value.

• Retrieving the data via the integer value using loops.

In Power Apps, a collection retrieves a maximum of 2K records. By using 'Collect' after 'ClearCollect', you can easily add more rows to the same collection.

For example:

• 2K customers in London

• 3K customers in Paris

• Total = 2.5K rows in colCustomers

Now that we know this, we need to find a way to repeat this as many times as needed.

To get the data, we need to know the highest integer value in the list and build a loop from 1 to that number. I named my column 'dg' (DataGrouping).

You'll see that I sort by ID (descending) and request the first 'dg' value, which is the largest based on the sorting. Using 'Max' on 'dg' also triggered a delegation warning.

Set( varLastDG, First( Sort( <SP List>, ID, SortOrder.Descending ) ).dg )

Now build the loop (ForAll). Use 'Sequence' to create a list of numbers from 1 to varLastDG.

ForAll( Sequence(varLastDG), Collect( <collection name>, Filter( <SP list>, dg = Value, Department = "R&D", Active = true ) ) )

The extra filters on 'Department' or 'Active' can reduce the number of returned records and speed up the entire process. Not every loop will return exactly 2K records – it might return none at all.

Add your collection to a grid to visualize the result. That's all you need to collect your data!

Now you need to make sure every record gets the correct integer value. You also need to make sure you don't add the same value more than 2K times.

For this solution, I patch a new record the standard way. However, this means you don't know 100% for certain which ID the record will get. That's why the newly created record is stored in a variable. You can retrieve the new ID from this variable and then patch (update) the record with that value.

Use a patch action to add or update a record. Save the result of the patch action in a variable and retrieve the ID. For an update, you don't need to patch the integer value. The record gets its integer value when it's created. That number never changes.

// Create the record and store it in a variable

Set( varSavedRecord, Patch( <SP List>, Defaults(<SP List>) { Title: txtWPNumber.Text, ....... } ) );

// Retrieve the ID of the record used in the patch

Set( varPatchedID, varSavedRecord.ID );

// Calculate your integer value

Set( varNewDG, Int(varPatchedID / 2000) + 1 );

// Patch the integer value into the record (not needed when updating a record, since it already got its ID when the record was created)

If( varEditState = "New", Patch( <SP List>, LookUp( <SP List>, ID = varPatchedID ), {dg: varNewDG } );

With this integer value in the 'dg' column, we have a simple way to collect the data.

• We look for the largest 'dg' and build a loop from 1 to that number.

• We retrieve all records whose 'dg' value = the loop value ('=' can be delegated)

I've already mentioned the reduction in the number of patches twice, but by now I hope the benefits of this approach are clear:

• No extra tool (Power Automate) to maintain.

• Just a few lines of code.

• No typecasting needed.

• Collections are the same as the SharePoint list.

Now you have two options to choose from: use Power Automate and retrieve all the data at once, or use the Power Apps approach with a loop.

Have fun exploring! And if you need support: my colleagues and I are happy to help 😊.