The power of Sequence() when used to collect >2000 records into your app

[Anonymous]

Getting more than 2000 records into our apps from a single data source has always been one of the most asked questions by PowerApp developers. Whilst it’s generally accepted that you really should try to avoid doing this, developers still want to be able to do it for many different situations.

This post aims to give developers a different way of doing this dynamically by leveraging the power of the Sequence() function. I personally love this function! I think it’s true potential has yet to be  discovered but with so many smart app developers out there I’m sure we’ll unlock all of it’s secrets soon enough 😊.

So, let’s get started!

Notes:

• This code works with a NUMERIC type column, which isn’t the ID column in SharePoint
• IDNumber shown in the code below is the name I’ve given to my NUMERIC column
• I tested this using a SharePoint list with 3307 records and Max IDNumber = 3309
• Replace yourListName with the name of your data source
• Must have your data row limit for non-delegable queries set to 2000 ie File --> Settings --> Advanced Settings

Step 1

Find number of 2000 record batches needed

// Get the number of 2000 row batches needed to loop through
With(
    {
        wID:   First(Sort(yourListName,IDNumber,Descending)).IDNumber 
    },
    Set(vNum2000s,
        Switch(Mod(wID,2000),
            0,Round(wID/2000,0),
            RoundDown(wID/2000,0)+1
        )
    )
);

Result: 2, perfect! I have 3307 records, so I need 2 lots of 2000 record batches to collect my data.

You’ll notice that this code generates a delegation warning, but you can ignore it. The reason being is because we are ‘flipping’ the data source on its head so that the record with the largest IDNumber is at the top, then we are collecting that number. When we do this we will always get the IDNumber we want, we just wouldn’t be able to get the lowest IDNumber in this way, hence the delegation warning.

We Switch between Round() and RoundDown() above to cover all record number cases. You can check if this is needed yourself but I did a fair bit of testing to get this bit just right! Thanks goes to @WarrenBelz who helped out with recognizing the importance of needing both here.

Step 2

Create Start-End values

// Create Start-End values that each batch loops through using Sequence()
// Rename column "Value" (generated by Sequence) to "Start" to keep things
// consistent, and use AddColumns to attach the "End" column

ClearCollect(
    colSeq,
    AddColumns(RenameColumns(
            Sequence(vNum2000s,0,2000),
            "Value",
            "Start"
            ), "End", Start + 2000
    )
);

Result: Table of Start / End values that match my list size of 3307 records and my vNum2000s value of 2, ie

The above creates a collection with ‘Start’ and ‘End’ values that we can now use in our ForAll loop.

Step 3

Collect our records

// Empty Collection that will be filled
// This is best practice before using ForAll to Collect() records

Clear(colSeqALL);

// Loop through the Start-End table collecting records
ForAll(
    colSeq As StartEnd,
    Collect(colSeqALL,
            Filter(yourListName,
                IDNumber > StartEnd.Start && IDNumber <= StartEnd.End
            )
    )
) 

Result: All of our records collected! Avg Time to collect 3307 was approx. 6.5 sec on my connection.

This code collects the data in batches. Each batch is, at most, 2000 records so we avoid delegation issues. The code uses Start and End values to Filter the data source then pull in those records. The number of times it does this is set by the number of records in the collection colSeq.

Pretty cool huh? 😊

You can also use AddColumns on the final collection … and inside the ForAll loop … if you want, like this:

// Loop through the Start-End table collecting record, AddColumns if required
ForAll(
    colSeq As StartEnd,
    Collect(colSeqALL,
        AddColumns(
            Filter(yourListName,
                IDNumber > StartEnd.Start && IDNumber <= StartEnd.End
            ),
            "NewColumn",
            false
        )
    )
)

I haven’t tested using RenameColumns or DropColumns inside the loop but I’m reasonably confident they would work as well.

After showing this code to one of my esteemed PowerApps colleagues @WarrenBelz , he did his thing using the With() function to clear the delegation warning and provide a different way of reading the code. You can check out his code re-write here .

So there you have it. Dynamic record collecting large datasets using Sequence and ForAll.

I’d love to hear from anyone who has a crack at using this code. Let me know in the comments how it performs on your datasets.

Cheers!

Eelman