[Power Automate] List Records - Use Expand Query to Retrieve Related Data in 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.

Comments

Popular Posts

[Power Automate] How to Set Lookup Field Value in Common Data Service (current environment) Flow Connector

[Power Apps] Using Common Data Service's Lookup Data Type Field in Canvas App

Get the Lookup Display Name and Option Set Value Label in a Single Query Using a CDS (Current Environment) Connector FormattedValue Property

Find out how to include a link to the record (Record URL) when sending an email from Dynamics 365/CDS using flow

Pass Parameters to Quick Create Form in Dynamics 365 using formContext.data.attributes

How to Set Lookup Fields with Null Value from Dynamic Content in CDS (current environment) Connector

Send Email from Dynamics 365/CDS with Attachment from Notes Using Flow

Getting the Lookup Attribute of the Parent Record in Power Apps Canvas App Development with Common Data Service (CDS)

Access Flow Run History within a Record in Dynamics 365/CDS