How to pull more than 3000 SP list items to PowerApps

(1) Share

Report

Posted on by sasidhar_mudra

166

@v-siky-msft ,

Hi Sik,

I have a SP list with more than 4000 items. The app is pulling only 2000 as there is the limit.

Even in the collection. Is there a way that i can pull all 4000 items into collection? Tried looking at different posts here and could not understand how to do it.

Would you be able to help me on this?

Thanks

Sasidhar

Categories:

Building Power Apps

I have the same question (0)

All responses (31)

Answers (1)

PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report

If datasource is the name of your data source table, then create a collection using:

ClearCollect(Collection1, datasource)

Then you can use Collection1 to do whatever you want. It will have all the records.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (1)

Report

If at all possible I would like to try to convince you not to try to work with all 4,000 records at once. Like any database there should be a balance between getting enough records to work with to limit the number of return calls required to the database versus the inefficiency of trying to pull that much data across an Internet connection. What you should be doing is looking for ways to limit the number of records you need at any one time by filtering the data source using a non-delegable function. Imagine for a moment that you are a user. Are you really going to scroll through 4,000 records in a gallery to find the one you want. Filter the list to a realistic number of records and you won't have a problem.

Was this reply helpful? Yes No
datamaster 250 on at

Like (3)

Report

Hi @sasidhar_mudra as @Pstork1 mentioned it's best not to collect over 2,000 records. However, in rare occasions if you must I have written a blog post that shows you just step by stop how to collect over 2.000 records.
https://officepoweruser.com/how-to-collect-over-2000-records-in-powerapps/

------------------------------------------------------------------------------OfficePowerUser.com------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members. If you thought this post was helpful, please give it a Thumbs Up.

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report

I would just say that there might be situations why someone might want all the records. Just because all records are being pulled into the app, it doesn't mean a user will be expected to scroll through the entire list. But yes, no doubt, that best practice is to not pull so many records.

Thanks,
Hardit

Was this reply helpful? Yes No
Office365Master 338 on at

Like (1)

Report

I have recently posted a long blog that demostrates how you use a Flow consuming the Graph v2 APIs that "can" return up to 5000 records per Flow run. This solution includes full support for automated paging for List or Document Libraries containing 10s of thousands of items, technically. All described in my, more specifically relating to result sets returned by the PA_Graph_ListItems flow for items in any given List and equally for result sets returned by the PA_Graph_DriveItems flow for items return for any given Document Library.

However I have tested this solution returning 5000 rows and can attest to the fact that your app will quite often grind to a halt in terms of responsiveness, timeout and by accounts, as others have commented, I seriously doubt why anyone would want to populate a client-side collection with anywhere near that number of rows. If you really have to work with a list performing any business logic on that number of items in list, then you really should be executing that code in a flow, not in your PowerApp.

Was this reply helpful? Yes No
sasidhar_mudra 166 on at

Like (0)

Report

@PowerAddict ,

Thanks for the response.

The collection method did not work It still shows only 2000 items from SP list.

I have a headcount of 4000 employees. I want to collect them altogether so that i can create a Dropdown to select manager name and ComboBox to select multiple employees reporting in to him. It is okay for me to sort by the names of manager to select from Dropdown which should give me all the employees reporting.

Thanks
Sasidhar

Was this reply helpful? Yes No
datamaster 250 on at

Like (0)

Report

Did you follow the tutorial post? If so you should have pulled into the collection more than 2k records.

Was this reply helpful? Yes No
sasidhar_mudra 166 on at

Like (0)

Report

@datamaster ,

No Sir, i did not understand the below.

Can you please help me understand as i was not able to interpret what is that i should filter.

OnStart:
ClearCollect(
colTranslations,
Filter(
‘[dbo].[tblWPOTranslations]’,
ClientID = varClientID
)
) ;

Thanks
Sasidhar

Was this reply helpful? Yes No
datamaster 250 on at

Like (0)

Report

Below is a sample from my post where I am filtering my table. The particular filter is optional. You first start with pulling your data in to reach the max 2,000 records in. You do not need to filter the first time.

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (2)

Report

Hi @sasidhar_mudra

Here is one of my blogpost, where I collected more than 2000 records in the collection, by creating a calculated string column in SharePoint. This gets auto calculated based on the ID, but since this was an integer field, I created one for storing string value.

With the help of StartsWith function this was collected easily. Can you give this a try:

https://www.appliedis.com/how-to-use-galleries-in-powerapps/

You can follow the same steps, I did for the OrderCollection.

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!

Was this reply helpful? Yes No