Current Microsoft MVP Awardees

MS Access™ MVP Awardee from July, 2007 through June, 2019


Active Topics

Screen shot of Blog

Tricking PowerApps into Loading 10,000 Records -- Part One

Publish Date -- Sep 04, 2021


We all know the importance of minimizing the number of records loaded into an active form. Ideally that number is "1", i.e. you want to work on one item at a time, so loading additional records is potentially very wasteful. Of course, some tasks require a list of relevant records from which to choose.

We handle that with pre-filtering forms that offer list boxes and combo boxes from which one record is selected for focus.

PowerApps takes that idea to the extreme. In the default setup, you can retrieve no more than 500 records by default, or up to 2,000 if you change that setting. Even if you have 10,000 records, PowerApps will ignore everything over 2,000--with no indication that there are additional records not retrieved.

I'm going to show you how you can get around that limit, if you really really have to, and want to incur the loss of performance it will probably entail.


We all know the importance of minimizing the number of records loaded into an active form. Ideally that number is "1", i.e. you want to work on one item at a time, so loading additional records is potentially very wasteful. Of course, some tasks require a list of relevant records from which to choose.

PowerApps takes that idea to the extreme. In the default setup, you can retrieve no more than 500 records by default, or up to 2,000 if you change that setting. Even if you have 10,000 records, PowerApps will ignore everything over 2,000--with no indication that there are additional records not retrieved.

I'm going to show you how you can get around that limit, if you really really have to, and want to incur the loss of performance it will probably entail.

I do understand and support the limitation on records in PowerApps. PowerApps are intended for the "low-code" development scenario. They are intended for smaller applications with limited reach and scope. They have to run well on smart devices over the internet. All of that suggests it's a good idea to impose this limit. In my first "production" PowerApps application, in fact, I settled on a technique that limits the number of records even more stringently. I get only the most recent 5 days worth of records, which usually amounts to two or three hundred.

However, some of us can't leave well enough alone. I wanted to know if I could bring in several thousand records, bypassing the default limitations. And the answer is, yes, yes you can. Here's how.

PowerApps Collections

Collections in PowerApps are somewhat distant cousins to the kinds of collections we create in VBA. In PowerApps, however, they are essentially "temporary, local tables" created within the application as a way to store and manage records during the session in which they are used. The thing is that Collections are NOT subject to the limitation on retrieving records from the actual record source.

Of course, you can only populate a collection in chunks of up to 2,000 at a time because of this limitation. But PowerApps also provides a low-code way to recursively bring in all of the records you want to retrieve, or more appropriately stated, as many records as you have the patience to load. That means you can:

  • Create a collection to hold the records once you retrieve them from the data source, which might be a SQL Azure table, for example.
  • Set up criteria that tell PowerApps how many records to expect; i.e. the total number of records in the table.
  • Recursively retrieve batches of records and add them to the Collection

The Code

If you are new to PowerApps, this will be unfamiliar code. Trust me, though, it works. You can adapt it to YOUR environment by changing the names as indicated.

// Collections

//Initialize Collections by clearing them in case there was a previous navigation to the screen

Clear(colYourCollectionNameGoesHereCount);
Clear(colIterationSpans);
Clear(colYourCollectionNameGoesHeres);
Clear(varCounter);
Clear(colInit);

//Identify the first and last Primary Key values in the table (assuming they represent the first and last records in the table)
//The code will work the same if the last entered record does not have the highest Primary Key value or if there are gaps in the sequence of Primay Key values.
//All we need to know is the largest possible number of recordss we're going to retrieve
//If the table does NOT use an Identify field for the Primary Key, you'll have to come up with a different way to find how many records are potentially in that table.

Set(
    FirstRecord,
        First(
            Sort(
             'SchemaName.tblYourTableNameGoesHere',
             PrimaryKeyID,
             Ascending
         )
     )
    );
Set(
    LastRecord,
        First(
            Sort(
            'SchemaName.tblYourTableNameGoesHere',
            PrimaryKeyID,
            Descending
             )
        )
);

//This calculation returns the number of 2,000 record retrievals we need to get all of the records in 2,000 record chunks
//We use RoundUp to account for the remaining records after taking retrieving chunks of up 2,000 each

Set(

    varIterations,
    RoundUp(
        (LastRecord.PrimaryKeyID - FirstRecord.PrimaryKeyID) / 2000,
            0
        )
    );

//Going out on a limb here; this calculation assumes that the maximum number of records will be the difference between the largest Primary Key value
//and the smallest. This calculation can produce a max count greater than the actual number of records, but should produce no less than the actual number.
//If we run a recursion expecting more records than are in the table, however, we will get nothing in the final chucnk, so the only downside is the potential waste of processing time that involves.

Set(
    varMaxYourCollectionNameGoesHereCount,
        (LastRecord.PrimaryKeyID - FirstRecord.PrimaryKeyID)
    );

//Now we create our first collection using the variables we've set so far.
//It's a dummy collection used only as a filter in later steps. The collection will consist of the digits 1 through 10.
//You may need or want to adjust this number to accommodate your own data sets.

ClearCollect(colYourCollectionNameGoesHereTemp,[1,2,3,4,5,6,7,8,9,10]);
//This step uses the first two collections to create a third collection containing the Numbers for each of the chunks we'll need later, i.e. Chunk 1, Chunk 2, etc.
ForAll(
        Filter(colYourCollectionNameGoesHereTemp,
        Value <= varIterations),
        Collect(colYourCollectionNameGoesHereCount,
        Value
    ));

//Next we retrieve the starting and ending Primary Key values for the 2,000 record chunks we'll retrieve in our final collection.

ForAll(
    colYourCollectionNameGoesHereCount,
        Collect(
        colIterationSpans,
            {
            Value: Last(
                FirstN(
                colYourCollectionNameGoesHereCount,
                CountRows(colIterationSpans) + 1
        )
    ).Value,
    min_Item: ((varMaxYourCollectionNameGoesHereCount/varIterations) * Value) -(varMaxYourCollectionNameGoesHereCount/varIterations),
     max_Item: ((varMaxYourCollectionNameGoesHereCount/varIterations * Value))
         }
     )
    );

//And finally we have all the bits and pieces we need to populate a collection with all of the records in the source table.

ForAll(
    colIterationSpans,
    Collect(
        colYourCollectionNameGoesHeres,
        Filter(
        'SchemaName.tblYourTableNameGoesHere',
        PrimaryKeyID >= min_Item && PrimaryKeyID <= max_Item
        )
     )
);

In Part Two, I'll show you how I implement this procedure in the OnVisible property of a PowerApps screen. In the meantime, if you are comfortable with PowerApps, give it a try.


Image by StartupStockPhotos from Pixabay

Feedback is welcome and appreciated. ghepworth@gpcdata.com

©Copyright 2010- Grover Park Consulting. All Rights Reserved.
Free Resources and Full Working Demos for Microsoft Access Database Design and Development