How to Avoid the “Get Record” Action to Retrieve Related Data in Power Automate

In a previous blog post I wrote about how to use FetchXML to List Records from CDS in Power Automate. But what if a column you defined in your FetchXML doesn’t show up in the Dynamic content because it is a column from a related record?

Defining the FetchXML

Let’s start with using the most simple example. We want to list Contacts and use the Industry from their Company (Account) later in the Flow. I create an advanced find, choose the Industry field from Account and this is what my search looks like.

I use the export as FetchXML option in Advanced Find and get this result:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="parentcustomerid" />
    <attribute name="contactid" />
    <attribute name="emailaddress1" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="account" from="accountid" to="parentcustomerid" visible="false" link-type="outer" alias="a_dc9b80f8c78146d89fd6a3b610836975">
      <attribute name="industrycode" />
    </link-entity>
  </entity>
</fetch>

The Dynamic Data Differs from the Output

Even though we did define what columns from the records we wanted to use, the Dynamic content shows all existing fields from the Contact entity, and only the Contact entity. If you were to use one of the not defined in your FetchXML you would get NULL in your result. Be aware of that when you use the FetchXML Query.

But this also means that we can not find our Industry in the Dynamic Content because it’s from the related entity Account. So what do we do?

The Easy Way Out – Get Record Action

The easiest way out of getting data from a related record is of course using the Get Record action. But this also means it will make an API call to find the related Account for each of our Contacts we receive. This way I access the Industry but also all other fields of my related Account entity. But then what is the point of me defining it in the FetchXML?

The better Solution – Parse JSON

So again – to avoid unnecessary API calls: We can instead use the Parse JSON action to actually take advantage of the fact that we defined this column as part of our FetchXML in the List Records action.

Run your Flow with the List Record action and copy your output.

Add the Parse JSON action and choose the Generate from sample option

Now – all of a sudden we have all the Dynamic content from our List Records action and can use the Industry information from Account later in our Flow.

Want to know more about Parse JSON?

The Dynamic content doesn’t look too good though so I suggest you read John Liu’s amazing blog post on how to use the Parse JSON action in a smarter and better way here.

Summary

Make sure you really take advantage of using a FetchXML Query when listing records to include as much related data as possible. By using Parse JSON instead of Get Record you avoid unnecessary API-calls that also slows down your Flow.

That’s the way the cookie crumbles.

Original Post https://saralagerquist.com/2019/11/25/how-to-avoid-the-get-record-action-to-retrieve-related-data-in-power-automate/