a deep dive into Delegation

It’s time to talk about Delegation.

Delegation is a critical concept in Power Apps: building canvas apps is all about bringing together data across the myriad connectors, so naturally getting all of the expected data for your Power Fx logic to ingest or end users to browse is core to a good app. However, I often see Delegation— *ahem*— relegated to advanced trainings only, or even skipped altogether.

Planning around Delegation should be a first step, not an afterthought. It can and will have an impact on your data architecture, and can sometimes crank up an otherwise simple app design to an unfeasible level of complexity!

what is delegation, anyway?

When you build a canvas app, you’re building a user interface without a back-end database. The attraction of going with a canvas app, of course, are those 600+ Connectors available across the Microsoft ecosystem and beyond— so we can bring data into the app without having to build a database layer!

Note: While you can upload data from Excel directly into the canvas app, or create manual Collections in your Power Fx code, those options are not actual databases. They also have limited utility for real use cases.

So, whether we’re leveraging the Power Platform’s in-built Dataverse, creating something lighter with Dataverse for Teams or a SharePoint List, or going for a third-party data source through a connector, our canvas app will be sending requests for data to the source via Power Fx code. Whenever we write a formula which references a table directly or create an operation to retrieve or transform data from the source, the canvas app client is sending those requests to the data source server. When the server responds, the client holds the received data in memory and displays it for your end user interactions.

The Power Apps client itself is obviously not as powerful as a back-end database. It has limited memory to cache and process data, and can’t scale up on-demand like Azure SQL to meet high-throughput processing demands. That being said, the client can easily handle simple requests, like setting a Gallery Items property to [@Accounts]. This results in a single API call to Dataverse without filtering. (The SQL equivalent would be SELECT [columns] FROM account;).

But what if you don’t want every single Account displayed in your Gallery? When you introduce a filter into your Gallery Items property, things can get complex very quickly:

Filter(
    [@Accounts],
    And(
        'Status Reason' = 'Status Reason (Accounts)'.Active,
        Or(
            'Address 1: City' = "Seattle",
            'Address 1: City' = "Redmond"
        )
    )
)

(The SQL equivalent to this one is SELECT [columns] FROM account WHERE statuscode = 1 AND (address1_city = "Seattle" OR address1_city = "Redmond"); ).

In order for the Power Apps client to figure out your request, it has to load in all of the Account data into memory and start filtering out inactive Accounts and processing all the rows by the value in the City column to see whether the row is located in Seattle or Redmond. That’s a very expensive process! So, Microsoft coded in a row processing limit to make sure Power Apps doesn’t lock up or time out trying to process more rows of data than it can handle (or that your end user would likely tolerate). The default setting when you create a new canvas app is only 500 rows, and although you can increase it, the absolute upper limit is only 2000 rows.

In the data world, 2000 rows is almost nothing! Think about your own customer base: how many customers, past and present, would you be filtering through? What about transactional data, like purchase history? In my experience, most business scenarios would be looking at tens of thousands, if not hundreds of thousands or millions of rows across a core data estate. 2000 rows just does not get us far enough for canvas apps to be useful, outside a handful of super simple applications.

That’s what Delegation is for. When a Power Fx command is said to delegate for a particular data source, that means it is sending the request to the source server and saying, “You filter it with these conditions, and send me back what you find.” This invokes the powerful filtering or searching features of the server, and the Power Apps client simply receives the data package back at the end. Since the client doesn’t need to do additional processing on delegated requests, the 2000 data row limit doesn’t apply.

When a command does not delegate for that data source, the client sends a request to the server for the entire table and then tries to apply your filter conditions. Given the 2000 delegation data row limit, this can have disastrous consequences!

practical scenario

To further illustrate the impact that Delegation can have, I put together a scenario for a hypothetical app I might build.

My field sales team needs an app which lets them interact with customer data. They want the ability to browse and search lists of customers in their territory, view & search recent purchase history, add new customers and update existing customer data, and pull up sales material on the product catalog. They need to be able to apply multiple filters to the product catalog so they can show the most relevant product list to the customer. My company is multi-national and has been in business for a few decades, so over the years our CRM database has collected many thousands of records that I’ll need to filter out so the app is easy to use and performant on the sellers’ mobile devices.

Delegation will come into play for almost all the tables I need to interact with:

• The customer list needs to be filtered for active customers in the current user’s territory.
• The purchase history needs to be filtered by the parent customer, and searchable.
• The product catalog needs to be filtered dynamically based on the conditions selected by the user.

The good news is that Filter(), Search(), LookUp() and SortByColumns() are all delegable functions for some data sources. I should be able to pare down the data to what the field sellers need, and allow them to search, filter and pull up specific data as they use the app.

The bad news is that I have be careful how I write my Power Fx code, because I could end up negating the delegation support and cause it to be processed locally instead!

For example, let’s filter the Purchase table by the parent customer. This formula Filter(Purchases, Customer.ID = CustomerGallery.Selected.ID) will not delegate to Dataverse. The customer “ID” field is on the Account table, not the Purchase table, and filtering by related record fields is not delegable. However, this formula Filter(Purchases, Customer = CustomerGallery.Selected) should delegate, since the Customer field is a Lookup column on the Purchase table.

Additionally, combining a delegable command with a non-delegable one will sadly negate the entire operation’s delegation. If I wanted to filter the seller’s customer list for them when the app starts up and then take it offline, I would need to store the data in a local Collection. However, Collect() and ClearCollect() do not delegate for any data source. It wouldn’t matter if the Filter() formula I write would delegate on its own— as soon as I wrap it into a ClearCollect(), I will see that blue line delegation warning in my Power Fx code.

Otherwise known as the “Blue Squigglies of Impending Doom”, because while you might not face immediate doom during dev, once your app is live that will almost always come back to bite you.

@ohthreesixfive

what does an end user see?

A field seller brings up the product catalog in front of an existing customer. They filter the list to show just the products which the customer is interested in. There are 8560 total products in the entire catalog, and the customer wants to see the Premium products which are blue, and only the medium and large sizes. There should be 450 products shown in the Gallery based on on the attributes.

When the data source support Delegation for the formula, here’s what the server returns:

When the data source does not support Delegation for the formula, here’s what the server could return:

When your query isn’t delegated to the server, there’s no way to control what is sent back to the client. I came up with the few examples above for illustration, but the reality is that you could get back any random data set! It could be the first 2000 records created, or the most recent 2000 records. Each data source might handle the request differently.

After the server sends back these 2000 records, the Power Apps client will apply your filter conditions to see what remains. In outcomes 1-4 & 6, the result would show the field seller 0 records, because the server didn’t happen to send back any products which were Premium AND blue AND medium or large. In outcome 5, 254 of the 2000 items returned did meet the criteria of medium or large blue Premium items. But there were a total of 450 in the original data set, so although we are showing some results to the seller, they’re missing 196 products to show to the customer.

As you can hopefully now understand, not planning around delegation in my field sales app would render it unusable for my team. An app which shows users incomplete or totally inaccurate data is one that will be quickly abandoned.

how do we get around delegation issues?

1. Choose your data source wisely. Learn about the various tabular data sources you could use for your app, and pick one which has a lot of delegation support. The official documentation references Dataverse, SharePoint, SQL Server and Salesforce as sources with broad delegation support.
2. Limit your app’s scope. Canvas apps are intended to be task-focused apps. If you’re rebuilding an entire CRM within a canvas app, you will likely have a bad time (with delegation, and with other platform limitations, too!)
3. Write clean Power Fx formulas. Make sure you are leveraging variables and Collections where appropriate. Trim down complicated statements where delegable functions are involved. Simplify your code, and your requirements when necessary (see #2 above). If you’re working with Dataverse, use Dataverse views to filter data for you instead of writing a Power Fx filter formula.
4. Offload complex data fetching workloads to Power Automate. Although it can add data load drag to app or screen start-up times, Power Automate is a powerful tool to fetch, filter and transform data asynchronously. Using ODATA filtering and composition actions with custom expressions, a cloud flow can interact with a data source server on a much more in-depth level than a canvas app. You can use the HTTP response action to send a JSON of the final data set back to a canvas app, eliminating the need for client-side processing and avoiding the 2000 record local processing limit.
   • I included instructions on this method in my offline guide, as this is the only way to get larger data sets stored into Collections for offline use.

key takeaways

• Delegation is about data.
  • We’re impacted by delegation warnings when we’re working with sets of data: Galleries, Collections, filtering and searching. You don’t need to worry about this for other kinds of operations like screen Navigation or component interaction when building your apps.
• Different data sources handle delegation differently.
  • With the wide variety of connectors available out of the box, your mileage may vary with delegation support. The data sources under Microsoft’s control (Dataverse, SharePoint List, SQL Server) have the best delegation support, while third-party connectors and custom connectors are likely to have no support at all.
• You must understand your expected production data volumes.
  • Simple apps might never need to consider delegation in their design, but almost all business scenarios are going to exceed the 2000 record threshold at some point. Your short lists of fixed reference data can safely ignore a delegation warning, but make sure you have a plan for large and variable numbers of rows in your data.
• Delegation is a decision about who will process the operations: the Power Apps client or the data source server.
  • Our goal is to delegate the operation to the server. The server will process the request at scale, returning the entire package of the requested data. Except in limited circumstances, having the Power Apps client process the operation is extremely undesirable. Put in the additional effort to create a workaround so that users aren’t presented with missing or incomplete data.
• Delegation is not an optional consideration.
  • Assuming you’re building an app for your organization using real business data, delegation is integral to app design. Embed delegation planning throughout your application lifecycle.
  • And please don’t forget about testing! Your test environments need to have similar data volumes as you expect in production. UAT is a perfect time to craft user test scripts intended to test realistic use cases which could trigger delegation issues.

As always, good luck and happy app building! Please drop a comment below to share your own experiences or ask any questions you may still have about delegation.