In Dataverse you can create relationships between tables, but how do you manage the relationships from a flow in Power Automate?
Relationships in Dataverse
Table of Contents
In Dataverse there are a number of different type of relationships that you can create.
- Many-to-one/Lookup
- One-to-Many
- Many-to-Many
Relationship examples
Consider the following tables:
- Address
- Car
- Company
- Person
Many-to-one relationships
If we created a relationship between the car table and the Person table called Main Driver. Then we would create a Many-to-one relationship (car being the primary table) or a Lookup. Lookup and Many-to-one relationships are the same thing.
Many-to-many relationships
If we created a relationship between car and the Person table called Drivers ( a car can have more than one drive but only one main driver), then we would select a Many-to-many relationship
One-to-Many relationships
One-to-many relationships are the same as Many-to-one relationships but with the primary and secondary tables reversed.
Relating records in Power Automate
To start this example I will create a flow that creates a record to my Car table and a Person record to my People table.
I can now use the relate rows action to link the car with the person through the Many-to-many relationship. That I use to register all the drivers of the car.
The Maindriver relationship however is not listed here.
If I however switch the update around and
Now I can select the Main driver relationship without any issue.
So the Relate with record needs to be referring to the table at the Many end of the One-to-Many or Many-to-one relationship.
As we connected Person 1 with Car 1 in the above action our Main Driver for Car 1 will be Person 1.
Using the relate action we can update our Many-to-Many relationship just the same way as we updated the Many-to-one relationship.
When I feed the above flow with Person 2 and Car 2, I will end up with a main driver and drivers set to Person 2.
The pains of relate rows
Hey, there is now SharePains.com post without a bit of a pain point. So far all worked well, but …
Recently I came across a bit of an issue with the relate rows action. My action was configured correctly.
As my flow was running there were no failures and the action reported its green tick telling me that all had worked as expected. But …
The relationship wasn’t updated. I’ve seen this once before. Using the Update a row action and specifying the relationship in the classic way, did work.
