cancel
Showing results for 
Search instead for 
Did you mean: 
tchin-nin

Deep Dive Into Delegation - What Is It Exactly ?

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. 

GetImage.jpg


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.  

 

 GetImage (1).jpg

 

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. 

 

 GetImage (2).jpg

 

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

 

 GetImage (3).jpg 

 

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. 

  

GetImage (4).jpg

 

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.   


GetImage (5).jpg

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".  

 

 GetImage (6).jpg

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.

   GetImage (7).jpg

GetImage (8).jpg

 

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.  

 

 GetImage.png


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 

Comments

Hey @tchin-nin

great article! I wish to read it 2 weeks ago when i really had to understand how delegation works.

 

If I could paraphrase what you’ve described in second common misunderstanding: 

- delegation is not about “how many data you need”. It’s about “how many data you need to search through to get what you need”

 

Looking forward for part 2 of your article.

Nice article. 

 

I highly recommend using flow with stored procedures when using a SQL Server data source. No need to decide what can and cannot be delegatable and limitless options for filtering data, performance is significantly greater. It does carry the extra overhead of flow, but leave data management code where it belongs, in SQL Server.

 

 

 

Hi @tchin-nin !

In this article of the MsDoc it states that operators such as > <  are supported, as well as other ||, ! and so on.

 

In this post you state that delegation with filter functions only work with =.

Which one is the right one? I am actually having trubles with the all operators beside =, and that makes it hard to filter i.ex. "all items created < or > a certain date.

 

Thanks for the help!

 

//Alessandro

Kudo Commander

@tchin-nin  This is a really good blog article.

can we please get a part 2?    🙂

 

I really want to see how to get around Delegation problems with SharePoint Online data sources.

 

I thought i had a work-around by using ClearCollect / UpdateIf / Patch... but as your part 1 shows and i've confirmed in my app, ClearCollect doesnt work 😞

 

thank you 🙂

@Cam Collecting always breaks delegation 😞

@tchin-nin thank you for the detailed information on Delegation. Could you please clarify my below queries on delegation please... here you go

  1. Since some of the data sources only applicable for delegation, all the filter/sort/search operation we perform on the data happens at source first and then the results are taken back to the app?
  2. This doesn't applicable to non-deleable data sources
  3. Under "advanced settings" of an app, we have an option to increase the default rows to 2000 from 500. But I read that "set how many rows are retrieved from server-based connections where delegation is not supported, which means this applicable only for non-delegable data sources. In this case, lets say if we have a CDS which has 10K records, if we use a filter which returns 501 results (default rows are set to 500), the filter applies first on 10K records in the data source and then the 500 results are taken back to app.
  4. In your blog, you mentioned that the data from a delegated source is loaded into app in a step by step process. Eg: 100 for the first time and then another 100 next time on gallery scroll. How can we configure this only these many (count) to be loaded into app for the first time?

So, How can we get over the limitation of the SQL database ??

 

See, my data source is SQL & I want to search the database using Id or name of employees. I created Combobox that concatenate id,name. By using the AddColumn function it will join them into one column.

 

then the power app will import only 2000 of the data. 

Is the only we to make power app support delegation for SQL database, by using the Filter, Search, etc. function?

@FatimahAlQadib you probably don't want to get over the delegation limit, it will impact the performances of your apps significantly. 

What you can do if you use a SQL datasource, is to play with stored procedures to make SQL do the job of shaping and filtering your data

Anonymous

I wish this would work as everyone would expect..

PowerApps sends a query to the data source with a filter, order by etc.. and then gets data back.

Having to do hacks and workarounds using custom string columns is tiring and onerous, boolean values still don't work either do calculated columns.

Why should retrieving data, using either Collect or ClearCollect not just accept all the data returned from the query, or maybe we need to enable using the "NEXT" pagination option? Using a Gallery where you have to scroll down to get all records sometimes is not feasible.

Also, we have apps that have to work offline with all the data required in our app, sometimes this might be more than 2000 items.

I think PowerApps is great, it has to just resolve these issues which are fundamental to what we are trying to build for our clients, who are looking for more and more powerful solutions/tools.

Hi,

I am currently having an issue with SQL Server On-Prem as it is non-delegable when I Filter with IN operator.

Just wondering if it is going to be delegable in Azure SQL Server?