cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pwrappr01
Helper V
Helper V

How can I efficiently load 10,000 items from a SharePoint List into a Collection?

I have a large SharePoint List that stores over 10,000 items. I want to load all 10,000 into a collection for use in a drop-down list.

 

I would much prefer to be able to filter the choices in the drop-down by pulling directly from the Source List, but I keep running into delegation issues and am only able to see the first few items.

 

The Collection is going to be filtered by other drop-downs, so the final lists will be somewhat manageable, but I need a way to load all of the data so users are not missing items.

 

 

28 REPLIES 28

@yashag2255,

 

I just noticed that the ListItems collection includes some duplicates that need to be removed. How can I correct this? Here is the code I modified based on our previous discussion:

 

ClearCollect(LoopCount,FirstN([0, 2000, 4000, 6000, 8000, 10000, 12000],RoundUp(First(Sort('Item Tracker',ID,Descending)).ID/2000,0)));

Clear(ListItems);
ForAll(LoopCount,Collect(ListItems,Filter('Item Tracker',ID > Value)));

@pwrappr01  Do you have duplicates in your SP list or is it getting collected inside the collection?

@yashag2255,

 

I just verified my SP List to ensure there were no duplicates there.

Rens_
Frequent Visitor


@pwrappr01 wrote:

@yashag2255,

 

I just noticed that the ListItems collection includes some duplicates that need to be removed. How can I correct this? Here is the code I modified based on our previous discussion:

 

ClearCollect(LoopCount,FirstN([0, 2000, 4000, 6000, 8000, 10000, 12000],RoundUp(First(Sort('Item Tracker',ID,Descending)).ID/2000,0)));

Clear(ListItems);
ForAll(LoopCount,Collect(ListItems,Filter('Item Tracker',ID > Value)));


 

Thanks for your solution. 

For SharePoint this seems to be working, but it isn't for Excel.

 

 

is it still working? ID is giving delegation warning now and count limited to 2000.

Odiseo55
Frequent Visitor

I don't know what I'm doing wrong, but it didn't work, I only managed to get 2000 item ...

 

Put the code in App-Onstart: 

 

ClearCollect(loopC,FirstN([0,2000,4000,6000,8000,10000,12000,14000,16000],
RoundUp(First(Sort(ShipToList,ID,Descending)).ID/2000,0)));

Clear(ItemShipTo);
ForAll(loopC, Collect(ItemShipTo, Filter(ShipToList, ID > Value)));

 

Your help please!

 

 

Anonymous
Not applicable

I am running into the same issue that @Odiseo55 is having.  Would love to hear if anyone was able to figure that out!

I have tested both methods to load lots of SharePoint items, note that I have kept the 500 item limit in settings for the purpose of testing, but you should change this in your apps to 2000.


Method 1

 

Concurrent(
            ClearCollect(col1, Filter(SPlist, numID >= 1 && numID <= 500)),
            ClearCollect(col2, Filter(SPlist, numID >= 501 && numID <= 1000)),
            ClearCollect(col3, Filter(SPlist, numID >= 1001 && numID <= 1500)),
            ClearCollect(col4, Filter(SPlist, numID >= 1501 && numID <= 2000)),
            ClearCollect(col5, Filter(SPlist, numID >= 2001 && numID <= 2500))
);
ClearCollect(colSPlist, col1, col2, col3, col4, col5)

 


Method 2 - from @yashag2255 

 

ClearCollect(LoopCount,FirstN([0, 500, 1000, 1500, 2000],RoundUp(First(Sort(SPlist,numID,Descending)).numID/500,0)));
Clear(colSPlist);
ForAll(LoopCount,Collect(colSPlist,Filter(SPlist,numID > Value)));

 

 

Important note

Neither of these methods work with the SharePoint built-in ID column, or with a calculated column =[ID] - they will only load 500 items.

 

To load more than 500 items you have to manually populate a number column with unique numbers. You will typically do this using Power Automate: if an item is created or modified, set numID = ID.

 

johnnyshield_0-1629190039654.png

This topic has been discussed extensively on the forums, but I thought I'd lay it out again as clearly as possible for any people finding this issue for the first time.

 

Other threads

Solved: ClearCollect for more than 2000 records - Power Platform Community (microsoft.com)

500 item limit in CDM entity search filter(need to... - Page 12 - Power Platform Community (microsof...

Solved: Delegation limit with Filter SharePoint list - Power Platform Community (microsoft.com)

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,744)