cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PowerAppsJunkie
Level: Powered On

Filter a SharePoint list with more than 2000 records

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter a SharePoint list with more than 2000 records

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:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

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,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
19 REPLIES 19
Super User
Super User

Re: Filter a SharePoint list with more than 2000 records

Hi @PowerAppsJunkie 

 

Can you try to use either of the below formulas?
 
Expressions:
Filter(SPListName, "NameB" in ColumnName)
Or
Filter(SPListName, "NameB" in Split(ColumnName, ";"))
 
These expression works for Text type column, if this differs please share more info about the column type.
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Community Support Team
Community Support Team

Re: Filter a SharePoint list with more than 2000 records

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:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

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,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PowerAppsJunkie
Level: Powered On

Re: Filter a SharePoint list with more than 2000 records

@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?

cwebb365
Level 10

Re: Filter a SharePoint list with more than 2000 records

ID is not delegable, so you're only getting the first 2000 id's no matter what you put in there. You have to go in an create a flow that takes your ID's and replicates it to a number column that you use in the list.
PowerAppsJunkie
Level: Powered On

Re: Filter a SharePoint list with more than 2000 records

So I create a flow when a new item is added to the list to lookup the ID of the item ID and copy it to a new number field? Then in the clearcollect grab that column instead?
cwebb365
Level 10

Re: Filter a SharePoint list with more than 2000 records

Well, you'll have to have a job run through all the records that exist now first so you can update the existing records to the new column as well. But you will use that RealID lets call it column, as your filter so RealID >= 2001 && RealID <= 4000 etc.
PowerAppsJunkie
Level: Powered On

Re: Filter a SharePoint list with more than 2000 records

Yup I was just writing a flow to do the ID's over to a new column now and then implementing a new flow for any new items added.

Will test as soon as the flow finishes but sounds like it should work as intended.
cwebb365
Level 10

Re: Filter a SharePoint list with more than 2000 records

Now that Lookup columns and choice columns are delegable, I don't know if Calculated columns work, but you could try to create a calculated column first and just try it so that the calculated column called CalcID and use [ID] as it's value. Might work, I'd try that to see if it's updated to use delegation real quick, if not, then you'll unfortunately have to go the other route.
PowerAppsJunkie
Level: Powered On

Re: Filter a SharePoint list with more than 2000 records

Ok I tried a CalcID field with [ID] and in the formula it says invalid argument type expecting a number value.

I did calculated field as a number value and a a single line of text but both give me the error

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 232 members 5,015 guests
Recent signins:
Please welcome our newest community members: