Delegation

Filtering using multiple filters with delegable functions

Delegation warnings some times don't get the same attention as errors. Why? Because you can ignore them and your app will work just fine, well, most of the times. Delegation refers to the ability of Power Apps to delegate processing of data back to the data source so only the processed data is sent back to the app. Not every function can be delegated to every data source. Every data source has a defined list of functions that can be delegated.

Delegation becomes an "issue" when you are working with more than 500 records. If the data in your data source exceeds 500 records and a function can't be delegated, Power Apps might not be able to retrieve all of the data and the results could be inconsistent and incorrect. The default limit (which is 500) can be changed to a max value of 2000. Although tempting to do, this is only a short term solution if you expect your data to grow beyond 2000 in the future. Also, increasing this limit can adversely affect performance.

I ran into a situation where I had to filter a CDS entity based on multiple filters for a gallery. The filters were a combination of text values and drop downs. When I had first created this app, I had safely ignored these warnings until yesterday when the record count reached 800 and the users were seeing inconsistent results. I knew the problem was delegation and hence my journey of getting rid of the delegation warnings (yes, there were 28 of them!) started.

After playing around for a while and not getting anywhere, I decided to take a break and answer some questions on the Power Apps Community. Being an active contributor and knowing how helpful the community members are, I decided to get some help myself. Its almost always true that someone has faced the same or similar issue in the past and I was hoping for the same. As soon as I posted my question, I was directed to an interesting exchange by Matthew Devaney and the credit for this goes to him and the others who participated in that exchange.

This definitely helped me and I am sure will help others so I wanted to share the solution with an example. The use case that I will be using as an example here is that we have a list of users (standard CDS entity) and we want to filter that list by name and/or title. Name is a text input filter where a user will type in the name and Title is a combo box filter.  If both are blank, then all the user records should be returned. If either one or both filters have values, then the filtered list should be returned.

1. With Non-Delegable Functions

This is an expression that meets the needs from a filter perspective if the number of records is less than 500 (or whatever the limit you have set the data row limit of your app to):

Filter(Users, If(!IsBlank(TextInput1.Text), 'Full Name' = TextInput1.Text, true), If(!IsBlank(ComboBox1.Selected.Title), Title = ComboBox1.Selected.Title, true))

The idea is that if the text input control is not blank, filter on name using the text in the text input control and similar if the combo box is not blank, filter on title using the combo box selected value, or else do not filter on them (hence, the true value in the else part of the two If statements). The problem here is that the If function is not delegable. So although the gallery looks perfectly fine, it would lead to incorrect results if the number of users is more than 500.

2. With Delegable Functions

This is an expression that meeds the needs from a filter perspective and is also delegable.

Filter(Users, IsBlank(TextInput8_1.Text) || 'Full Name' = TextInput8_1.Text, IsBlank(ComboBox15_1.Selected.Title) || Title = ComboBox15_1.Selected.Title)

The logic is similar to above but it is implemented without the If statements. If either of the two filters is blank, the OR condition makes that filter true  and if not, then the filter comes into play.

To summarize, the main point that I wanted to illustrate, using this example, is that getting past delegation is almost always possible by tweaking your expressions. In some extreme cases, the data source can also be tweaked to one that supports delegation, however, in most cases that's practically not possible. If everything else fails, you can collect your data from the data source into a collection and then perform actions like filtering on the collection rather than directly on the data source.

Have fun! Get addicted!

Leave a Reply