Dataverse Many to Many Relationships
It can be a little tricky to work out how to navigate the many to many relationships between Dataverse tables
Let’s look at the many-to-many relationship between the Teams and User tables. A Team can have many users and a User can belong to many teams
The key to being able to traverse the relationship is to find the name of the many to many relationship between the 2 tables. This can be identified from either table
User Table Relationships
Team Table Relationships
Table Navigation
I want to be able to display all the Teams that I am a member of. To do that I create a canvas app and add the User and Team tables
Having created a gallery, to display all the Teams I am a member of, set the Items the property of the gallery:
LookUp(Users,'Full Name'="Chris Harper").teammembership_association
The formula works by getting my user record and then traversing the many-to-many relationship to display all my associated teams
Similarly, I can display all the members of a the Developers team in a gallery with this Items property:
LookUp(Teams,'Team Name'="Developers").teammembership_association
When typing the relationship name, Intellisense offers you the options below that work equally well:
LookUp(Users,'Full Name'="Chris Harper").'Teams (teammembership_association)'
LookUp(Teams,'Team Name'="Developers").'Users (teammembership_association)'
Many of the standard Dataverse tables have lots of relationships and the many options presented by Intellisense can be confusing. Knowing the particular relationship name you are wanting to use will save a lot of frustration!
- Hiredgun.tech: Many to Many Relationships
- Hiredgun.tech: Dataverse Relations
- Hiredgun.tech: Dataverse Navigation