Delegable or not Delegable, that is the query!

Silly title I know – couldn't help myself! The significance of this topic is certainly not silly - it could give your app trouble free scalability or … well not.

Delegable queries in Canvas Apps

A delegable query is simply a query that offloads the heavy lifting to the connector source rather than downloading all the data and doing the processing locally in your Canvas App.

A good example is when using the filter command. You can associate a gallery to the Accounts entity via the command:

Filter(Accounts, AccountSearchText.Text in name)

This will result in a fetchxml query condition sent to CDS of:

<filter type="and">
    <condition attribute="name" operator="like" value="%contoso%" />
</filter>

You can also use:

Filter(Accounts, StartsWith(name,AccountSearchText.Text))

Which will give the fetchxml filter:

<filter type="and">
    <condition attribute="name" operator="like" value="Contoso%" />
</filter>

All these queries are delegable and are the most optimal for large datasets.

Consider however, if you use a non-delegable predicate such as:

Filter(Accounts, Len(name)>5)

This will result in the yellow-triangle-of-doom in the Canvas App designer with a tool tip saying:

"Delegation warning. The highlighted part of this formula might not work correctly with column "name" on large data sets…"

This is because Len is not in the list of supported delegable predicates – you can see the complete list in docs.microsoft.com (I've submitted a pull request to update this list because CDS and Dynamics 365 connectors actually have much better support than when first released)

Optimising for large queries

If you do need to perform one of these non-delegable predicates in your filter, you can also combine the filter with an initial query that pulls down a smaller subset using a predicate that is supported. To do this you need to nest the filters:

Filter(
    Filter(Accounts, AccountSearchText.Text in name),
    Len(name)>5
)

Delegable Queries in Flow

If you browse the Dynamics 365 templates available for Flow, you'll see a flow called 'Add notes to a Dynamics CRM contact'

This flow basically allows you to search for a contact via a flow button and add a note to the matching contacts. The flow is simply:

Query Contacts
For each contact
- If the first name and last name match those entered when triggering the flow
  - Create a note

Seems straightforward until you consider delegable queries. If you have thousands of records, flow will attempt to download them all and loop through them one by one since the conditions are not delegable to the initial query.

You'll see the effect of this if you test the flow and look at the number of records in the Apply to each. There are 29 records in this CDS instance and all are returned by the query since the query isn't delegated.

This solution is definitely not scalable, and I'm surprised it's in the list of templates offered! To make the query delegable, the connector must be edited to include an OData query:

This query will then scale as the number of contacts grows in your database because the heavy lifting of the query is delegated to the CDS database. These kind of performance considerations are important when building apps that will scale with the data-source. PowerApps makes it super easy to build amazing user experiences, but we sure to keep an eye on the App Checker ( ) since it's full of suggestions that'll keep your app running smoothly.