PowerApps Tip Corner – Know your Delegation!

While PowerApps is a very powerful tool, and can connect to MANY data sources, one of the biggest limitations is in it’s ability to pull back records from a data source.  For performance reasons, PowerApps will only pull in the first 500 records from any data source.  This allows the App to perform well, but also means that you as a PowerApps designer must be aware of how to filter your results to meet this threshold.

PowerApps has the ability to offload some filtering to the back end data source.  For example, instead of having the PowerApp filter any results you may return, it can send the query to the data source, have it filtered there, and then only return those filtered results.  Essentially offloading the heavy work to the data source back end.

However….

Currently, the delegation abilities for PowerApps are not as robust as they need to be.  This table outline which data sources can be delegable, and specifically the OPERATORS that can be delegated:

Top-level delegable functions

Common Data Service SharePoint SQL Server Dynamics 365 Salesforce
Average No No Yes No No
Filter Yes Yes Yes Yes Yes
LookUp Yes Yes Yes Yes Yes
Max No No Yes No No
Min No No Yes No No
Search Yes1 No Yes Yes Yes
Sort Yes Yes Yes Yes Yes
SortByColumns Yes Yes Yes Yes Yes
Sum No No Yes No No

1For string fields only

Filter and LookUp delegable predicates

Common Data Service SharePoint SQL Server Dynamics 365 Salesforce
Not Yes No Yes Yes Yes
IsBlank No No Yes Yes No
TrimEnds No No Yes No No
Len No No Yes No No
+, – No No Yes No No
<, <=, =, <>, >, >= Yes Yes (only =) Yes Yes Yes
And (&&), Or (||), Not (!) Yes2 Yes (except Not(!)) Yes Yes Yes
in No No Yes No Yes
StartsWith No Yes No No No

2For operators only. And/Or/Not function not delegated.

As you can see above, I can only filter a SharePoint list by using ‘=’ or StartsWith.  Only SQL Server can delegate ‘+’ or ‘-‘.  You can see how not having the full range of delegable operators, and then being limited by the first 500 Items in a data source could cause serious heartburn.

I know the PowerApps team is hard at work to find solutions for these, so I am hopeful that in the future this becomes a non-issue.  But for now, know hat you can and can’t do with a back end data source before you start designing your PowerApp, it will save you time and energy in the future!

Please check out some of my other articles to take a deeper dive into the product, and as always, please feel free to reach out to me at jo.karnes@centricconsulting.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: