cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DenisMolodtsov
Kudo Kingpin
Kudo Kingpin

Can only get the first 500 items from the large list

We have a large document library with 1 million documents.:

 

LargeList.png

I am trying to get documents with ID between 500 and 1000 using the formula:

 

ClearCollect(InMemoryArray, Filter(Documents, ID > 500 && ID <= 1000) );

 

Unfortunately, ClearCollect can't get documents with ID higher than 500.

 

"Number of items: " & CountRows(InMemoryArray)

 

DenisMolodtsov_1-1622647840101.png

 

Question: How do I get items by ID in a large document library?

Notes

  • The OOB ID field is supposed to be indexed by default, but I still get the "Delegation" warning. 
  • All IDs are sequential. There are no gaps. Documents with ID 501, 502, etc. exist. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User
Dual Super User

This is one of the footnotes from the SharePoint specific documentation on delegation located here :SharePoint - Connectors | Microsoft Docs.

  1. The SharePoint ID field for a table is a number field in Power Apps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field.

You can't use the ID field with anything other than '=' in a Filter whether it is indexed or not.  The fix for this is to find a better way to filter the documents without loading them into a local collection.  Using local collections for large lists should be a last resort.  They are slow and prone to causing data conflicts because each app has its own local collection.  So changes made to it are only visible in that app.  For a really large list it will also take a significant amount of time to load. I've seen times of 30 to 40 seconds when the list is only 5,000.  You will wait well over a minute or two to try to load 1 million records.

 



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

View solution in original post

2 REPLIES 2
CNT
Super User
Super User

@DenisMolodtsov When using the built in Share Point ID column, Power Apps will bring in only 500 (or 2000 if you increase the Data Row Limit to 2000) records. You'll have to use your own ID column. Someone had this issue recently, Solved: Re: Collection not collecting all rows - Power Platform Community (microsoft.com)

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

Pstork1
Dual Super User
Dual Super User

This is one of the footnotes from the SharePoint specific documentation on delegation located here :SharePoint - Connectors | Microsoft Docs.

  1. The SharePoint ID field for a table is a number field in Power Apps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field.

You can't use the ID field with anything other than '=' in a Filter whether it is indexed or not.  The fix for this is to find a better way to filter the documents without loading them into a local collection.  Using local collections for large lists should be a last resort.  They are slow and prone to causing data conflicts because each app has its own local collection.  So changes made to it are only visible in that app.  For a really large list it will also take a significant amount of time to load. I've seen times of 30 to 40 seconds when the list is only 5,000.  You will wait well over a minute or two to try to load 1 million records.

 



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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,841)