Perform complex Dataverse FetchXml queries using Power Fx (from a Canvas App)

One of the constant challenges we face when writing canvas apps and custom pages using Power Fx is ensuring that the queries we use are always delegatable. Not all operators are delegatable to the server when using Filter or Sort, which can sometimes create a performance challenge. Furthermore, some types of queries are not possible such as group-by and complex link entity queries. Wouldn't it be great to be able to run a FetchXML query and then use the results in your app? In this post, I'll show you a pattern that allows you to do just that using the new PasrseJSON Power Fx function.

Creating the query mechanism

We will use a Cloud Flow to perform the FetchXml query, which will be called from Power Fx. This side-steps any delegation issues since we know that the query is always run on the server.

1. Create a new instant cloud flow (I will call it 'PowerApp: FetchXml Query'). Select Power Apps as the trigger.

2. Add a Dataverse List Rows action, and then in the Table name and Fetch Xml Query parameters, add Ask in Power Apps under Dynamic Content.

3. Add a Respond to Power Apps action, Add a string output and enter the expression:

Your flow should look similar to the following:

Perform query using Power Fx

Imagine that you wanted to find all the distinct accounts that had at least one contact with an activity that is due in the current month. This would be difficult using standard Power Fx since you would need to perform multiple queries, possibly causing performance issues. 

1. First we create the query using the Accounts view in a model-driven app. The query looks like the following:

2. Now we can use Download FetchXML to get the query. You can also use the awesome FetchXML builder from Jonas.

3. Inside your canvas app, enable the ParseJSON feature inside Settings:

NOTE: You will need to save and reload your app for this setting to take effect.

4. Under the Power Automate tab, add the FetchXml Query Cloud Flow so that it is available to our app.

5. Inside your app, make a call to the following Power Fx. This could be inside a Screen OnVisible, or a button:

// Get all accounts with at least one contact that has an activity due this month
UpdateContext({ctxAccountsThisMonth:
    ForAll(
        Table(
            ParseJSON('PowerApp:FetchXmlQuery'.Run("
             <fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='true'>
                <entity name='account'>
                    <attribute name='name' />
                    <attribute name='accountid' />
                    <filter type='and'>
                    <condition attribute='statecode' operator='eq' value='0' />
                    <condition attribute='industrycode' operator='not-null' />
                    </filter>
                    <link-entity alias='accountprimarycontactidcontactcontactid' name='contact' from='contactid' to='primarycontactid' link-type='outer' visible='false'>
                    </link-entity>
                    <link-entity name='contact' alias='aa' link-type='inner' from='parentcustomerid' to='accountid'>
                    <link-entity name='activitypointer' alias='ae' link-type='inner' from='regardingobjectid' to='contactid'>
                        <filter type='and'>
                        <condition attribute='scheduledend' operator='this-month' />
                        </filter>
                    </link-entity>
                    </link-entity>
                </entity>
                </fetch>
             ",
                "accounts").results
                )
            ),
            {
                accountid:GUID(Value.accountid),
                name:Text(Value.name)
            }
        )
    })

This code simply calls the Fetch XML Query and then maps the results into a collection. It picks out each attribute from the results and converts it to the correct data type (e.g. Text or number).

You can now use this data in your app! Each time you call this flow, the query will be re-evaluated without any caching so be careful how many times you call it in order to minimise API entitlement consumption. When you want to join the results back to the standard Power Fx Accounts data source, if you show the results in a gallery you could use:

 LookUp(Accounts,Account=Gallery1.Selected.accountid).'Account Name'.

Performing aggregate group by queries from your canvas app

Imagine that you wanted to show the total number of accounts per industry code inside your app. You could easily use a Power BI query for this - however, there are sometimes when you need the data to be native to the app.

Using the awesome FetchXML builder, you might create the Fetch XML to look similar to:

<fetch aggregate='true'>
  <entity name='account'>
    <attribute name='industrycode' alias='group' groupby='true' />
    <attribute name='industrycode' alias='industrycode_count' aggregate='count' />
  </entity>
</fetch>

If you use this in your Power Fx in a similar way to the code above, you will find that the Cloud Flow gives an error similar to:

An ODataPrimitiveValue was instantiated with a value of type 'Microsoft.Xrm.Sdk.OptionSetValue'. ODataPrimitiveValue can only wrap values which can be represented as primitive EDM types.

The reason behind this is that the Dataverse connector for Flow does not know how to interpret the metadata being returned from the aggregate query since each row is not an account, but an aggregate row. You will get a similar error if you try and group by a Lookup:

An ODataPrimitiveValue was instantiated with a value of type 'Microsoft.Xrm.Sdk.EntityReference'. ODataPrimitiveValue can only wrap values which can be represented as primitive EDM types.

To work around this we must create a 'primitive' data type column to group by. So for an OptionSet/Choice, we would create a numeric column that contains the Choice Value and for a Lookup, we would create a String column that contains the Lookup GUID. We can then group by these columns. I call these 'materialized columns'. To do this you could either create a Plugin or a Cloud Flow The cloud flow should be triggered when a record is created or updated, and then update the two materialized 'primitive' columns.

1. First create the Cloud flow to be triggered when an account is created or updated:

2. Add a second Dataverse Update a row step, and set the:

Row Id : triggerOutputs()?['body/accountid']

Account Status ID (This is the custom string column)  : triggerOutputs()?['body/_dev1_accountstatus_value']

Industry Code Value (This is the custom number column) : triggerOutputs()?['body/industrycode]

3. After you save your flow, when you update your accounts you should find that the two custom materialized columns will contain the primitive version of the Choice and Lookup columns. This now allows us to perform grouping/aggregate queries inside a flow.

4. Inside your app, add the following Power Fx to a screen OnVisible:

Concurrent(
// Get accounts by industry code
UpdateContext({ctxCountByIndustry:
    ForAll(
        Table(
            ParseJSON('PowerApp:FetchXmlQuery'.Run("
                <fetch aggregate='true'>
                    <entity name='account'>
                        <attribute name='dev1_industrycodevalue' alias='group' groupby='true' />
                        <attribute name='dev1_industrycodevalue' alias='industrycode_count' aggregate='count' />
                    </entity>
                </fetch>",
                "accounts").results
                )
            ),
            {
                group:Value(Value.group),
                industrycode_count:Value(Value.industrycode_count)
            }
        )
    })
,
// Get the industry code name/value pairs for the current language
UpdateContext({ctxIndustryCodes:
    ForAll(
        Table(
            ParseJSON('PowerApp:FetchXmlQuery'.Run("
                <fetch>
                <entity name='stringmap'>
                        <attribute name='stringmapid' />
                        <attribute name='attributevalue' />
                        <attribute name='displayorder' />
                        <attribute name='value' />
                        <filter>
                        <condition attribute='attributename' operator='eq' value='industrycode' />
                        <condition attribute='objecttypecode' operator='eq' value='1' />
                        <condition attribute='langid' operator='eq' value='1033' />
                        </filter>
                    </entity>
                </fetch>",
                "stringmaps").results
                )
            ),
            {
                attributevalue:Value(Value.attributevalue),
                value:Text(Value.value),
                displayorder:Value(Value.displayorder)
            }
        )
    })
);

There are two queries here, the first is the aggregate query returning the accounts grouped by industry code (the custom materialized column). The second is a query that returns the name/value pairs for the industry code choice column in the current language since we can no longer use the standard enum (Industry (Accounts)) that Power Fx provides us. The Power Fx enums are text-based only and you cannot get access to the Choice numeric value.

Notice that the two queries are performed inside a Concurrent function to ensure that they are run in parallel so that they can run as quickly as possible.

5. We can now show the results inside a Chart by binding the Chart.Items to :

AddColumns(Filter(ctxCountByIndustry,!IsBlank(group)),"IndustryName",LookUp(ctxIndustryCodes,attributevalue = group).value)

Note: The IndustryName column is added so that the chart can show the Choice Text value instead of the numeric value that we used to group by. The result might look something like this:

So that's it. I hope that eventually, these kinds of queries will be possible natively using Power Fx without the need to call a Cloud Flow. Maybe even, allow querying Dataverse using the SQL endpoint.

Hope this help!

@ScottDurow