Power Apps: Delegation Issue and workarounds

As soon as you start using PowerApps, you understand how powerful they can be, but you’ll find the following alert message quickly.

Delegation warning. This part "Filter" of this formula might not work correctly on large data sets .

You may ask yourself why shouldn’t PowerApps be able to deal with large datasets. The answer is that it does but with certain safeguards for performance. It’s essential to understand how PowerApps deals with data and why this limit exists.

The issue

PowerApps, for performance reasons, limits the number of records to 500 for each data source, avoiding big datasets to go over the network, keeping the app running smoothly. The issue is that only 500 are fetched hence the:

might not work correctly on large data sets.

PowerApps ignores any record over 500, and the information will not be displayed. Aside from the alert, this is quite a silent issue that can bring some confusion if you’re not aware of it. By default, all filtering operations are done client-side, meaning that your PowerApp collects the information and performs all filters and sorts that you want to display the data. It’s important to limit these costly operations to manageable sizes, since they can run on any device, including phones, where resources are limited.

Microsoft has a bunch of articles explaining what delegable functions are but, in a nutshell, these are functions that fetch the results from compatible data sources they can then “delegate” the filtering to the more powerful backend. With this, we can keep performance while always getting the filtered information regardless of the size of the dataset.

The workaround(s)

There may be more than two workarounds, but I’ll focus on these two. If you have more, please make sure to let me know, and I’ll add it here.

The quick and dirty fix

You can change the limit. There’s a hard limit of 2000 records, so if you have a dataset that is more than 500 but always less than 2000, this solution will work for you. Be aware that this will have a performance hit since PoweApps will fetch more information, but aside from that, all should work correctly.

Here’s how to do it:

Go to Home

Select App Settings

Select Advanced Settings

Change the value. The max value is 2000.

The scalable fix

A more sustainable solution is to take full advantage of delegable functions. I have an article where I keep this information updated so you can check it out to see if the connector that you’re using (or thing of using) is delegable.

Let’s build an app with this issue and prepare it for proper delegation. I’ll use SharePoint Lists as my data source, but you can use any other compatible data source. Let’s think about a simple timesheet app where each person inserts the time he/she spends in each task.

SharePoint as a Data Source

First, let’s create a list that contains the data. It should look something like this:

NoteI’m following my naming conventions defined here, but feel free to name the columns how you see fit.

Now let’s add some fields:

I’m not the most imaginative person when it comes to creating examples, but I think you’ll understand it 🙂

PowerApp to show the data

Creating a PowerApp is simple. In the List go to PowerApps and select Create Application

Name the application.

PowerApps creates a sample app for you with a bare list, edit, and add capabilities.

You should see the data that you created before. The format is not relevant for this example. This app is already making the proper delegation to SharePoint in the filter, but we want to only display information for the logged user and not the full list. To do so, select the Browse Gallery and change the formula from:

SortByColumns(Filter(Timesheet, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

To:

SortByColumns(Filter(Timesheet, StartsWith(Title, TextSearchBox1.Text) && 'Created by'.Email = User().Email), "Title", If(SortDescending1, Descending, Ascending))

Looks good but now I get our delegation error:

If you check the reference, you can see that Filter is delegable, but the issue is that it’s only compatible with string values and where we’re comparing with Created by.Email. Checking the SharePoint’s List, we can understand that Created by is not a String hence the error. A potential solution could be to create a calculated field, but this would not solve our problem since we’re still not getting a String field. So we need to create a new column called email that stores the string value so that we can use it for comparison. We’ll hide it from the user since it serves no purpose and we won’t force him/her to insert it every time, so let’s automate the process.

Automation to the rescue

That’s where our friend Microsoft Flow shines! We can create a flow that looks at this list and performs actions every time a user creates adds an entry to the timesheet. But first let’s add a column to our list:

Now let’s create a flow to update the fields. Go to flow.microsoft.com and create a new automated flow:

Add a name and select your initial trigger. We’ll pick “When an item is created”:

Now choose your SharePoint instance and list and add a SharePoint action to update the item.

Next, pick the same instance of SharePoint and add the following:

That’s it. Once you create a new item in SharePoint, this flow will trigger automatically and add the email.

It works 🙂

Changing the PowerApp

Since we changed the data source, we need to update PowerApps to fetch the changes.

Just go to:

Now we’re ready to change the formula with our new field.

SortByColumns(Filter(Timesheet, StartsWith(Title, TextSearchBox1.Text) && searchpersonemail = User().Email), "Title", If(SortDescending1, Descending, Ascending))

But we still have the delegation warning. Why? The User().Email is not a String field, so the PowerApp still cannot delegate the filtering to SharePoint. User().Email is used globally in the application so we can create a variable with the email value as a String when the PowerApp is launched and use that variable in the formula.

To do this, go to App and add the variable:

Let’s try our formula with the variable:

SortByColumns(Filter(Timesheet, StartsWith(Title, TextSearchBox1.Text) && searchpersonemail = UserEmail), "Title", If(SortDescending1, Descending, Ascending))

No more warning. The PowerApp can now do the proper delegation to SharePoint instead of doing it client-side. AWESOME!

Photo by Briana Tozouron Unsplash

Key Takeaways

1. There’s a reason for it: The delegation warning can be annoying, but there are good reasons to limit the number of records that are going over the network. Take advantage of the power of the delegation and shift the heavy tasks to the data source.
2. Workarounds: There are workarounds to deal with these warning that requires some work, but they enable scalability of the app regardless of the number of records.
3. Automation is your friend: Use Microsoft Flow to automate the creation of the search tables that help filtering the data efficiently.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out my other PowerApps articles

Feature Image by Joshua Hoehne on Unsplash