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.

It is a good idea to warn the users to add those query option parameters and improve the performance of the flow. But at the moment, the same warning is shown as false positive if we are using the Fetch Xml Query in the List records action even if an attribute, filter or top count is specified in the FetchXML. It is just a warning and the user can proceed to save the flow. But after saving the flow, the Flow Checker opens automatically with the warning and it can be a bit annoying. Hopefully, Microsoft will fix this Flow Checker issue soon and until then, we can apply the following workaround to make the Flow Checker happy and not to complain about the lack of recommended query parameters.

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?

Select Query - Combine

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.

Filter Query - Combine with AND

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".

Order By - Overwrite

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.

Expand Query - Combine with _LinkEntityAliasPrefix

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.

Only many-to-one relationship are supported while passing $expand with saved query,user query or fetchXml.

Top Count - Overwrite

If we specify the Top Count together with the Fetch Xml Query in the List records action, the number in the Top Count parameter takes precedence over the top parameter in the <fetch> tag of the FetchXML. In the following example, Top Count parameter is specified as 2 and top parameter in the FetchXML is 1. The output returns x2 Contact records as specified in the Top Count parameter even though it is supposed to return only one record as per the FetchXML.

If we use a combination of both FetchXML and other query option parameters, does it count as one API or does the platform aggregate the results from two separate FetchXML and Web API call?

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>

In what scenarios can we leverage and populate FetchXML Query as well as the other query option parameters?

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.

If you come up with an idea about using a combination of both FetchXML and other query option parameters, please drop in the comment section below.

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.