Microsoft Power Automate: How to use Fetch XML Inside of a Flow to Query CDS Entities and Get Data from Related Entities

Problem:

You’ve set up a Flow using Microsoft Power Automate, and you have a step that lists records from an entity. Later in the Flow you want to reference a specific attribute from that listed entity, but when using the built-in Dynamic content selector in the Flow UI, you are unable to find the specific attribute that you need, even though it is a valid attribute in the entity schema. This sometimes happens when you try to reference a lookup attribute from the entity, for example.

Solution:

Use can use a Fetch XML query inside of a Common Data Service “List records” step to get the entity attributes you need available for processing in your Flow. You can also use the same query to get attributes from other entities linked to the main entity.

Pros to this approach:

1. You are able to pinpoint the exact attributes you want to return in your list
2. You are able to get attributes from entities related to the main entity that is being queried
3. You can filter and sort your results directly in the Fetch XML query
4. You don’t have to be limited by the Dynamic content selector UI

Cons to this approach:

1. Learning curve – You have to learn the Fetch XML syntax
2. You have to specify all of the exact attributes in the query, which can be tedious
3. You have to write expressions to grab attributes returned by the Fetch XML, rather than using the Flow UI’s Dynamic content selector

Steps:

1. Add a Premium Common Data Service connector “List Records” in Microsoft Power Automate, which will function as your query step.

2. Select the desired entity that you plan on querying. Make sure the connector is expanded and click “Show advanced options”, which will reveal the Fetch XML Query field.

3. Enter a Fetch XML query into this field. An example query is:

This query will attempt to get the first name, last name, and email address of the top 50 contacts who have a last name that begins with the letter “D”. It will also get the fax number of the Contact’s parent company (a.k.a., Account), assuming the Contact has both a parent company and that parent company has a fax.

Headache-saving tip #1 for using link-entity: The “from” should be the primary ID of the related entity (in this case, the accountid of the Account entity). The “to” should be the lookup field that exists on the main entity (in this case, parentcustomerid of the Contact entity).

Headache-saving tip #2 for using link-entity: ALWAYS be sure to indicate a unique and simple alias for each instance of link-entity (in this case, I have used “company” as the alias since I am getting an attribute from the Contact’s parent company). A unique and simple alias will make it much easier to reference the related entity’s attribute(s) later on in the Flow when you start writing expressions to get the data.

4. Process each record returned by the Fetch XML query individually by using an Apply to each step. Make sure to use value from the Dynamic content as the input for this step:

5. Add Compose steps so that you can add Expressions to easily get individual attributes from the Fetch XML results. The input for Compose steps should use one of the following syntax:
   1. items(‘<name of the apply-to-each step’>)?[‘<name of the attribute>’] à use this if you are getting an attribute directly from the main entity being queried, e.g., items(‘Apply_to_each’)?[‘firstname’]
   2. items(‘<name of the apply-to-each step’>)?[‘<linked entity alias>.<name of the linked entity attribute>’] à use this if you are getting an attribute from one of the link-entities from the query, e.g., items(‘Apply_to_each’)?[‘company.fax’]

Note: Always make sure that you use an underscore character (_) in place of spaces in the expression!

Another Note: I also highly recommend renaming the Compose step so that you can easily tell what it’s for – see an example of this later in this blog.

6. You can even combine two or more expressions inside of one Compose step to do things such as constructing the Full Name of a Contact:

7. This final Compose step shows how you could then see the results of your Fetch XML query and the previous Compose steps in an easy to read format. Here I have renamed the previous three Compose steps (Fax, Email, and Fullname) to make it incredibly simple to use the Dynamic content selector to populate this Compose step’s input. The results will list out the Contact’s name, email, and company fax:

After saving the Flow and running a test, the final Compose step reveals the results of an individual Contact returned by the Fetch XML query:

Rather than populating only Compose steps, you can also use the results of the Fetch XML query to perform further queries, populate emails, and many other tasks that I have not even thought of yet!

To quickly create and fine-tune your Fetch XML query, I highly recommend using the FetchXML Builder plugin in the XrmToolBox tool. This plugin lets you use a GUI interface to build the query quickly and easily. Here is the FetchXML Builder plugin in the XrmToolBox:

For more examples and techniques around how to use Fetch XML, refer to Microsoft’s documentation.

Need help with filling in gaps around any of your business systems? The Microsoft Power Platform can make it happen. Talk to our development team today about your challenges and goals.