Is it possible to filter on a SharePoint list with more than 2000 records?
What I want to do is do a filter in for a column that has text like "NameA; NameB; NameC". So there are 3 names in this column that are seperated by a ";" I need to filter on a name so lets use "NameB" as an example. So that if "NameB" exists anywhere in that column it will show the list item in a gallery.
I can't use StartsWith because well Name B could exist anywhere in that column...so it could be "NameC;NameA;NameB" on one list item or it might be "NameD;NameB;NameA" in a different list item. I need to show all SP list items that could have "NameB" anywhere in that column.
The issue I'm running into is how to search anywhere in that column for "NameB" and not run into delegation issues because the list has 3,300 items in it.
So is there any work around or can I use Flow to help fix the problem?
Solved! Go to Solution.
Hi @PowerAppsJunkie ,
Currently, the 'in' operator is not delegable within SP Connector, it means that you could not delegate the data process from your app to your SP List data source, instead, you could only process data locally.
In default, you could only process 500 records locally at most, you coud change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 in Advanced settings of App settings, then you could process 2000 records locally in your app.
As an alternative solution, you could consider bulk-load your SP List records into your app. Please check and see if the alternative solution mentioned within the following thread:
On your side, please set the OnStart property of the App to following:
Concurrent( ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000)), ClearCollect(col2, Filter('YourSPList', ID >= 2001 && ID <= 4000)) ); ClearCollect(MergedCollection, col1, col2)
Then modify your Filter formula as below:
Filter(MergedCollection, "NameB" in NameColumn)
then re-load your app (fire the OnStart proeprty of the App), then check if the issue is solved.
Note: Please make sure you have changed the "Data row limit for Non-delegable queries" option to maximum value -- 2000 in Advanced settings of App settings already.
Best regards,
Hi @PowerAppsJunkie ,
Currently, the 'in' operator is not delegable within SP Connector, it means that you could not delegate the data process from your app to your SP List data source, instead, you could only process data locally.
In default, you could only process 500 records locally at most, you coud change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 in Advanced settings of App settings, then you could process 2000 records locally in your app.
As an alternative solution, you could consider bulk-load your SP List records into your app. Please check and see if the alternative solution mentioned within the following thread:
On your side, please set the OnStart property of the App to following:
Concurrent( ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000)), ClearCollect(col2, Filter('YourSPList', ID >= 2001 && ID <= 4000)) ); ClearCollect(MergedCollection, col1, col2)
Then modify your Filter formula as below:
Filter(MergedCollection, "NameB" in NameColumn)
then re-load your app (fire the OnStart proeprty of the App), then check if the issue is solved.
Note: Please make sure you have changed the "Data row limit for Non-delegable queries" option to maximum value -- 2000 in Advanced settings of App settings already.
Best regards,
@v-xida-msft
I used the collection as you put in but it's only grabbing the first collection of 2000 records. I don't get anything from the 2nd collection and when I preview it via view it shows the collection as blank.
It's acting like it's not pulling in the records.
I created a button to pull them in manually into a collection like this:
ClearCollect(col2, Filter('YourSPList', ID >= 2001 && ID <= 4000))
But when I click that it doesn't pull any records in but when I click the button when changing it to this...it works...
ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000))
Any ideas?