Filter a SharePoint list with more than 2000 records

[PowerAppsJunkie]

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?

[yashag2255]

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!

[v-xida-msft · Accepted 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:

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,

[PowerAppsJunkie]

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

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]

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]

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]

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]

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]

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