Delegation warnings and delegable functions

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

Delegation warning. 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.

Delegation Warnings in Power Apps Microsoft Office 365 image 20

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

Delegation Warnings in Power Apps Microsoft Office 365 image 23

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.

Delegation Warnings in Power Apps Microsoft Office 365 image 25

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.

Delegation Warnings in Power Apps Microsoft Office 365 image 26

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.

Delegation Warnings in Power Apps Microsoft Office 365 image 27

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:

Delegation Warnings in Power Apps Microsoft Office 365 image 51

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.

13 thoughts on “Delegation Warnings in Power Apps

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

  2. I am getting warnings on “first” function – How do i optimize this:

    If(IsBlank(SharePointIntegration.Selected)|| IsEmpty(SharePointIntegration.Selected),First(‘insert list name’),SharePointIntegration.Selected)

  3. Any thoughts on how to work around the delegation warning when attempting to filter on an in-built SharePoint column like Content type (aside from creating a whole new column in SharePoint and duplicating the data, which seems absurd)?

    For instance, say I have a library with different content types within it, and I want to find only documents of a particular content type, this would raise a non-delegable warning: Filter(DocsLibrary, ‘Content type’.Id=”0x0120D52000B3982394C1114596410ACBE842938B00DF2BBD30DB5B2453B0E3E63C17″). So would Filter(DocsLibrary, IsFolder=true).

    1. Hi Liam, is this a library with many items in it? Is delegation something that you need to worry about?

      I just tried to filter by content type and I didn’t manage to get that to work.

  4. @Pieter Yes, there are many items in this library, though mostly in sub-folders. If there was a way to only retrieve the top-level folders and search on that set, that might provide a workaround. Perhaps using a flow to return a set of matching items could work, but I can’t imagine it would perform well if it has to be called every time the app is used.

    Any idea why the built-in SharePoint fields cannot be used in delegation? Are they technically Complex fields or something?

    (By the way, I can’t seem to get in-line replies to work on this site – no comments box appears when I press reply, regardless of the browser I try.)

  5. I’m getting a delegation warning filtering an SharePoint (Online) list by a datetime column. Your chart says it’s should work. The code looks like this-

    Filter(‘My Calendar’, ‘Start Time’ >= Today())

    ‘Start Time’ is a SharePoint “Date and Time” column. The warning is “The highlighted part of the formula might not work correctly for column ‘Start Date’ on large data sets.”

    Any ideas? Thanks!

      1. I wonder if it is because it’s a calendar? Even though a calendar is a list, of course.

  6. Sorry if this is a duplicate, not sure if my original answer posted… yes, the column is indexed and I’ve refreshed the data source several time since I indexed it. Thanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: