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.
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.
On a previous project, we came across some limitations with FetchXML queries and the other query options. What we found was the following:ReplyDelete
- FetchXML is limited to 5000 records at a time. The other query did not have this limitation. I wonder if the combination would allow more than 5000 records?
- With the other query it is limited to 10 different filters across the Select, Filter and Expand. We found you could only have 10 filters across all of these but with FetchXML we didnt have the same issue. Although it might be because it was easier to build the query with FetchXML. Unfortunately, I can't find the specific Microsoft article that specifies the issue, but if I remember correctly it is something to do with this: https://social.microsoft.com/Forums/en-US/b3978065-7215-4643-a9e8-e9b388f44814/limit-on-quantity-of-ltlinkentitygt-in-fetch-xml-is-10?forum=crm
- The combination would NOT allow more than 5000 records. The output of the List records with Fetch Xml query will be maxed at 5000 no matter what other query option parameter is specified.Delete
- I guess the problem with the Web API is that each filter condition of the column value in the related table is counted as x1 link entity. (e.g. if the following query is considered as x2 link entities). That's why you would hit the x10 link entities limit pretty quickly with the Web API since it is ($filter condition in link entity + $expand link entity combined)
[Organization URI]/api/data/v9.1/contacts?$select=contactid&$expand=parentcustomerid_account($select=accountid)&$filter=parentcustomerid_account/name eq 'Adventure+Works+(sample)'
In FetchXML query, the tags within the are not counted towards the x10 link entities limit. Here is the Microsoft Docs which mentions about the limitation of x10 link entities limit in FetchXML query.