PowerApps: Delegable Functions Reference

By default, PowerApps tries to deal with the information in the client. For a small number of records, the client can quickly deal with operations like Sort or Filter without issues, but as the data increases, we could end up with crashes and slow apps, especially for slow networks. To improve performance, PowerApps enables the delegation of some operations to compatible data source called delegable functions. PowerApps only fetches from the data source the data that it needs to show. Instead of the full data set. You can find the full Microsoft’s reference explaining this concept.

I’ll go into further detail explaining workarounds for non-delegable functions in Delegation Issue and Workarounds and teach how to take advantage of delegable functions fully. Be aware that, for non-delegable functions, there is a limit of records fetched of 500 that you can increase up to 2000.

Delegable Data Sources

There are a lot of connectors available, but only a minimal subset of them enable delegation. These are:

  1. Common Data Services
  2. Dynamics 365
  3. Salesforce
  4. SharePoint
  5. SQL Server
  6. Exceptions

Let’s explore each one.

Common Data Services

Number Text Option Set Date Time GUID
NO NA NA NA NA
+ NO NA NA NA NA
< NO YES NO YES NA
<= NO YES NO YES NA
<> YES YES YES YES YES
= YES YES YES YES YES
> NO YES NO YES NA
>= NO YES NO YES NA
And (&&) YES YES YES YES YES
Average YES NA NA NA NA
EndsWith NA YES NA NA NA
Filter YES NA YES YES YES
In NO NO NO NO NO
IsBlank YES YES YES YES YES
Left NA YES NA NA NA
Len NA NO NA NA NA
LookUp YES NA YES YES YES
Lower NA YES NA NA NA
Max YES NA NA NO NA
Mid NA YES NA NA NA
Min YES NA NA NO NA
Not YES YES YES YES YES
Now() NA NA NA NO NA
Or (||) YES YES YES NA NA
Replace NA YES NA NA NA
Right NA YES NA NA NA
Search NA NA YES NA NA
Sort YES YES YES YES NA
SortByColumns YES YES YES YES NA
StartsWith NA YES NA NA NA
Substitute NA NO NA NA NA
Sum YES NA NA NA NA
Today() NA NA NA NO NA
Trim NA NO NA NA NA
TrimEnds NA NO NA NA NA
Upper NA YES NA NA NA

Limitations:
1. Number cannot contain arithmetic expressions. To work your way around this just check Delegation Issue and Workarounds.
2. Sum, Min, Max, Avg are limited to 50 000 records.

 

Dynamics 365

Number Text Date Time
NO NA NA
+ NO NA NA
< YES YES YES
<= YES YES YES
<> YES YES YES
= YES YES YES
> YES YES YES
>= YES YES YES
And (&&) YES YES YES
Average NO NA NA
EndsWith NA NO NA
Filter YES NA YES
In NO NO NO
IsBlank YES YES YES
Left NA NO NA
Len NA NO NA
LookUp YES NA YES
Lower NA NO NA
Max NO NA NO
Mid NA NO NA
Min NO NA NO
Not YES YES YES
Now() NA NA NO
Or (||) YES YES NA
Replace NA NO NA
Right NA NO NA
Search NA NA NA
Sort YES YES YES
SortByColumns YES YES YES
StartsWith NA NO NA
Substitute NA NO NA
Sum NO NA NA
Today() NA NA NO
Trim NA NO NA
TrimEnds NA NO NA
Upper NA NO NA

 

Salesforce

Number Text Date Time
NO NA NA
+ NO NA NA
< YES YES YES
<= YES YES YES
<> YES YES YES
= YES YES YES
> YES YES YES
>= YES YES YES
And (&&) YES YES YES
Average NO NA NA
EndsWith NA NO NA
Filter YES NA YES
In YES YES YES
IsBlank NO NO NO
Left NA NO NA
Len NA NO NA
LookUp YES NA YES
Lower NA NO NA
Max NO NA NO
Mid NA NO NA
Min NO NA NO
Not YES YES YES
Now() NA NA NO
Or (||) YES YES NA
Replace NA NO NA
Right NA NO NA
Search NA NA NA
Sort YES YES YES
SortByColumns YES YES YES
StartsWith NA NO NA
Substitute NA NO NA
Sum NO NA NA
Today() NA NA NO
Trim NA NO NA
TrimEnds NA NO NA
Upper NA NO NA

SharePoint

Number Text Boolean Date Time
NA NO NA
+ NA NO NA
< YES NO NO NO
<= YES NO NO NO
<> YES NO NO NO
= YES YES YES NO
> YES NO NO NO
>= YES NO NO NO
And (&&)
Average NA NA NA
EndsWith NA NA NA
Filter YES YES YES NO
In NA
IsBlank NO NA
Left NA NA NA
Len NA NA NA
LookUp YES YES YES NO
Lower NA NA NA
Max NA NA
Mid NA NA NA
Min NA NA
Not
Now() NA NA NA
Or (||) NA
Replace NA NA NA
Right NA NA NA
Search NA NA NA NA
Sort YES YES YES YES
SortByColumns YES YES YES YES
StartsWith NA NA NA
Substitute NA NA NA
Sum NA NA NA
Today() NA NA NA
Trim NA NA NA
TrimEnds NA NA NA
Upper NA NA NA

SQL Server

Number Text Boolean Date Time GUID
YES NA NO NA NA
+ YES NA NO NA NA
< YES NO NO NO NA
<= YES NO NO NO NA
<> YES YES YES NO YES
= YES YES YES NO YES
> YES NO NO NO NA
>= YES NO NO NO NA
And (&&)
Average YES NA NA NA NA
EndsWith NA NA NA NA
Filter YES YES YES NO YES
In NA YES NA NA NA
IsBlank NO NO NO NO NO
Left NA NA NA NA
Len NA YES NA NA NA
LookUp YES YES YES YES YES
Lower NA NA NA NA
Max YES NA NA NO NA
Mid NA NA NA NA
Min YES NA NA NO NA
Not
Now() NA NA NA NA
Or (||) NA NA
Replace NA NA NA NA
Right NA NA NA NA
Search NO YES NO NO NA
Sort YES YES YES YES NA
SortByColumns YES YES YES YES NA
StartsWith NA YES NA NA NA
Substitute NA YES NA NA NA
Sum YES NA NA NA NA
Today() NA NA NA NA
Trim NA NA NA NA
TrimEnds NA NA NA NA
Upper NA NA NA NA

Limitations:

  1. Direct data filters don’t work on SQL Server To work your way around this just check Delegation Issue and Workarounds.
  2. In will only work if string value" in <column>
  3. StartsWith will only work if StartsWith(<column>, "string value")
  4. EndsWith will only work if EndsWith(<column>, "string value")
  5. Beware of char and nchar tables. Len for example, will always return 10 in a char(10) column. A column with a value “hello” will return false for EndsWith(<column>, "llo")

 

Exceptions

Finally, let’s go over some cases where PowerApps will fetch the information and store it directly in memory. In other words, these are not delegable data sources, but we can take full advantage of PowerApp’s language:

  1. Excel workbooks
  2. Collections
  3. Tables stored in context variables.

 

Feature Photo by Todd Quackenbush on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

%d bloggers like this: