Dataverse Many to Many Relationship

There are 2 ways to create a many to many relationship between tables in Dataverse, either directly or with a custom intersect table

Let’s consider the user case of assigning engineers to projects. This is a typical many-to-many relationship because an engineer can work on many projects and each project can have many engineers working on it

Option 1: Direct Many to Many Relationship

With Dataverse, a direct many-to-many relationship can be created between 2 tables

Once created, viewing the relationship tab for either table shows a many-to-many relationship with the other. However, Dataverse actually generates a virtual intersect table between the two tables

We know this table exists because we see the name of the virtual intersect table when the relationship is created, as can be seen below

However, once the dialogue box is closed, there’s no further sign of the virtual table. It’s not visible when viewing the tables in the environment. Also we don’t see the table when looking at the solution either

Unfortunately this means we can’t create additional columns within the virtual table or see any records that it contains.

Associating an Engineer to a Project

The records in the Engineer table are presented in a very simple gallery called galEngineer shown below and the records in the Project table are displayed in a simple gallery called galProject,

With a direct many to many relationship, to assign an engineer to a project, we relate a record in galEngineer to a record in galProject

Relate(galEngineer.Selected.Projects,galProjects.Selected)

This would also work equally well the other way around

Relate(galProject.Selected.Engineers,galEngineer.Selected)

Once done, in this example, Cath Best, Javascript Engineer is assigned to Project Baracuda

Capturing Additional Information on the Assignment

Because the intersect table is virtual, it’s not possible to add custom columns to this table

If we had the requirement to record the dates that engineers were assigned to a project, we could create a custom ‘Assignment’ table to capture this info. The assignment date could ‘patched’ to that table at the same time the Relate function was invoked

Patch(
   Assignments
   Defaults(Assignments),
   {Enginner:galEngineer.Selected.Name}
   {Project:galProject.Selected.Title}
   {'Date Assigned':Today()}
)

The records in the Assignment table are a log of the engineers who have worked on projects. This would be useful for reporting or resource tracking purposes

To remove a relationship, the Unrelate function is used. We would ‘patch’ an update to the ‘Assignment’ table at the same time to record the date the relationship was removed and the engineer stopped working on the project

Unrelate(galEngineer.Selected.Projects,galProjects.Selected);
Patch(
   Assignments,
   Lookup(
      Engineer=galEngineer.Selected.Name,
      Project="galProject.Selected.Title
   ),
   {'Date Released':Today()}
)

Option2: Custom Intersect Table

The 2nd way to create a many to many relationship is to create a custom intersect table. The custom intersect table itself can also be used to store all the required data on the relationship

The custom intersect table has a many-to-one relationship to both the Developer and Project tables. Because Assignment is a custom table, it is visible when viewing the tables in the environment. Custom columns can be created and the records it contains can be viewed

Associating an Engineer to a Project and Capturing Additional Info

The start and end dates of assignments, can be populated at the same time records are associated. The ‘Date Assigned’ and ‘Date Released’ would be columns created in the Assignment table

To create a relationship between an Engineer and a Project, when the relevent records are displayed in the galleries, the Patch function is used to create a record in the Assignment table. The record is related to the Engineer and Project tables through the Lookup columns in the Assignment table, as shown below

Patch(
   Assignments,
   Defaults(Assignments),
   {Engineer:galEngineer.Selected},
   {Project:galProject.Selected},
   {'Date Assigned':Today()}
)

With a custom intersect table, to record the fact that the developer no longer works on the project, we again use the Patch function to add an entry in the ‘Date Released’ column to denote this

Patch(
   Assignments,
   Lookup(
      Engineer="Cath Best",
      Project="Baracuda"
   ),
   {'Date Released':Today()}
)

Alternatively we could removed the record from the Assignment table altogether, but this would meant deleting the assignment history too

Using a custom intersect table, the association between the two records and the additional info is all captured using the same Patch command

Summary

Which is the best technique to use? The four main issues to consider are summarised below

Criteria	Comparison
Ease of coding	Very little difference between the two options
Speed of relating records	No difference
Speed of identifying whether a relationship exists	Relate is slightly quicker as it uses dot,notation. Patch requires the intersect table to be simultaneously filtered on both Lookup columns to the two related tables to pinpoint the record and this takes a little longer
Visibility of the intersect table and its contents	If this is important then using a custom intersect table is the way to go

Which would I recommend?

The fact that with the Relate option, the virtual table or its contents aren’t visible can be frustrating. I like to be able to see a table and its contents, particularly when creating and testing an app to make sure all works OK. Also if someone unfamiliar with the app starts working on it, the virtual table structure isn’t immediately obvious

Relate is slightly faster when identifying whether a relationship exists. The Relate function uses dot.notation rather than the Filter function so is less complex. It usually doesn’t make a noticeable difference

By default I’d tend to use a custom intersect table. If the number of records in the intersect table is likely to become large, I’d consider switching and creating a direct many to many relationship between the 2 tables instead

More Info

Hiredgun.tech: Dataverse Relations
Hiredgun.tech: Dataverse Navigation
Power Apps Reference: Relate & Unrelate