Categories: Microsoft 365

Delegation Warnings in Power Apps

We have all seen them, Delegation Warnings, but what is delegation and how do you clear your Power Apps from these warnings

Delegation

I’m going to start this post by explaining Delegation.

In Power Apps you can connect to all sorts of data sources. Often people use SharePoint, SQL Server or Dataserve ( a.k.a. Common Data Services) to store their data for their Power Apps.

Within the apps you can now collect all the records and then use a filter to select the items.

ClearCollect(colActiveOrders, 
             Filter('Orders', 
                    IsBlank(Status.Value)
             )
);

If a function within a connector is delegable then the processing can be done at the datasource end. If not, well … then your app will be doing all the hard work.

What do you think is better?

In the olden days when there was SharePoint on-premises ( Sorry if you still are in the olden days, I can help you with that! ), we had the choice to run code server side or client side. This is very similar.

Delegation warnings

There are many variations of the Delegation warnings. In this section I’m looking at a few examples.

The “Filter” part of this formula might not work correctly on large data sets

The highlighted part of this formula might not work correctly on large data sets. The “CountRows” operation is not supported by this connector.

The highlighter part of this formula might not work correctly with colum “…” on large data sets.

When to worry about Delegation Warnings

You might have noticed that all of these warnings use the word might, so there will be some cases that things might not work!

For each connector that you use you will have to check if they support delegation.

On the SharePoint connector documentation page there is a clear overview of the delegable functions.

Note that there are a few notes there.

One of the improtant ones are, formula such as

Filter(..., IsBlank(CustomerId))

won’t delegate to SharePoint. the following however will delegate:

Filter(..., CustomerId = Blank())

Interesting! so as you code your solutions. Don’t just make it work, make it work better!

SQL Server

Did you think that SharePoint lists was good enough to store your data, have a look at SQL Server.

The list of delegable functions is so much larger.

So the first recommendation is to use SQL Server.

And even the Common Data Services connector or Dataserve will potentially be a better option than SharePoint.

When not to worry

If you use smaller datasets, then you don’t need to worry too much. Remember when you bought that new hard-disk fro your new computer in the 90s? You would never fill that one. Now you probably fill that disk with one document.

How to fix delegation warnings?

First of all when you use a datasource that supports delegation make sure that you get familiar with the delegable functions. then as you find the delegation warnings in your app find alternative ways of doing the same. Like the earlier mentioned isBlank function.

Another example i will have a look at now. Typically , you could do an update by using some code like this:

Patch('Project Risks', First(Filter('Project Risks', ID = ThisItem.ID)),{....})

You will now see a delegation warning on the first function if you use SharePoint lists.

Remember why we use delegation? to reduce data going across the network.

How about using the following code instead:

We first collect the item using the filter function and stick the single item in a collection. And as we know that we will only get one item back, We can now use just the First item.

Yes, the code could still be optimized to the following, where we skip the Set.

ClearCollect(colItemsToUpdate, Filter('Project Risks', ID = ThisItem.ID));
Patch(
    'Project Risks',
    First(colItemsToUpdate),
    {
        Title: inpTitle.Text,
...

If you however cannot get rid of the warnings, then leave a comment below.

Share
Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

View Comments

  • Hi Pieter,

    We're using SharePoint instead of SQL Server because of the licensing costs around it. One thing that doesn't seem to be delegable with SharePoint is using the "in" operator as in a string being contained in a single-line of text column. StartsWith works fine but it's not exactly the same.

Recent Posts

Filter Query in SharePoint Get Items in Power Automate

Have you tried the experimental version of Filter query in the SharePoint Get Items action…

2 days ago

3 Ways to add a column to an array in Power Automate

When you have an array in Power Automate and you would like to add a…

6 days ago

Patch any field type in a SharePoint list item using Power Apps

In Power Apps the Patch function is very important when you work with data. In…

1 week ago

Call flows from a model driven app

Wouldn't it be great if you could call flows from a model driven app? Well…

2 weeks ago

Variables or Compose? Consider neither in Power Automate

The question is Variables or Compose actions in Power Automate? Variables Sometimes you need a…

2 weeks ago

Your 1st lesson in Power Apps, in a few easy steps!

1st lessons can be so important when you want to get started with Power apps.…

4 weeks ago
%%footer%%