Microsoft Dataverse List Rows - Use Expand Query to Retrieve Related Data in Cloud Flow

Last week, Sara Lagerquist showed us How to Avoid the “Get Record” Action to Retrieve Related Data in Power Automate using the FetchXML in her blog post. Her post reminded me of another way that we can retrieve related data using the Expand query parameter of List records CDS action as I mentioned in one of the Power Automate Ideas.

In this blog post, I will describe how you can use the Expand query parameter to retrieve related data by using the steps below. The basic idea is to use List records action of CDS connector with Expand Query, Parse the output JSON and use the value from Expand Query. (which is not available in the list of Dynamics content)

1. Add List records action from Common Data Service (current environment) connector
2. Get Expand Query parameter for List records using FetchXML Builder 
3. Add Expand Query from FetchXML Builder to the List records action
4. Get sample data for Parse JSON action
5. Generate schema in Parse JSON action using sample JSON value
6. Extract the sample value JSON from the List records action OUTPUT
7. Insert a sample JSON Payload
8. Use the output (related entity data) of Parse JSON action

I will use the same simple example as Sara's post in which we want to list Contacts and use the Industry from their Company (Account) with an additional step to update back to the Description field of the Contact. (just to visualise the data)

1. Add List records action from Common Data Service (current environment) connector

First of all, you will need to add a "List records" action for Contact with the Expand Query to retrieve the Industry (industrycode) of the Company Name (parentcustomerid)

2. Get Expand Query parameter for List records using FetchXML Builder

If you're not familiar with Expand Query of the CDS Web API, use the FetchXML Builder tool (FXB) from XrmToolBox by Jonas Rapp. (don't be fooled by its name. It can be used for OData and WebAPI queries as well). For more details on how to use the tool to get Power Automate Parameters, read step by step guide in this blog post by Sara.

As in the screenshot below, build the FetchXML query and click on View > Power Automate Parameters which will show the pop-up with the values to fill up in the "List records" action of the flow.

3. Add Expand Query from FetchXML Builder to the List records action

Copy the Expand Query from the FXB to the Expand Query of the List Contact records action.

4. Get sample data for Parse JSON action

Before we Parse JSON for the output of the List records action, let's Test run to get the sample data in order to generate the schema. When the Test flow runs successfully, click on the "Click to download" link of the List records action OUTPUTS to see the output JSON.

5. Generate schema in Parse JSON action using sample JSON value

If you want to know more details about parsing JSON, you can read it here but you can also follow the next step below just to achieve this.
Within "Apply to each" control, add a Parse JSON action and click on the Generate from sample button

6. Extract the sample value JSON from the List records action OUTPUT

Format the sample JSON output from the previous step using any of your faviourite tool for better readability. In this case, I use Format JSON command from JSON Viewer Notepad++ plugin.
From the formatted JSON, select one record block within "body" > "value" as shown in the screenshot below.

7. Insert a sample JSON Payload

Insert the sample JSON payload from the step above into the pop-up of the Parse JSON action and click Done.

8. Use the output (related entity data) of Parse JSON action

Once the Parse JSON step is done properly, the industrycode from the Expand Query can be selected from the Dynamic content list as shown below.

Summary

By using the Expand Query to Retrieve Related Data, not only it simplifies your flow but also it improves the performace by avoiding unnecessary API-calls with Get record action to retrieve the related entity data.

You can download the above sample flow from my GitHub repository via this link.