Get Output Data from Expand Query of Microsoft Dataverse Connector in Power Automate

The Common Data Service (current environment) (which is highly likely to be renamed soon) Dataflex (Common Data Service current) Common Data Service (current environment) Microsoft Dataverse connector has Expand Query parameter for Get a record and List records actions which can be used to retrieve the related entity. In one of my previous blog posts, I wrote about how you can use Expand Query to retrieve related data. The example I used in that blog post was querying Contact records together with the Industry field from the related Account entity. But this time, I will show you how to get the related entity data from the various types of relationships below without using Parse JSON.

• parent entity (N:1)
• multi-level relationship (e.g. parent entity of parent entity)
• child entity (1:N)
• many to many related entity (N:N)
• related entity using FetchXML
• filter based on related entity

Parent entity (N:1)

In my previous blog post that I mentioned earlier, I explained the use of Expand Query from the List records action and use the output (related entity data) using Parse JSON action. The reason why Parse JSON action was used in that scenario was because the Company Name (parentcustomerid) field is a polymorphic (Customer) lookup field. Since the Power Automate designer is not able to show the fields from the related entity of the polymorphic lookup field in the Dynamic Value pane, we need to use the Parse JSON action to make those fields show up in the Dynamic Value list and select the field/s from the list. But if you are good with JSON and if you also like to live dangerously 🤓, you can just write the flow expression without relying on the Parse JSON action.

In the following List records action, the value of the Expand Query is "parentcustomerid_account($select=industrycode)" to retrieve the Industry of the Contact’s Company.

Below are examples of how to get the industrycode of the related Account in the Apply to each loop from the List Contact records action:

items('Apply_to_each')?['parentcustomerid_account']?['industrycode']

or

items('Apply_to_each')?['parentcustomerid_account/industrycode']

⚠ Important 

If you are going with the first approach, make sure a question mark ? is added before each object value to return the NULL value instead of an error if there is no relationship (no value in the lookup).

In the following Get record action, the value of the Expand Query is "parentcustomerid_account($select=industrycode)".

Below is how you can get the industrycode of related Account from the Get a Contact record action:

outputs('Get_a_Contact_record')?['body/parentcustomerid_account/industrycode']

You might be wondering how can you build such kind of expression for your flow with a different name for the step and different entity/attributes.

1. First of all, add one of the dynamic values from the action that you need the related entity field (e.g. Full Name from the Get a Contact record action in my scenario).
2. After that, click on the ellipsis (...) of the step and then click Peek code.
3. Copy the JSON value of inputs into your favourite text editor, remove the @ symbol and curly brackets { } 
4. Replace the field value from step 1 with the Expand Query lookup name. You do not have to paste the expression into the flow in this step yet.
5. Add the required field from the related entity after the lookup name and add it as an expression in the flow step.

💡 Tip

Instead of step 2 and 3, a cursor can be placed in the text box of the field, press Ctrl + A (select all) and Ctrl + C (copy) to copy the dynamic value into the clipboard too.

If the lookup is not polymorphic, things are a lot easier and you can select the fields from the related entity from the Dynamic Value pane. You can read more about it in this blog post by Aaron Back. In the following example, let's query Account records together with the Last Name of the Primary Contact of Account.

In a normal lookup scenario, you will be able to see the fields from the related entity in the Dynamic Value pane with the following format. <<Lookup Field Name>> + <<Field name in related entity>>

It is shown as Primary Contact Last Name in this scenario.

In the following List Records action, the value of the Expand Query is "primarycontactid($select=lastname,statecode,statuscode)" to retrieve the values from the Primary Contact of the Account.

Multi-level relationship

If you need to get the data from the parent entity of the parent entity, you can use the nested expansion in the Expand Query. In the following example, let's query Account records together with the Last Name of the Primary Contact of Account and the First Name field of the Created By user of the Contact entity.

In this scenario, you will be able to see the fields from the related entity in the Dynamic Value pane but not the fields from the 2nd level related entity (e.g. First Name of the User who created the Primary Contact). Now, we can re-use the same approach that we used for the polymorphic lookup field.

💡 Tip

Use the latest version of FetchXML Builder for XrmToolBox to generate the Expand Query parameter for getting fields from the related entity.

In the following List Records action, the value of the Expand Query is "primarycontactid($select=lastname;$expand=createdby($select=firstname))" to retrieve the Primary Contact entity and its Created by user.

You can get the values of the related entity using the following expression:

items('Apply_to_each')?['primarycontactid/createdby/firstname']

and the complete Inputs of the above Compose action is:

Account Name: @{items('Apply_to_each')?['name']}
Primary Contact Last Name: @{items('Apply_to_each')?['primarycontactid/lastname']}
Primary Contact Created By User First Name: @{items('Apply_to_each')?['primarycontactid/createdby/firstname']}

Now, you know the drill and you can get the value from the related entities up to 11-level parent record (because 11 nested expand query is the max the platform can handle) by following the same pattern.

An example scenario of a multi-level relationship could be getting the Currency Symbol of the Currency of the Parent Business Unit of the Business Unit of the Manager of the User who created the Primary Contact of the Account 🥵

items('Apply_to_each')?['primarycontactid/createdby/parentsystemuserid/businessunitid/parentbusinessunitid/transactioncurrencyid/currencysymbol']




Child entity (1:N)

You can also get the data from the child entities in a single query using the Expand Query. In the following example, let's query Account records together with the Full Name field of all related child Contact entities.

In this scenario, you will need to have nested Apply to each control. The first one is to loop through the Accounts from the List records action and second one is to loop through the child Contact entity of each Account entity. The purpose of the flow below is to get the list of Contact Full Name for each account.

In the following List Records action, the value of the Expand Query is "contact_customer_accounts($select=fullname)" to retrieve the related child Contact records.

When we add child 1:N relationship in the Expand Query, the output JSON returns the array value with the relationship name and you can use refer to the following expression to populate the input for the nested Apply to each control. If you are not sure how to write such expression, you can simply add the Dynamic value from the List records action and replace the field name with the relationship name to the child entity.

items('Apply_to_each_Account')?['contact_customer_accounts']

Within the nested Apply to each control, you cannot see the values in the Dynamic Value pane. But I hope you can apply what you have learned so far and write your own expression. Choose the Current item of the nested Apply to each control and append  ?['fieldname']

Here is  an example:

items('Apply_to_each_child_Contact')?['fullname']

This is the output of the flow above:

 

Many to many related entity (N:N)

Getting the value from the N:N related entity is pretty much similar to getting a value from 1:N related child entity. In the sample flow below, List Records action is used to retrieve the Users with associated Teams (pretty much similar to the previous example).

In the following List Records action, the value of the Expand Query is "teammembership_association($select=name)" to retrieve the N:N related team records.

Below is the expression used for the nested Apply to each control:

items('Apply_to_each_User')?['teammembership_association']

And this is the expression for getting the team name value in the nested Apply to each control:

items('Apply_to_each_associated_Team')?['name']

This is the output of the flow above:

Related entity using FetchXML

Getting data from the related entity using Fetch Xml Query in List records action is a bit different from Expand Query. The related entity fields are not available in the Dynamic Value pane even for the direct parent entity fields. If you like to choose the related entity fields from the list, you can check out the blog post by Sara Lagerquist on how can you use Parse JSON action with FetchXML in List records action. Or if you are keen to learn how to use the related entity fields from the FetchXML List records action output JSON in the expression, check out the sample query below.

The following scenario is the combination of all examples above to query Account records together with the Last Name of the Primary Contact of Account, the First Name field of the Created By user and related Teams of the Contact entity and the full name of child Contact entities.

This is the FetchXML used to query the data for the scenario above:

<fetch>
  <entity name="account" >
    <attribute name="name" />
    <link-entity name="contact" from="contactid" to="primarycontactid" alias="primarycontact" >
      <attribute name="lastname" />
      <link-entity name="systemuser" from="systemuserid" to="createdby" alias="createdbyuser" >
        <attribute name="firstname" />
        <link-entity name="teammembership" from="systemuserid" to="systemuserid">
          <link-entity name="team" from="teamid" to="teamid" alias="team">
            <attribute name="name" />
          </link-entity>
        </link-entity>
      </link-entity>
    </link-entity>
    <link-entity name="contact" from="parentcustomerid" to="accountid" alias="childcontact" >
      <attribute name="fullname" />
    </link-entity>
  </entity>
</fetch>

🛈 Note 

It is important to set the alias of the link-entity in the FetchXML, because the system will automatically set the alias of the related entity with number postfix (contact1, contact2, etc.) if you don’t specify your own alias. Setting the alias will be more meaningful than just a number postfix and you do not have to figure out what will be the system auto-generated alias.

Unlike the output JSON of the List records action with Expand Query which has nested JSON array for 1:N or N:N related entities, the output JSON of the List records action with FetchXML has only 1 level JSON array of records with repeating values for the parent record. Imagine the data table like the following is responded JSON array with repeated Account, Primary Contact, Created By User values for each Team and Child Contact of the Account. It is not really ideal if you want to capture the values from the parent entity and the child entity separately.

Just like the expressions in the previous examples, select one of the Dynamic Values from the List records action and replace the field name with the related entity <<Alias>>.<<FieldName>> (e.g. primarycontact.lastname)

The following expression was used to get all the fields of different entities specified in the FetchXML:

Account Name: @{items('Apply_to_each_Account_in_FetchXML')?['name']}
Primary Contact Last Name: @{items('Apply_to_each_Account_in_FetchXML')?['primarycontact.lastname']}
Created By User First Name: @{items('Apply_to_each_Account_in_FetchXML')?['createdbyuser.firstname']}
Created By User Team Name: @{items('Apply_to_each_Account_in_FetchXML')?['team.name']}
Child Contact Full Name: @{items('Apply_to_each_Account_in_FetchXML')?['childcontact.fullname']}

This is the output of the flow above:

Filter Based on Related Entity

Just like getting the column value from the related parent entity, you can also filter the data based on the column value of the related parent entity (for one level up).

In the following List Rows action, the first condition value of the Filter Rows is "createdby/isdisabled eq true" which is to filter based on the isdisabled value of the related User related by createdby lookup column.

The second condition value of the Filter Rows "parentcustomerid_account/industrycode eq 1" is to filter based on the industrycode value of the related Account and this one is a bit more complex because the parentcustomerid lookup column is polymorphic lookup. In this case, it is postfix with _ + logical name of the related table _account. It is best to use the FetchXML Builder tool to generate the parameters for List rows action.

Summary

Even though the Power Automate designer cannot show all available values from the output of the Get a record and List records actions with Expand Query parameter, we can use those output by adding an expression based on the output JSON format.