Can only get the first 500 items from the large list

[DenisMolodtsov]

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

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)

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. 

[CNT]

@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 · Accepted solution]

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.