cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ratanapouy
Helper III
Helper III

how to get data from SP List into Data Table more than 2000 records

hi,

 

I have 4,662 records in SP list to load into gallery or data table. i am using collection to get those data, powerapps limits only 2,000 records, how to get all those records?

 

Thanks,
Ratana

7 REPLIES 7
yashag2255
Dual Super User II
Dual Super User II

HI @ratanapouy 

 

Directly there is no way to fetch such large data from SharePoint list due to delegation. As a workaround to this, you can follow below Steps:
1) Navigate to SP list.
2) Create a column of type "Calculated" and add the formula: ="ID"&TEXT([ID],"0000") and name this as "CalculatedColumn"MicrosoftTeams-image (43).png
3) Now, on the AppStart use the expression: 
Clear(MyCollection);
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID0")));
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID1")));
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID2")));
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID3")));
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID4")));
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID5")));
Collect(MyCollection,Filter(SPListName,StartsWith(CalculatedColumn,"ID6")));
 
This will get all the records from the SP list upto ID 6999.
 
4) Now, use this collection as Gallery Items.
 
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!
Pstork1
Dual Super User III
Dual Super User III

To add to @yashag2255 reply.  I would really suggest you rethink how you are presenting the data.  Even if you can get all 4,000+ records in a gallery or data table, why would you think users would actually scroll through that many items to do anything with them? I suspect what you really want is for the user to be able to access all 4,000+ records.  Think about building the system out to have some kind of paged approach to the records that can be moved thorugh quickly.  Your user will still have access to all the records, but will have a much better experience.

 

For example, if you are presenting a list of users, sort and filter the list by the first letter of the name.  By default show the "A" entries in the gallery.  Then let the user enter a letter and switch the gallery to the sorted and filtered view of entries starting with that letter.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
yashag2255
Dual Super User II
Dual Super User II

Hey @ratanapouy , I agree with @Pstork1 suggestion to design the app in a way that the user can access the records with ease. To address a similar problem, I recently blogged this alphabet based slider that would allow a user to see the records starting with a particular alphabet and then on. Maybe you could take a look at this one too. https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Developing-an-Alphabet-based-slider-con...

 

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!

v-xida-msft
Community Support
Community Support

Hi @ratanapouy ,

Do you save your SP List records into a Collection in your app, then use the collection as data source in your app?

Do you want to retrieve all records from your SP List into your app?

 

Currently, it is an known issue with Collection in PowerApps. The Collect function could only retrieve at most 2000 records once time from your cloud service into your app.

 

If you want to retrieve all records from your SP List into your app, you could consider bulk-load your SP List records into multiple individual collections, then merge these individual collections into a single one collection, then use the single one collection as data source in your app. Please check and see if the alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

On your side, please take a try with the following workaround:

Within your app, set the OnStart property of the App to following:

Concurrent(
           ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000)),
           ClearCollect(col2, Filter('YourSPList', ID >= 2001 && ID <= 4000)),
           ClearCollect(col3, Filter('YourSPList', ID >= 4001 && ID <= 6000)),
           ClearCollect(col4, Filter('YourSPList', ID >= 6001 && ID <= 8000)),
           ClearCollect(col5, Filter('YourSPList', ID >= 8001 && ID <= 10000))
);
ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

Then set the Items property of the Gallery or Data Table to following formula:

MergedCollection

then re-load your app (fire the OnStart property of the App control), then try your app again, check if the issue is solved.

Note: Please make sure you have set the "Data row limits for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Please take a try with above solution, then check if the issue is solved.

 

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.

i try to follow your instruction, it doesn't work.

Hi @ratanapouy 

 

Are you getting any errors? Please share more details so that we may help you better.
 
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!

there is no error. i got nothing in combine collection.

in SP list, i have over 2,480 items. here is what i exported from SP list as spreadsheet.

08.png

 

tried to concatenate Month & Year and make BatchID by collecting range from 1-2000 = 1, 2001-4000 =2...etc

BatchID in SP List:

BatchID.png

 

This is formula in PowerApps:

 

//New Method Implementation
//Add each collection with 2k records
Concurrent
(
ClearCollect(ZDB01, Filter(tbl_CheckPoint_Data, CheckPoint.Value = CheckPointMaster_3.Selected.checkpoint, BatchID="1")),
ClearCollect(ZDB02, Filter(tbl_CheckPoint_Data, CheckPoint.Value = CheckPointMaster_3.Selected.checkpoint, BatchID="2")),
ClearCollect(ZDB03, Filter(tbl_CheckPoint_Data, CheckPoint.Value = CheckPointMaster_3.Selected.checkpoint, BatchID="3"))
);
//Combine Collection
ClearCollect(LargeDB, ZDB01, ZDB02, ZDB03);

 

//Filter collection base on requirement
ClearCollect(LargeDBItem,Filter(LargeDB, Month_Year="92019"))

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,446)