How to Collect Over 2000 Records in PowerApps
There are often times where we will need to load into a collection more records than PowerApps allows you to collect in one collect call. In this post I will show you how to collect over 2000 records in PowerApps. Continue reading and you too will become an Office Power User!
Set in PowerApps Studio
By default PowerApps limits the amount of records returned to a collection to 500 records. We can change this default to 2000. You change the maximum by going to settings and then advanced settings. In the data row limit for non-delegable queries change the value from 500 to 2000.
Clear Collect First Round
In this first step we will use a clear collect to collect our initial data into the collection. Lets put this in the Apps OnStart.
- OnStart:
ClearCollect(
colTranslations,
Filter(
‘[dbo].[tblWPOTranslations]’,
ClientID = varClientID
)
) ;
Collect Second Round
In this next step we will check to see if the record count is equal to 2,000 records. If it is we will do another collect with a filter shown below.
- OnStart: Add this below the code above
If(
CountRows(colTranslations) = 2000,
Set(
varIDForNextTransRun, // Set this variable to get a the last ID saved into the collection.
Max(
colTranslations,
ID
)
);
Collect(
colTranslations,
Filter(
‘[dbo].[tblWPOTranslations]’,
ClientID = varClientID && ID > varIDForNextTransRun
)
)
) ;
Collect Third Round – Repeat as Needed
In this step we will repeat the steps from the previous step and repeat as many times as needed.
- OnStart: Add this below the code above.
If(
CountRows(colTranslations) = 4000,
Set(
varIDForNextTransRun,
Max(
colTranslations,
ID
)
);
Collect(
colTranslations,
Filter(
‘[dbo].[tblWPOTranslations]’,
ClientID = varClientID && ID > varIDForNextTransRun
)
)
)
Now you know how to collect over 2000 records in PowerApps! Please leave your feedback. I hope this article has been helpful for you so bookmark this blog as new articles are posted regularly.
Additional Resources
Do It For Me
See it in a real world example how-to-work-offline-with-powerapps
