Power Automate: Missing Fields in Dynamic Content from Related Tables

In Power Automate, have you encountered missing fields in the Dynamic Content if the field you want to reference is in a related table in Dataverse? I have experienced this a few times, and the solution was not that obvious.

In this article, I’ll give an example of a situation where this problem arose for me, and then I’ll explain how I solved it.

Example of Missing Field Problem

You may encounter this problem when joining multiple Dataverse tables. In my case, I had to join a custom Job table (parent) to the Contact table (child) in order to get and update an account record(grandchild)/stub account’s address fields.

Job > Contact > Account

I find the tools in the XRMToolbox like FetchXMLBuilder and SQL4CDS very handy! Generating your FetchXML via CE’s Advanced Find is also an option.

But being a developer with SQL knowledge, it is common for me to write SQL like this in SQL4CDS:

NOTE: You can also query the Dataverse tables via SSMS. See this post about How To Query CDS Data Using SSMS.

But the cloud flow I am creating will need the FetchXML Query to filter out the data I need, so good thing there is a FetchXML tab to convert the SQL into FetchXML (you can also EDIT in FXB):

In my cloud flow, I wanted to sync updates from a Job’s Loss Address record to the linked Contact/Policy Holder’s BC Account.

My Power Automate steps look like this:

NOTE: I have substituted the GUID of the the Loss Address that got updated via Dynamic Content into the FetchXML query.

The problem:

After adding the Update Row step below, I couldn’t find the BC Account field in the Dynamic content that I need to put into the Row ID field of the Update a Row step. This is because it is not a field in the Jobs table, which is the main table from the previous step. It only shows the fields from the Jobs table.

The field that I need (enc_bcaccount) is on the related Contact table, not the main Job table.

Running the flow and looking at the raw outputs of the List Rows step, I was able to find the attribute I needed from the JSON body below:

Solving the Missing Field Problem for a Related Table

So I thought, if I can add a temporary Compose step just to see and peek code of a different field (enc_jobnumber) that was added via the Dynamic Content like below, then that should also be the way to write/reference the field that I want. I just have to add it manually as an Expression:

@items('Apply_to_each_stub_account')?['enc_jobnumber']

And so, that is what I did:

items('Apply_to_each_stub_account')?['contact.enc_bcaccount']

I was now able to get the value of the related table’s lookup value (something that is not available in the Dynamic Content) and update the account’s address fields from the Loss Address.

I could have written this in a plugin, but with Power Automate it was way simpler!

Please contact us if you have any questions about this article or about the Power Platform.