How to pull more than 3000 SP list items to PowerApps

[sasidhar_mudra]

@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

[yashag2255]

Hi @sasidhar_mudra 

Can you check if you are able to get the records after index 2000 from the SharePoint list. You can do this by adding a temporary screen and on that screen, add a gallery control with formula:

Filter(myCollection, Batch_ID = 3000)

also, create a label and place the expression on the Text property as: CountRows(MyCollection) -> This should be equal to the number of records in the SP list.

If you are able to see those records, it is okay if you are getting delegation warning as we are collecting items based on starting character and which supports upto 10k records.

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!

[sasidhar_mudra]

@yashag2255 ,

Nopes. No luck sir. It still shows 2000 records only.

Thanks

Sasidhar

[Anonymous]

@sasidhar_mudra @yashag2255 I've been following along with this post in the hope this would work for me as well, but alas I'm getting the same delegation warning and only returning 2000 items, no more.

Screenshot to show this:

colSecond2000 returns empty.

I also created the extra SP column via:

New Column -->Calculated (calculation based on other columns) --> Single line of text --> formula --> =IF(ID<2001,1,2).

My SP List holds 3300 records so this formula works fine. I've also double checked that IDs > 2000 have 2 in this column and they do. I even changed the above formula to --> =IF(ID<2001,"1","2") but got the same result.

This just doesn't work for me. @yashag2255 have you got this to work on a SP List with more than 2000 records? 

[yashag2255 · Accepted solution]

Hi @Anonymous / @sasidhar_mudra 

I did a few tests on my side and I was able to overcome delegation with Single Line of Text field instead of a calculated field. In my case, I added the values manually in a SP list(in my case to Title Column) and when used StartsWith on this Title column, this was fetched with no delegation warning.

I did some research and calculated columns are not delegable, so you have to do that for a single line of text column. You can either create a workflow to auto-update this value whenever a record is created.

Below is a screenshot of my SP list for which I was able to get more than 2000 records.

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!

[sasidhar_mudra]

@Anonymous ,

Hello Eelman,

Were you able to crack this ?

Regards

Sasidhar

[Anonymous]

@sasidhar_mudra  To be honest I haven't re-tried doing this, however, I've just recently been watching this video series on delegation 

https://m.youtube.com/watch?v=gwiErbYtRdA&list=PLTyFh-qDKAiE6ia-D94Qk5-AzpN4dqOmD&index=1

Its given me a much better understanding of how to access my SharePoint data without having to pull all of it into the app. Maybe this is a better solution, unless you can write a Flow to build out your new SP column?

just today I was able to populate a gallery (using the methods in the videos) with over 2000 records. Also, by using dropdowns as gallery filters, as per the videos, I could filter down my data to a more manageable size - still getting access to ALL my data.

I thought I understood delegation before watching this series but I was wrong. It now means I will have to think differently about how I build future apps but this will open up more opportunities for functionality I need.

Watch video 1 and build out an app using the steps shown and see if you can get what you need? I think you will, but let me know if you get stuck

[pranavnaidu24]

Partial fix for fetching SharePoint List items up to 4000.

Concurrent(

ClearCollect(CollDescending,Sort(DataSource,ID,Descending)),
ClearCollect(col1, Sort(DataSource,ID,Ascending))

);
ClearCollect(col2, Filter(CollDescending, ID >= Last(col1).ID+1 && ID <= First(Sort(DataSource,ID,Descending)).ID));
ClearCollect(MergedCollection, col1, col2);

Hope this helps

[Drrickryp]

@pranavnaidu24 

Instead of ClearCollect(col2, Filter(CollDescending, ID >= Last(col1).ID+1 && ID <= First(Sort(DataSource,ID,Descending)).ID));
You can use ClearCollect(col2, Filter(ColDescending, Not(ID in col2.ID));

ClearCollect(MergedCollection, col1, col2)

This works as long as the list doesn't exceed 4k items. I like to put a warning label on the screen with the Visible property CountRows(MergedCollection>3999)

ref: https://powerusers.microsoft.com/t5/Building-Power-Apps/Collections-maxes-out-at-2000-when-populatin... 

[Pstork1]

Using delegation is definitely better than pulling 4,000 records into the app.  Even if you can get .all the records into the app, users won't scroll through that many records in the UI.  Yes, it can give you a better search experience, but you'll get a worse performance experience.  Its not worth it.

[sasidhar_mudra]

@yashag2255 ,

My sincere apologies, the solution your provided worked for me well and the app is running smooth. Thank a lot.
I totally missed selecting this as Solution and respond !!!!

Regards

Sasidhar