Answered

More than 5K rows

(1) Share

Report

Posted on by Jaw1

Good day.
Trying to build a Power App using a sharepoint list for 5K rows.
Ive seen articles on how to use the ID, but this ID field may be more than 10K since other rows have been deleted in the past.

Any suggestions

Ive seen articles on using Power Automate and even JSON.

Lookng for the most reliable option.

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

WarrenBelz 149,317 Most Valuable Professional on at

Like (0)

Report
More than 5K rows

Hi @Jaw1,

I need to firstly clarify that sorting on a Date column as well as filtering on the column where the date is equal to, or after/before a specified date is Delegable.
So firstly, if you had a Gallery with the Items
Sort( SPListName, Created, SortOrder.Descending )

It would display all records in descending date order. In the background, it is bringing them back in batches of 100, but these update as the user continues to scroll down.

If you had a Date Picker and want to display all the records after the chosen date and had in the Gallery
Sort( Filter( SPListName, Created > DatePickerName.SelectedDate ), Created, SortOrder.Descending )

you would get all records after that date (even if more than 2,000).

If you simply want the newest record based on that date column, you would need
First( Sort( SPListName, Created, SortOrder.Descending ) )

Is this what you are asking ?

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
Jaw1 25 on at

Like (0)

Report

More than 5K rows

Maybe a practical way to ask this via this example...

I have SP "Created" date column. What I want to do is use this as the "Last Refresh" date in My Power App.
My SP list has 5K rows. When a try to bring in the most current date, Power Apps is only looking at the 2K rows (yes, i up'd the limit :based on ready your article TY). What would I need to do to have Power App look at all records?

I've read articles on options for creating collection and even using power automate.

Just trying to find the best option for this example to hopefully find the best options

Was this reply helpful? Yes No
WarrenBelz 149,317 Most Valuable Professional on at

Like (0)

Report

More than 5K rows

Hi @Jaw1,

A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 149,317 Most Valuable Professional on at

Like (1)

Report
More than 5K rows

Hi @Jaw1,

As you have now confirmed, you are new to Power Apps and probably need some basic guidance on “how things work”.

The first thing, which I mentioned in my second point was that if your filter is Delegable, (refer my blog ) once you index the relevant columns, the list size does not matter, SharePoint will handle the query for you and you do not need a collection.

If you must make a big collection (it is very rare that I ever need to), then there is quite a simple one-time ForAll(Patch()) statement that will back-populate your shadow ID field with a Flow doing this going forward. You will probably need to run this a number of times as it picks up the blanks fields up to your Data Row Limit each run until there are none left.
With( { _Data: Filter( SPList, ShadowIDField = Blank() ) }, Patch( SPList, ForAll( _Data As _D, { ID: _D.ID, ShadowIDField: _D.ID } ) ) )

Be aware however of the performance penalties that come with large collections, both in collecting the data and running the query “locally” (as well as potentially "clogging" the app itself with undue ovrheads). I have found that anything much over 4K items slows considerably, getting slower the more fields and filters involved.

Lastly, what @matthummel has noted (retrieving a non-Delegable response with a Flow on a large data set) certainly works, but requires some degree of knowledge converting what can be complex filters into Flow odata queries, however we may be getting ahead of ourselves if your filter is Delegable.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
Suggested answer

matthummel 7 on at

Like (0)

Report

More than 5K rows

Good question,

I second what @WarrenBelz said

Try indexing a column like created date or by status and filter or sort the column. The columns need to be indexed.

Power Automate would be the best for large list. Try offloading in automate then return it to power apps. Build a flow retrieving all rows using Get Items with Pagination and then send results back to power apps with the Respond to PowerApps action.

I work with large list and this has helped with a few of our list.

Was this reply helpful? Yes No
Jaw1 25 on at

Like (0)

Report

More than 5K rows

@ WarrenBelz

Very greatful for the reply!

Since I have more than 4K rows in the Sharepoint list, is the best bet to use the collection in Power App?

If so, I cannot use the ID field automatically generated in SharePoint, correct? I would need to create a unique ID field, right?

Is the best bet to use Power Automate to create a flow to create an item field that mirrors the ID field?

I could see this working with a "New Items is created", but how would I account for the prior IDs?

Any video suggestions?

Very green here. Sorry for all the followup questions.

Was this reply helpful? Yes No
WarrenBelz 149,317 Most Valuable Professional on at

Like (2)

Report

More than 5K rows

Hi @Jaw1,

I will add a couple of comments guessing what you are referring to here. Firstly, one of the prime requirments on SharePoint lists over 5,000 records is to index any columns (you will find this in Settings > Indexed columns ) that are involved in query criteria either in SharePoint Views or Power Apps Filter/LookUp functions. You are allowed 20 indexes per List.

The second clarification is that after this, any Delegable queries in Power Apps will work fine on large Lists - I have several over 100k.

Lastly, if you are wanting to make a Collection over 2,000 records, then this blog of mine may be of assistance. Your reference to the ID suggests you are asking why this cannot be used for the purpose - the reason is that the ID field is only Delegable for = (equals), not > or < (greater than or less than)

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
Suggested answer

Michael E. Gernaey 47,123 Super User 2025 Season 2 on at

Like (0)

Report

More than 5K rows

Hi @Jaw1

Can you please clarify what do you mean by building an app. There are so many details there, are you using Tables, Galleries, Dropdowns, Combos?

I am not sure what you mean by because the ID's can be over 5k. I do not know what articles you are reading so please provide details here of what you need.

If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.

Thank you!
Sincerely, Michael Gernaey

Was this reply helpful? Yes No