How to query related entity data in CDS using FetchXml in Power Automate

• How do I use FetchXml in Power Automate?
• How do I access related entity fields when using Fetchxml in PowerAutomate?

If you are having any of the above questions, you are at the right place. Strictly speaking, this blog is more about how to access related entity information in PowerAutomate through FetchXml. But as I am going to explain from the beginning, if you are not aware of the basics as well, this blog might help you with that too.

To demo this scenario, I am using the below fetchxml.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="parentcustomerid" />
    <attribute name="emailaddress1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="parentcustomerid" operator="eq" value="{B56B3F4B-1BE7-E611-8101-E0071B6AF231}" />
    </filter>
    <link-entity name="account" from="accountid" to="parentcustomerid" visible="false" link-type="outer" alias="pa">
      <attribute name="address1_composite" />
 <!-- string field -->
      <attribute name="industrycode" />
 <!-- optionset field -->
    </link-entity>
  </entity>
</fetch>

Quite evident from the above fetchxml, I am retrieving all the contacts associated with an account. For the account record, I am retrieving the value of “Address 1” field which is of type text and “Industry” field which is of type optionset. Also note the alias – “pa” I am using while I join with the account entity. And below is my Power Automate flow.

Quite a simple flow nonetheless. Below are the steps for the flow.

• Declare couple of variables to store the Account Address and Industry
• Run the fetchxml
• Iterate through the items returned by FetchXml and set the value of Account address and industry.

You might be thinking, it’s quite a mundane one. And to be honest it is. But what does baffle infact a lot is your related entity data (in this case for account entity) does not show up in the Dynamic content pane of Power Automate flow.

Then how do I get it. It’s actually quite easy. Just select any placeholder value from Dynamic content pane and then from the side menu (ellipsis), choose “Peek Code“.

Copy the expression that you see in the value

Now to get the Industry code all we need to do is use the appropriate field names in the expression. Below are the expressions for Industry code and Address 1 fields.

Industry - items('Apply_to_each')?['pa.industrycode']
Address 1 - items('Apply_to_each')?['pa.address1_composite']

As you can see, I now have the correct values. Or is it? There is a small glitch though. The Account Industry is showing as 5 instead of name. That is because it is Optionset and if you put the field name in the expression, it will always give you the optionset value. If you want the Optionset text you need to use the below expression.

items('Apply_to_each')?['pa.industrycode@OData.Community.Display.V1.FormattedValue']

Very much similar to the format we use to retrieve optionset text using WebApi.

Hope this helps!

You may also like the below posts

Debajit Dutta
(Business Solutions MVP)