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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,691)