Delegation is a very central concept when making Power Apps. With 2000+ lines, every delegation warning should be taken seriously. Power Apps kindly tells its maker that there are sections in the formula that cannot be delegated to a data source with the yellow triangle.
However, you shouldn’t trust that everything is fine based on only the fact that no yellow triangles are to be found. This time, we will go trough a few special situations of delegation, as well as the way in which these oddities should be addressed.
Max number of rows for non-delegable queries
From time to time, in the midst of intense Power Apps development, it’s a good idea to change the number of rows returned by non-delegable queries to one (the default is 500, and maximum is 2000).
Why? Because after this, we can easily see which parts of an app are making queries that cannot be delegated. In practice, the number of rows in these lists and galleries drops to one or zero.
After testing this, be sure to return the value back to the original value. Otherwise, you’re spending your afternoon wondering what’s wrong with your data sources.
Examples of hidden delegation problems
Let’s create a table (DelegationExamples) with four rows in Dataverse for Teams. Two Timo’s and two Lauri’s.
After this, let’s create a Canvas Power Apps and add a gallery that displays the contents of that table.
Now we can start.
We only want to show the same names once in the gallery. This is accomplished with the Distinct command.
The end result is exactly what we want. And there are no signs that there are any problems with this.
The number of rows to be retrieved is set to 1 in situations where delegation is not supported. This is done in Advanced Settings.
We can see that the Distinct command cannot be delegated. As a result, only one row is returned.
Next, let’s group the rows in the table based on the Name column. This is accomplished with the GroupBy command.
GroupBy( DelegationExamples, "crb24_name", "RestOfMe" )
The end result in the gallery is again one line for each name. And no trace of possible delegation problems.
But what happens when we set the number of lines returned by non-delegable queries in the settings to 1?
Well, again only one row is returned.
This mean that the GroupBy command is also non-delegable.
The Distinct and GroupBy commands are not promised to support delegation. What is confusing, however, is that using them does not produce the familiar triangle warning.
Dynamic gallery content
Next, let’s create a very typical situation. We have the same kind of gallery listing Timos and Lauris. But now we add the Show all checkbox in the screen.
If the box is checked, the DelegationExamples data source is displayed as is in the gallery. If the box is unchecked, only names beginning with the letter T are displayed.
The gallery now looks like this. Everything is working fine, even if we lower the number of returned rows for non-delegable queries.
Let’s add a search box that allows the user to search for people in the table by their first name (crb24_name).
Search( If(chkShowAll.Checked, DelegationExamples, Filter( DelegationExamples, StartsWith(Name,"T") ) ), inpSearchText.Value, "crb24_name" )
Still, everything is working fine.
But what happens now when we set the number of rows for non-delegable queries to one?
What! The query cannot be delegated at all!
We find ourselves in the same situation if instead of (or in addition to) Search we want to Sort. A maximum of one row is always returned.
What is causing this? Search and Sort are guaranteed to be delegable queries with Dataverse for Teams.
The problem is the way we built the formula. The outermost part of the query is the Search command. However, the data source used within it changes according to the condition (options a and b).
As a result, the query as a whole cannot be delegated to the data source. To fix this, we must first execute the query within the condition (Filter) and only then do the Search for the result. The latter part is done locally.
The fix is quite simple. Let’s move the entire query inside the condition.
And this is how our query works again as it should.
Delegation is a difficult topic for Power Apps makers, as it strongly depends on which data source is used. Dataverse has the best support for delegation.
The documentation shows the commands that can be delegated (as long as the documentation keeps up with the constant changes), but we can’t really expect everyone to follow the ever-updating documentation.
Makers are accustomed to responding to delegation warnings. But as we saw, the warnings don’t always appear when they should.
The most straightforward way to avoid problems is to occasionally test how the application works when non-delegable queries return only one line.
Interested in reading our latest Power Platform blog posts?
You’re welcome to subscribe to our email newsletter: Forward Forever News. Max 1 email per month, with a curated list of latest insights and articles on Power Apps, Power Automate, Power BI.