Ohh – what a challenge before I found a very simple solution for a common problem. I was searching, how I can access my related records from a many-to-many relationship in my Power Automate flow. I was googling and found extremely difficult stuff. Someone have resolved the records by using “FetchXml”, calling APIs, or some Xrm “black magic” with Plugins.

No – this is definitely to complicated. Finally I was wondering: Isn’t there a simple way to access my records of a many-to-many relationship between Dataverse tables from Power Automate?

To shorten this: Yes there is a simple solution. Just 3 steps! But let me explain a little bit more what I tried to do.

My problem

My dataverse solution contains three tables. My table Scheduling contains scheduling information for the execution of my assigned rules within assigned containers. In detail, many scheduling records can be related to many rules or containers. In conclusion, this is a many-to-many relation between my tables:

Moreover, I want to iterate over all related rules and containers for a single Scheduling record in a Power Automate Flow. And yes, I had no idea how to do this… That’s why I started searching the web.

My Dataverse solution

Initially, I have created my three tables in Dataverse in my solution. I do this, because I love Application Lifecycle Management and use solutions to transport all components easily between my Dataverse environments (DEV – TEST – PROD).

Here in my example, my tables contains only the default columns. These are the record identifier and the record name columns.

Setup table relationships

Remember, I have 2 many-to-many relations in my example. One relationship is between Scheduling and Rule, the other between my tables Scheduling and Container.

I setup the many-to-many relations between my tables in my table designer. I navigate to my table Scheduling and afterwards to it’s Relationships:

In my next step, I create a new “Many-to-many” relationship between my tables:

In detail, I select my Rule table, define my Relationship name and Relationship table name as follows:

Afterwards I repeat the same procedure to setup my many-to-many relation between my tables Scheduling and Container.

Adding test data

Well, I can’t access my Relationship table in Dataverse. This means I need another solution.

For that reason, I modify my Main form in my table Scheduling. I add a Subgrid to a new 1 column section in my form. Furthermore, I activate “Show related records”. Afterwards, I can select my table Containers:

Additionally, I repeat this procedure to add a subgrid for my second related table Rules. The result is shown here:

In my last step, I add my test data for table Rules and Containers. Finally, I open the Scheduling main form form “Edit row using form” im my modern table editor:

Then, I can easy assign related records for my Scheduling table in my subgrids Containers and Rules by using “Add Existing Container” or “Add existing Rule”:

Well done, I have successful prepared my example. Let’s move on and create a Power Automate flow…

Power Automate

I create a simple flow like this – nothing special:

After running this flow, I have no information about my related tables in my result body – and yes this was my initial problem:

Here is the simple solution which I have found…

Expand Query

I must expand my query! In detail, I configure “Expand Query” with my collection names from my relationship. This means, I use “mme2k_Containers” for my related Containers.

As you can see, I get this information directly from my table Relationships:

In my next step, I expand my query in my Power Automate flow Dataverse action “Get Record by ID“. Furthermore, I add my 2nd collection, my related rules, by adding “mme2k_Rules”. The result is shown here:

Let’s test my flow… Success!

Now you can see, there is a collection in my result of the Get Record by ID action:

My second collection “mme2k_Rules” is also present.

Using related records

As last step, I initialize 2 variables (type Array) in my flow. I do this for my rules and for my containers:

I use as Expression the body of my action “Get record by id” (name: Scheduling) and access my expanded relation “mme2k_Rules”:

body('Scheduling')?['mme2k_Rules']

Finally, I use both variables from 2 Apply to Each actions. One Apply to Each action for my rules and one Apply to Each action for my containers:

My test result is a combination of all rules and containers. Now I can use this information to execute my scheduling procedure:

Summary

Under the bottom line, accessing my related Dataverse records from many-to-many relationship in Power Automate is simple! I don’t need any FetchXml or Xrm black magic with plugins.

First of all, I expanded my query in my Get record by Id action. I used “Expand Query” for this and added there the collection names from my relationships. Finally, I have stored the result collections in array variables to use the items in Apply to each actions.

You see, these were 3 simple steps and you can use related records in a Power Automate Flow … Yes, this was also my Never Stop Learning of the week!