Mix and Match FetchXML Query with Web API Query Options of Microsoft Dataverse Connector in Power Automate

Recently, Microsoft has added validation in the Flow Checker to show the following warning message to the users if any of the Select Query, Filter Query or Top Count is not specified in the List records action of the Common Data Service (current environment) connector in Power Automate. 

Update action 'List Records with Fetch Xml Query' to use a select query, filter query, or top parameter. Use these fields to only return the records and fields you need and get better performance from your Flow.

The easiest way is to add one dummy field name in the Select Query. The field name can be the primary GUID field of the entity (<<EntityName>> + id) field (e.g. contactid) or one of the fields in the FetchXML attribute list (e.g. lastname in the following example) so that it would not query additional fields than it is supposed to.

But what if we specify the fields in Select Query which are different from the FetchXML? What if we specify other query parameters which are contradicting the FetchXML?

If we specify the Select Query together with the Fetch Xml Query, the List records action will output the fields specified in Select Query as well as the fields in the FetchXML. In the following example, firstname field is specified in the Select Query and the lastname field is in the FetchXML. The output returns with values for both firstname and lastname fields.

If we specify the Filter Query together with the Fetch Xml Query, the List records action will only output the records which match with the condition specified in Filter Query as well as the condition in the FetchXML. In the following example, "the last name contains o" is specified in the Filter Query and "the first name starts with S" condition in the FetchXML. The output returns with Contact records which have the first name starting with "S" and the last name containing "o".

If we specify the Order By together with the Fetch Xml Query in the List records action, the fields in the Order By parameter takes precedence over the <order> tag in the FetchXML. In the following example, the lastname field is specified in the Order By parameter and firstname in the FetchXML. The output returns the Contact records in the order of the last name as specified in the Order By parameter.

If we specify the Expand Query together with the Fetch Xml Query for N:1 relationship, the List records action will output the values from the related entity specified in Expand Query as well as the <link-entity> tag of the FetchXML. In the following example, "the last name of the modified by user" is specified in the Expand Query and "the first name of the created by user" in the <link-entity> of the FetchXML. The output returns with the related entity field values as specified in the <link-entity> of the FetchXML as well as the ones as specified in the Expand Query. The only difference from the normal List records action with Expand Query is that the output for the related entity field values are formatted as additional properties with the _LinkEntityAliasPrefix + expand field name in the FetchXML output format instead of the JSON object parameter for the related entity.

If we specify the Expand Query together with the Fetch Xml Query for 1:N relationship (e.g. Account and child Contacts), the flow will fail with the following error.

It is equivalent to the fetchXml query string parameter being used in combination with other $select, $filter, $orderby, $expand and $top query options in Web API Query.

e.g. 

The List records action with the Fetch Xml Query and other query parameters as in the screenshot above is equivalent to the Web API Query below. The output result is consistent across both List record action of CDS (current environment) connector and Web API Query.

GET https://[Organization URI]/api/data/v9.1/contacts?$select=lastname&$filter=contains(lastname, '%o%')&$orderby=lastname desc&$expand=modifiedby($select=lastname)&$top=5&fetchXml=<fetch top='1' >
  <entity name='contact' >
    <attribute name='firstname' />
    <filter>
      <condition attribute='firstname' operator='like' value='S%' />
    </filter>
    <order attribute='firstname' />
    <link-entity name='systemuser' from='systemuserid' to='createdby' alias='createdbyuser' >
      <attribute name='firstname' />
    </link-entity>
  </entity>
</fetch>

So far, I will have not encountered any valid scenario where I need to use both FetchXML and other query option parameters. I use query option parameters for most of my List records actions and sometimes, I use FetchXML query for complex queries (but not both).

The only scenario I can think of is retrieving the FetchXML stored somewhere in the system (e.g. List Rollup Queries, Views or Saved Views) and add more query parameters to inject additional filter parameters and fields. In that way, the users can update the Rollup Queries and run certain actions dynamically without the system customiser updating the flow.

In summary, we can use FetchXML Query together with the other query options in the List records action of the Common Data Service (current environment) connector (except one-to-many relationship in Expand Query). Based on the parameter, it takes precedence over the parameter in the FetchXML or combines with the parameter in the FetchXML.