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?

[cwebb365]

I just tested, got it to work using =VALUE([ID]) not sure if it works in PowerApps thou, so that should get you to next step to see.

[PowerAppsJunkie]

Ok I'm not sure what is wrong here....It is still not pulling the records > 2000

I added a calculated ID field as well as one I populated with MS Flow called RealID

So I have 2 ID fields now CalcID and RealID which match the ID but when I use this statement the collection comes back completely blank.  I've refreshed my data source and the fields show up but when I assign this to a button where it should collect this data past > 2000 records it does not do anything.  The first line on a button to collect the first 2k items works fine.

ClearCollection(col1, Filter('SPTestList', RealID >=1 && ID <=2000))

ClearCollection(col2, Filter('SPTestList', RealID >= 2001 && ID <= 4000))

This is driving me bonkers!  

[PowerAppsJunkie]

I do notice on this statement it shows a delegation warning.  I tested both the Value(CalcID) and ID both showed the blue underline however the RealID column did not but showed blue lines under the other parts of the statement I included it.  So maybe you can't use a calculated field? I had RealID show up without the delgation warning and when I replaced both sides it pulled in all the information.

I would be nicer to use a calulated field though because then I wouldn't have to have an extra flow so if you know how I would do that or if it's possible.  Also the first answer didn't mention delgation problems but I guess ID does not delegate as you said.

[cwebb365]

Yeah. Calc columns isn’t work before but they added some new delegation so thought it might be worth a shot to see if it worked now.

[cwebb365]

Yeah was just about to say use RealID on both sides.

[PowerAppsJunkie]

Yup that works and I can grab everything I need. Always curious to see or find if others ways can be had etc...

How many collections can you pull with 2k records? Is there a limit to what you pull down so say I had 10k items just do 5 collections?

Also if I need to edit this data and patch it back to a SP list. Do I update the collection then patch the data or can I patch the data then just recollect?

I'm new to working with collections so don't know all the ins and outs of it.

[cwebb365]

You can actually pull all the records to s single collection. That’s not limited to 2k. You can technically get fancy with a forall and automate pulling all the records into a collection without manually doing it all. I had a test working at some point that did it. Can’t recall the collection limit but it’s large.

[PowerAppsJunkie]

So instead of doing multiple collections and then merging them it's possible to just loop through the records in a list and add them to a single collection?

Is one faster than the other?

[cwebb365]

Easier to manage. Try it. Do clear(col1)

Then do collect instead of clear collect with your same ones and run it. Pretty sure you’ll get all 2000+ records in the col1

[v-xida-msft]

Hi @PowerAppsJunkie ,

Please also consider take a try with the following alternative solution, then check if the issue is fixed:

https://blog.coeo.com/davidmorrison/2017/06/21/powerapps-500-record-limit-delegation-and-how-to-work...

Best regards,