cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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?

21 REPLIES 21
Highlighted

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.
Highlighted

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!  

Highlighted

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.

 

Untitled.png

Highlighted

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.
Highlighted

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

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.
Highlighted

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.
Highlighted

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

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
Highlighted

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,

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,588)