Understanding Intersect Entities and N:N Relationships in Dynamics 365

In Dynamics 365, there are 3 different types of relationships:

• one to many (1:N)
• many to one (N:1)
• many to many (N:N)

When a many to many relationship is created by using the standard method of clicking “New Many to Many Relationship”, an “intersect” entity is created that allows for the relationship to exist. An out of the box example of a many to many relationship is systemuserroles, which maps System Users and Roles; a user can have many roles, and a role can belong to many users. However, creating many to many relationships this way can have limitations. The alternative method is to create a “manual” many to many relationship.

In this post, we will look at creating many to many relationships using the out of the box “native” method, which creates an intersect entity for you, versus the “manual” method of creating your own intersect entity.

Native Intersect Entity

Consider the example of students and classes. A student can have many classes, and a class can have many students. Let’s create these 2 entities, Student and Class.

Now, let’s create a new many to many relationship. In the class entity, select N:N Relationships, and New:

Select Student, and Save:

The new relationship will be created in both the Class and Student:

Next, we will modify forms to allow for adding of records. Open the Student main form:

Select Navigation. You will see Classes:

Drag classes to the navigation. Save and Publish:

Do the same with the Classes form. Save and Publish:

Now let’s create a new student record in the system. Then click on the arrow:

You will see Classes:

Click Add Existing Class and New:

Enter a new class:

Add some more classes:

Likewise, open a class, and add students:

The way this works, is Dynamics 365 creates an “intersect” entity between the Students and Classes. These intersect entities are created for both system N:N relationships (e.g. systemuserroles for System User and Roles), and custom entities such as Students and Classes.

To find an intersect entity, go to the N:N relationships of a given entity, such as the User, and find the relationship, such as Security Role:

On opening the relationship, you will see the Relationship Entity Name, in this case systemuserroles:

In our student-class example, the relationship entity is called new_new_class_new_student:

Note we can retrieve data from this entity using FetchXML, as shown below using the FetchXML Builder:

However, this method is limiting. For example, the entity is hidden, therefore you cannot add additional fields the intersect entity. To get past this limitation, we can create a manual N:N relationship as below.

Manual Intersect Entity

Consider the example where we have doctors and patients. A doctor can have many patients, and a patient can have many doctors. Let’s create these as entities in Dynamics 365.

Now instead of creating a N:N relationship like above, let’s create our own entity to manage the relationship. We will create a new entity called DoctorPatients:

Now go to N:1 relationships, and create a new relationship:

Enter Doctor as the name:

Repeat, adding Patient as the new relationship:

You should now have 2 N:1 relationships defined:

We will now add fields to the main form:

Drag across the Doctor and Patient:

Save and Publish.

Next, create some test data. Doctors:

Patients:

Now, create a new DoctorPatient record:

From here, we can create a new view to give us doctor patient records:

As this intersect entity is a “standard” entity in Dynamics 365, we can add new fields to it, for example the location the doctor and patient meet. From here, you can use this in workflows, reporting etc which depending on your use case may provide the extra functionality you need.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin