PowerApps - Beyond 500 item limit and Delegation Warning. The "XXXXX" part of this formula might not work on large datasets.

This is the most common warning which is overlooked by the Power app developers as its just "Warning".

We see the function work and fetch the results, and thus ignore the warning. I have seen  many of the powerapps that were productionized have these warnings.

Please make a note:

"That is not just a technical warning, its a future functional error". 

In this post i will explain what does it mean and how to solve it.

I have created a Sharepoint online site with a list of 6000+ user info.

I have also created a canvas power app where i am using the above list as a Data Source.

Usually no application will try to show thousands of records and let users scroll down to record they want. So as a general requirement i am implemnting search box functionality here.

Now let me change the Data Source of the gallery from "PowerAppList" to a collection i created "RequiredData".

And we will be populating the collection on click of the "Search" button. here is the code for that.

Yeah, it shows a warning "Delegation Warning. The "Search" part of this fomrula might not work on large datasets.", but who cares, its working. let me show you the results.

See, it fetched many results from my sample data set and look ak that scroll bar, seems to be working. Its just a technical warning, no biggie, right? Now we productionize this.

Most of us didnt get to work with real dataset, which is quiet big compared to our sample dataset.

To demonstrate this, i have created 6000+ records in that sharepoint list. So we have User1 to User6002. let me search for User50.

Oopsie ! There is the functional error i am talking about.

When i search for users who have User50 in thier name, it should show, User50, User500,User501,User502... and so on. But here it cannot see data beyind User500 due to 500 Item limit in Powerapps.

Before we discuss whats wrong, read below definition.

Delegation

Powerapps have a limit of 500 records per data source, but there are ways of using large data sources with thousands of records.

Delegation is where the expressiveness of Power Apps formulas meets the need to minimize data moving over the network. In short, Power Apps will delegate the processing of data to the data source, rather than moving the data to the app for processing locally.

There are delegeable Data sources like SQL, Sharepoint, Power Automate and Logic Apps.
But each of these data sources have certain commands that doesnt work for delegation.

In my case, Search is not supported for Delegation of Sharepoint Data source.

Please note that every function, every operator used in formula to query need to support Delegation, If i use Filter() with a Find() function it will not work as expected. Filter() supports delegation but Find() doesnot support delegation on Sharepoint list. let me demonstrate my point.

User60 search should fetch User60, User600,User601,User602... and so on.
But due to the fact that the total formula is not delegeable, it looks at top 500 thus the above result.

Now let me change the formula to make it delegeable. For Sharepoint both Filter() and StartsWith() functions supports Delegation. So it will work beyond 500 items now.

Now I serach for User600 and here is the result.

Please go through this MS article on what Data Sources supports what functions for delegation. Drop me a comment if you have any questions or queries.
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sources

As a best practice, alsways make the critical coumns on sharepoint list as a Indexed columns.