cancel
Showing results for 
Search instead for 
Did you mean: 

Fix 500 Record Limitation

Problem:

  • At most 500 records can be returned at a time, whether filtered or not.
  • OneDrive, Dropbox, and Google Sheets can only read from the first 500 records.
  • CDS can read from all records, but can only return 500 records on a Filter.
  • It is not possible to Collect all records of an unfiltered datasource.

 

Suggestion 1:

Could you create a function to only pre-load and reload entity data to a temporary collection? It would do the same as this, but it could pull in everything without the 500 record limit:

Refresh(datasource);
ClearCollect(temp_datasource,datasource)

It would only import all records or none; so if you wanted to filter, you would have to do it after the temporary collection has been created. Furthermore, to reduce drag on the network, you could restrict this specific function to queue up or a quota like 100 runs per second.

 

 

Rationale: If we can collect the entire contents of a datasource to a temporary collection, we can operate on the temporary collection instead. This would allow for all aggregate functions and other non-delegable functions to work on the temporary collection. Then from time to time, we can reload the datasource to have the most updated data in the temporary collection.

 

 

Suggestion 2:

Could you create a function that would pull in 500 records at a time until all are pulled in? It would work like my solution, but it would be automated with fewer lines of code.

 

 

 

 

I am suggesting these functions as temporary solutions to not having all functions delegable.

 

Please consider this idea.

Status: Completed
Comments
Community Champion

@ManishM, once data has been collected to the app ("cached"), you can do anything with the collection without being limited to 500 records at a time.

 

You just need a creative way to pull in 500 at a time since one does not exist natively.

 

 

Microsoft
I have been successful in pulling up to 5000 records 500 at a time. But I was told that delegation does not work on collection either hence my confusion. With the new understanding can I use commands like Group, distinct, sum, etc on Omer 5000 records in collection?
Community Champion

@ManishM, that's correct. I've been able to successfully Sum() across >10,000 records when it has been collected into a collection. You can do any operation to the collected data.

 

I hope the misunderstanding hasn't inconvenienced you too much these past two months. 😕

Microsoft

Thank you @mr-dang. only changes I had to make was to apply non-delegable commands as I was pulling 500 records and store them in additional Collections and then join them into another collection 🙂

 

Thank you, again.

Power Apps
Power Apps
Status changed to: Completed
 
Frequent Visitor

I can not seem to find this new setting.. there is only one setting available in the experimental features section, and that is called delayed load...

settings snapshot

Power Apps

It's currently in first preview and will start rolling out for general availability as of Monday. 

Anonymous
Not applicable

An article suggested to use:

StartsWith & = operator in filte as a work around.

 

But honestly:

 

StartsWith And = operators fail in my case. I have no clue what I need to do.

Filter(CommsTRACKER_3,StartsWith(CM_Status.Value,”Ongoing”))

or

Filter(CommsTRACKER_3, CM_Status.Value = “Ongoing”)

The column is indexed.
It only gives me one record where as there are 86 records in the SP list. [Total more than 1000+)

What is wrong?
Pretty much with this limitation I am at loss on how do I deal with even small list of items now.

In big trouble for choosing and suggesting power apps.

Any help?

 

Advocate II

Please set the limit of this experimental feature to at least 5000, i have narrow lists that load the 2000 items really fast but need 600 more rows and i am tired of work arounds in PowerApps at this point.

 

-Eric Niemiec