cancel
Showing results for 
Search instead for 
Did you mean: 
Eelman

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

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

colSeq Table example.jpg

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

 

Comments

Can this be used with a SQL View?

@tonykiefer 

Not sure but it should work on all data sources - you’ll just have to give it a try

@Eelman provided I swap out my "yourListName" with my SQL View name...should also three steps be performed at once?

 

This is how I have it setup in my OnVisible property of the form...

tonykiefer_0-1654344257680.png

and this is the SQL View structure...

 

My RecId is an int column...I actually casted it from the ModelId number which is unique.  

tonykiefer_0-1654344389324.png

 

and this is the result...

tonykiefer_0-1654344604843.png

 

So it seems to be working but it does not populate the data.

Should I change the RecId from SQL Int to SQL numeric?  Do you think that is the problem?

tonykiefer_0-1654345013608.png

 

 

@tonykiefer 

Two things

 

- what does the Yellow message say behind the first image you posted

 

- are you trying to use this on a View of only 1000 records? Can you check what’s in the colSeq collection

Hi @EddieE....The error message says "You are using one of more implicitly shared connections.

 

Also...I was only showing the top 1000 records of the SQL View.  The View actually has nearly 20,000 record that I need to use.

@tonykiefer 

And the colSeq collection?

 

After running the code can you post an image of what gets populated in this collection 

Hi Eddie,

 

After running the code the colSeq collection just shows the Start and End cols with no data.

 

Likewise the the same with colSeqAll.  Only the column names appear as in the image provided above.

@tonykiefer 

Ok, thanks.

 

Because colSeq is empty this means that vNum2000s is Blank or zero, can you check this value.

 

This may mean 1 of 2 things:

1. MyRecID isn’t of data type Numeric

or

2. You aren’t really accessing the SQL view

 

On the last one, are you able to display the SQL view data in a gallery ? Simply make the view name the gallery Items to check this. If you cannot display the view then there’s something wrong with the connection 

Hi @EddieE. thank you for the insight.  I do think I am making the connection to the view otherwise the collection would not have been able to identify the column names...even though the collection has no data.  I will examine the MyRecID to make sure it's numeric and not INT.  I know I changed it to numeric but I may have to drop the column and re-add it...or create the view from scratch.  One way or another I think I can get it to work.

 

Thanks again for all your input!

@tonykiefer 

No worries, happy to help.

 

Let me know if you figure it out because I’d be interested to know if it’s a data type issue - I don’t have access to a SQL source to test this.

Hi @EddieE

I'm doing some research on the "Numeric" data type.  Officially, we have bigint, int, smallint, and tinyint...this of course excludes floats, decimals, bits and so on.  There is also a "Numeric" type...but to use the Numeric type you need to specify size constraint for fixed precision and scale.  So when you say make sure the column is "numeric"...do you mean int?  If you mean specifically "numeric"...what are the size constraints for numeric specific to powerapps.  I'm asking because it seems if we need to use "numeric" then it must be because of additional limitations of Powerapps.