Deep Dive Into Delegation - What Is It Exactly ?

[tchin-nin]

Hi Fellow PowerAppsers! 

This article is the first of a serie called Deep Dive into Delegation, where I try to deeply explain how it works and how to workaround with different datasources (SharePoint, SQL, CDS) 

1. What is it exactly? 
   
   

Everyone who have work on some medium to advance scenario in PowerApps have probaly heard about delegation, at least on this warning. 

The first thing to do to understand what is this mysterious thing, is to look at this article from the PowerApps documentation that provides a good overview of what is delegation. 

What is it ? 

One of the strength of PowerApps is all it's connectors that let you build apps around powerfull data systems, the most famous are Excel, SharePoint, SQL and the Common Data Service (CDS). The delegation is a PowerApps feature that makes possible to handle a large set of data within your apps while maintaining decents performances and resources consumption. 

When you're building an app, you can directly interact with your data source to display a list of items (or records, or entries,...) or an item informations. What is happenning is that, when it is possible, PowerApps will delegate the process of retrieving data to the datasource itself, making the datasource retrieve small sets of data to ensure performance consistence. When the user requests more data, for example by scrolling down the list of items in a Gallery, the datasource provides another small set of data to PowerApps to be displayed. You can see the phenomen happenning when you scrolldown a gallery plug on a SharePoint list, the loading dots appear on the top of the app,  meaning that more data are being loaded from the datasource. We can even quantify the small sets of data retrieved by the datasource by using a Label to display this number. 

How does it work ?

I have a simple gallery, plugged directly on my SharePoint List and a Label that counts how many items are in my gallery. When I load my gallery for the first time, let's say when I start my app, the label indicates that only 100 items are presents in my gallery. This is the first set of data retrieve directly by SharePoint.  

 

By scrolling down, I'm telling my app that I want to see more data from my datasource, so 100 more items are retrieve by SharePoint. 

 

I can scroll over all my 2000 items without having any performance issue because SharePoint has retrieved them step by step.

  

Not All Datasources Can't Handle Delegation

Yes, yes this is an awesome feature that guarantee that you can work with a lot of data as 90% of business applications require... but there is a but (at least 2 actually). It works only with some datasource : 

• SharePoint 
• SQL 
• Common Data Service 
• Dynamics 365 
• Salesforce 

If you plan to work with a lot of data (more than 2000), you can only consider working with the datasources listed above. It is not (yet?) possible with others. 

So what happens when my datasource is not delegable ? I'll use the same data but this time in an Excel file, so PowerApps won't be able to delegate the retrieving of data. 

This is where the "delegation limit" appears. Knowing that the datasource I'm using can't handle delegation, PowerApps has loaded, locally, in my apps internal temporary storage, a small amount of data : The first 500 items retrieved. Everytime I'll reference my Excel datasource, I will always look only at these 500 items, even if 2000 items are stored in my Excel table.   

Data are not loaded step by step (100 items at a time), I have directly access to the 500 first items of my table (because this is the sample stored locally), but I can't scroll down to see more than these 500 items. 

The delegation limit can be increased up to 2000 items. (File/Settings/Advanced) 

Not all operations can be delegated

I've highlighted before how delegation can work with the simpliest call to a delegable datasource : no Filter, no sorting, no data transformation. Can you see the second "but" coming ?  

If you are working with a large set of data, you'll be able to use only delegable operations (functions and operators). Just as PowerApps delegates the process of retrieving data step by step to the datasource, it will delegate as well all functions and operators you might use to retrieve your data.  

To know exactly which operations can be delegated to each DataSource, [this article] is the reference.  

For example, PowerApps can delegate a Filter function to SharePoint but only with "=" operator.  

Back to my SharePoint list, I will filter all expenses with the title "Lunch".  

 

No delegation warning on my formula means it can be delegated.  

I have in total 490 items with "Lunch" as a Title and I can see that a first small data set has been retrieved by SharePoint. SharePoint itself perform the filter on its own data, and retrieve the results to PowerApps.

   

After scrolling down, I'm able to go through all my items, retrieved small set per small set by the delegation in SharePoint. 

Now, what happen if the formula I wrote can't be delegated ? As soon as I'm using a function or an operator that can't be delegated, I'm falling down in the case of a non-delegable DataSource. Meaning that PowerApps will load locally the first 500 items and perform the non-delegable operation on that set of data only.  

For example, PowerApps can delegate a Filter function to SharePoint but not the "in" operator.  

On my SharePoint list, if I want to display all requests that Title contains "nch" (like in "Lunch"), I'll face a delegation issue.  

 

Because my formula can't be delegated, PowerApps reacts by loading locally the 500 first items and operates the filter on these set of data ONLY. 
 

In total I have 490 requests that contain "nch" in their Title, but only 177 of them are on the first 500 items of my list. And this is exactly what my gallery displays.  

At the end, I have an incomplete set of data that can't be exploited. 

Common misunderstanding 

 One of the biggest misunderstanding I can see is using a local PowerApps Collection to avoid delegation issue.  

In fact, by doing so you avoid delegation warning, but not the issue. Collections can't handle delegation (as it's a set of data stored locally), so collecting items from a big delegable datasource won't change anything because your collection will only contains the 500 first items of the DataSource. It is during the ClearCollect() function that the delegation issue appears, resulting that only the 500 first items are retrieved. And of course no delegation warning when using a non delegable function on a collection because there is absolutely zero delegation when performing operations on local data, but you will just perform that operation on a limited set of data. 

The second common misunderstanding is that delegation happens only when retrieving a set of data (an array, a table, a list,…), so retrieving a single item is not subject to delegation issue. This is wrong, delegation happens anytime you have an interaction with an external data source. The best example is the Last() function that retrieve only one item, the last of the table. On my 2000 items SharePoint list the formula "Last('Expense Requests').ID" retrieve "500" not "2000" as I could expect : It is the last of the 500 items retrieved locally because the formula is not delegable.  

On the next articles of this series, now that the concept of delegation is cristal clear, I'll go deeper on the delegation issues you may encounter with the most common datasources, and how to workaround them.  

Feel free to comment or DM me if some aspects of the delegation are not clear.

Théo