This is going to be a short post. I want to share with you my approach for overcoming the threshold called data row limit, that prevents function “Collect” to get more than the set number of items. The data source in my case is SharePoint.

I was inspired by @mrdang‘s answer to a community question, where he advised to achieve it using batches. So I created this improved and shorter version of that draft idea.

BASICS

For some of use it is a real surprise when we learn, that functions Collect and ClearCollect are actually… non-delegable. So when using a Collect(datasource) function, the maximum number of returned rows is limited by the data row limit setting:

By default, this is set to 500 and you can increase it to max. 2000.

In my case, the reason why I needed to load more data to a collection, was that later in the application I had to do complex filtering operations, which were non-delegable as well, so in the end I would never receive the data I was expecting.

PREREQUISITES

The only downside of my solution, that is caused by SharePoint, is the fact, that column ID cannot be used for range comparisons (higher, lower, etc…) because using it for such makes the whole query non-delegable (source). If you are using other sources such as CDS or SQL Server, that shouldn’t be an issue.

So for SharePoint you need to have another column, that is the number type and that preferably has the same values as the corresponding ID column values.

Unfortunately too, it is not possible to use calculated column for this purpose as even if it’s set to “Number” Power Apps still recognizes it as text and doesn’t allow to use in range comparisons.

SO HOW TO DO IT?

First you need to calculate number of iterations, that will be required to download all data from data source. To do that, I am taking ID values of the first record and the last. Next, I divide their difference by the number of rows returned by single iteration. This number cannot be bigger than a set row data limit of course 

Set(
    firstRecord,
    First('Large List')
);
Set(
    lastRecord,
    First(
        Sort(
            'Large List',
            ID,
            Descending
        )
    )
);
Set(
    iterationsNo,
    RoundUp(
        (lastRecord.ID - firstRecord.ID) / 500,
        0
    )
);
Collect(iterations, Sequence(iterationsNo,0));

Last step is to create collection, where each item is a number of an iteration. So eg. if I have 1000 records and want to get them in 500 per each iteration, I will have two iterations, so the iterations collection will contain: [0, 1]

Next for each iteration, I am calculating the lower and upper boundary, to download items with IDs between. So eg. for first iteration I will need to get items having ID between 0 and 500:

ForAll(
    iterations,
    With(
        {
            prevThreshold: Value(Value) * 500,
            nextThreshold: (Value(Value) + 1) * 500
        },
        If(
            lastRecord.ID > Value,
            Collect(
                LargeListSP,
                Filter(
                    'Large List',
                    ID_val > prevThreshold && ID_val <= nextThreshold
                )
            )
        )
    )
);

This is how it looks in action:

No, I have no idea why rows count shows 3 items less than actually is, but well… Nothing is perfect 

Find more great content here!

About the Author:

Hi!

I’m Tomasz Poszytek, Microsoft Business Applications MVP. From more than 10 years now I’m being involved in projects, which aim is production of various applications using SharePoint as a platform. My adventure started absolutely without a purpose, when in the beginning of 2000 I was given FrontPage installation disc and started experimenting with this WYSWIG tool.  

Reference:

Poszytek, T. (2021). OVERCOME 2000 ITEMS LIMIT USING POWER APPS COLLECT FUNCTION. Available at: https://poszytek.eu/en/microsoft-en/office-365-en/powerapps-en/overcome-2000-items-limit-using-power-apps-collect-function/ [Accessed: 4th March 2021].

Find more great Power Platform content here.