cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pwrappr01
Level: Powered On

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

@yashag2255,

 

I have not. How does one go about creating an auto-incrementing field in a SharePoint list?

Super User
Super User

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

Hi @pwrappr01 

If you do not need to edit or add items to  your data but are using the list for lookups only, I suggest that you save your data as a static Excel file and use the Import from Excel option for datasources.  Admittedly, it has limited application but in the right circumstance, you can import up to 15,000 items at a time. It creates the datasource in seconds, delegation is not an issue, all functions are possible and they operate at blinding speed.  You can even create huge collections from the data by combining the tables using Collect(). 

For an example, please see my post in the community blog Automatically-Prefill-City-and-State-using-Zip-Codes-in-your-App  It uses a zip code file consisting of over 40,000 items broken into three 15000 item tables and then reconstitues them into a single collection. 

pwrappr01
Level: Powered On

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

@Drrickryp,

 

I appreciate that suggestion. Unfortunately, all of these datasources will be regularly updated. I would much prefer to store them in Excel and will continue thinking over your suggestion, but the regularity of the updates makes me think this might not work.

Super User
Super User

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

@pwrappr01 

If you need to update then this is not the right approach.  If you will have large datasets (greater than 2-4k), consider migrating to Sql or Common Data Service.  

Super User
Super User

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

Hi @pwrappr01 , 

 

In SharePoint, AutoNumber field can be generated by creating a new column of type "calculated". For doing so,
1) Navigate to SharePoint list.
2) Add a column and select More to see all types.
3) Select Calculated in the Type.
4) Add the Formula: [ID]
5) Save to add column.
Here we will be using the values from “ID” field from SharePoint list that starts from 1.
Note: This will auto-fill the column value in existing data so no need to updating the data separately.
 
MicrosoftTeams-image (47).pngH
This is the cleanes way to do what you are trying to. Refer to this and the earlier comment I posted. 
 
Hope this Helps!
 
PhilD
Level 8

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

Hi @yashag2255  what is the advantage of doing this versus just using the ID field as is?

hnguy71
Level 8

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

Hi @pwrappr01 @yashag2255 @Drrickryp @PhilD ,

I have a working solution to grabbing all items in SharePoint. If you follow the thread convo between me and Randy it will answer a ton of questions asked here:

https://powerusers.microsoft.com/t5/General-Discussion/Collect-All-SharePoint-List-Items-Using-ForAl...

This solution requires either Flow or Workflow Designer to accomplish this task. With this solution I am able to pull in as much or as little as I want through iterations. 

Super User
Super User

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

Hi @PhilD , 

 

No advantages that I can think of. I was working on a list where I was using a customised SNO column and I just used that (missed the point of straightaway using the ID column).  My bad!

 

Thanks, 

pwrappr01
Level: Powered On

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

@yashag2255,

 

This solution worked like a charm! Do you have any recommendations regarding performance because this will likely be used on multiple lists with 1,000s of items?

Super User
Super User

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

Hey @pwrappr01 

 

Glad that it worked for you. 

Notes: Loading data at multiple places using the same local collection will be a very efficient approach to go with. With the current size of data, it usually takes some time to filter out the results. But when you collect it at once, it might take a bit longer at initialisation but the subsequent steps will be way more faster. Also, this won't have the lags caused by connection issues as a copy of data is always present. Storing data in a collection overcomes the delegation problem that we have with the data sources. So it is best approach to go with in case of heavy data. It will get all the records instead of capping at 2000.
 
One suggestion though, if you are planning to use dropdowns, they have a limitation of 500 records, so instead make use of combo boxes (just a heads-up, please ignore if already implemented)
 
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!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 64 members 4,920 guests
Please welcome our newest community members: