How to Solve Delegation Limit Issue in Power App?-For 2000 Records

How can I manage to filter more than 2000 records from the list?

Okay, we have learned in the previous blog that how to overcome the delegation limit issue in retrieving 500 records from a table. We can change the Data row limit for non-delegable queries setting from 1 to 2000 Max. (Refer: How can I manage to filter more than 500 records from the list)

 

Now, what if we have to filter more than 2,000 records from a Table, There is a workaround for Power Apps delegation limit. We need to apply the Collection in Power App, which will act as a temporary copy of your data source.

 

Power Apps just doesn’t like dealing with all that data from an outside source, but temp storing through Collection within the browser works alright.

 

How to Overcome 2000 rows limit:

Yes, this is possible to filter more than 2000 records from list. For this, You could run multiple Collections to bring in the data in chunks of 2000 in one go, it will store these rows in a temp storage and then query again for the next 2000 rows and again it will store the next 2000 rows in the temp storage and so on and thus group all these chunks into a single larger collection.

Changing the limit:

• We need to apply ClearCollect formula to the “OnSelect” property of the screen to make  a temporary storage (temp)
• And then apply Sort and Filter formula in created temp to the “Items” property of the Gallery control.

 

The ClearCollect function is a combination of Clear and Collect which deletes all the records from a collection and then adds a different set of records to the same collection.

 

Take an Example– We have a Table named “Employee”. Now, We need to filter 10,000 records on the basis of ID (Int datatype).

Data source: SQL Server

Steps needs to be Performed:

Step1: Select the screen which holds your Gallery Control, Choose “OnVisible” property of that screen and apply ClearCollect formula in fx :

 

ClearCollect(Temporary storage Name,Filter(‘Name of Table’,ID<=2000),

Filter(‘Name of Table’,ID<=4000 And ID>=2001),Filter(‘Name of Table’,ID<=6000 And ID>=4001 ),Filter(‘Name of Table’,ID<=8000 And ID>=6001 ),Filter(‘Name of Table’,ID<=10000 And ID>=8001 ))

 

Step2: Select the Browse Gallery control, Choose “Item” property of that Gallery and apply sort and Filter formula in fx :

SortByColumns(Filter(Temporary storage Name, TextSearchBox1.Text in Text(ID)), ” ID”, If(SortDescending1, Descending, Ascending))

 

 

Result:
To ensure that now your app can scale to large data sets and can also retrieve the records having IDs of more than 2000, Let’s test the App. Anything that can’t be delegated returns a single record according to the searched ID, which should be easy to detect when testing your app.

Let’s search for ID -9,000 and see the result below:

 

 

 

 

 

 

 

 

 

 

 

 

 

Nice, we have successfully overcome the delegation limit issue and retrieved the record where ID > 2000 from the list.

Stay tuned for more learning!

Blog disclaimer:
This is a professional weblog, and we have invited experts to share their thoughts, expertise , perspectives and knowledge. The opinions expressed here are purely representing their personal views and not those of any institution, employer or company.

for more details, visit here