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!

How-to-collect-over-2000-records-in-PowerApps

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 be posted regularly. 

Additional Resources

Do It For Me
See it in a real world example how-to-work-offline-with-powerapps

Share the knowledge!

Paul Rodrigues

Business Analyst with 20 years of IT experience creating practical solutions. I love to automate business processes through the use of technology while making the end users work easier. My current favorite tools are PowerApps, Power BI and Power Automate. #PowerAddict

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *