Answered

Filtering a SharePoint list with more than 2000 records

(0) Share

Report

Posted on by danielrtovar

Good day,

I am filtering a data table numeric field based on a SharePoint list with about 6000 records. In configuration I already changed the data row limit parameter to 2000. I discovered that a solution can be to work with collections and join them and then filter the joined collection. For that reason in the 'OnSelect' parameter of the app I added the following collections:

Concurrent(
ClearCollect(MyCollectionSP1;Filter('SharePoint list name';ID<2000));
ClearCollect(MyCollectionSP2;Filter('SharePoint list name';ID >=2000 And ID<4000));
ClearCollect(MyCollectionSP3;Filter('SharePoint list name';ID >=4000 And ID<6000));
ClearCollect(MyCollectionSP4;Filter('SharePoint list name';ID >=6000 And ID<8000));
ClearCollect(MyCollectionSP5;Filter('SharePoint list name';ID >=8000 And ID<10000));
ClearCollect(MyCollectionSP6;Filter('SharePoint list name';ID >=10000 And ID<12000))
);;
ClearCollect(MyCollectionUnified;MyCollectionSP1;MyCollectionSP2;MyCollectionSP3;MyCollectionSP4;MyCollectionSP5;MyCollectionSP6);;

However, the second collection "MyCollectionSP2" is not saving records with ID greater than 2000. It is always displaying the first 2000 records from the SharePoint list even if they are split into collections. How can I display more than 2000 records in the collections based on the SharePoint list?

I appreciate any pointers on this or another solution if you have any.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(5)

Report
Copy link

Link copied!
@danielrtovar

ID is not delegable except when using the equal operator. So, since you are using greater than and less than, you will not get anything more than 2000 records.

You will need to either filter by another column that is delegable, or reconsider your need to load every record into your app. Most delegation issues can be worked around by using a narrowing filter which is delegable and then filter the results of that with non-delegable operations.

As a quick example of a narrowing filter - let's say the list contains invoices for clients. Narrowing filters can be things like a date range, a client ID/name, invoice types, invoice status, etc.

So, doing this:

With({_items: Filter(invoices, InvoiceDate >= DateValue("1/1/2021") && InvoiceDate <= DateValue("12/31/2021"))}, //Delegable Search(_items, "customerName", CustomerName, CompanyName) // non-delegable by itself )

The first part of the With statement would contain the results of the delegable filter on the dates. The Search function (which is not delegable to a datasource) uses the _items (the results of the narrowing filter) for its table source.

So, in the above, if the narrowing filter returns less than 2000 records, all works fine. If there would be more than 2000, then consider another criteria for narrowing.

I hope this is helpful for you.

Was this reply helpful? Yes No
danielrtovar 7 on at

Like
a
(1)

Report
Copy link

Link copied!

I did it with a date field and it worked perfectly. Thank you very much for your help.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@danielrtovar

Happy to help!

Was this reply helpful? Yes No
Power5 on at

Like
a
(0)

Report
Copy link

Link copied!

What property did you place this code in?

Was this reply helpful? Yes No