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

[pwrappr01]

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.

[pwrappr01]

@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)));

[yashag2255]

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

[pwrappr01]

@yashag2255,

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

[Rens_]

---

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

[Karteek]

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

[Odiseo55]

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!

[mijikai]

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

[Odiseo55]

Hi, follow this link https://www.tachytelic.net/2020/04/get-more-5000-items-sharepoint-power-automate/

it helped me a lot.