Adding Attributes To a Many-to-Many Relationship in Microsoft Dynamics 365

Dominic Jarvis, 08 September 2017

Why would I Want to Do This?

This last week I’ve been experimenting with more of the features present in Dynamics 365. I ran across an issue when I was creating an Event Management scenario.

A brief overview of my scenario: A company wanted to manage multiple events, which contacts can register for. Seems like a fairly standard N:N relationship. One small issue. Native many to many relationships in Dynamics 365 do not have the ability to store attributes on the relationships.

Why is this an issue? Say for example, I want to track the amount of tickets that each contact requires, and retain this value to perform operations against the event (reduce total capacity to prevent overbooking etc.). I might want to track whether or not a contact has any dietary restrictions, or special considerations for a particular event but not for others. All of these would be most appropriately stored as an attribute of the relationship itself, and not of either the contact or the event.

An Overview of the Desired Relationship

Basically, what we are wanting is for the relationship itself to be able to track and maintain these attributes, so that the event is able to maintain these values, and the ability to distinguish their source.

How Do We Accomplish This?

In order to accomplish this, we’ll need to instead of creating a native N:N relationship between the Contact and Event entities, create two 1:N relationships with an intermediate entity which stores the data that we wish to retain. This consists of a 1:N relationship from the Event entity to the Registration entity, and a 1:N relationship from the Contact entity to the Registration entity. This allows for a contact to be related to multiple registrations, and multiple registrations to be related to a singular event, allowing the same sort of relationship between the Contact and Event entities as a native N:N relationship. Now to implement the functionality.

For our purposes, let’s create an entity called ‘Registration’ which manages the registration of the contact to the event. When creating this entity, we can add any number of these attributes that we require.

To actually implement the 1:N relationships is very simple. In your solution, navigate to the endpoints of the N:N relationship (in this case, the Contact and Event entities), select the 1:N Relationships section, and create a ‘New 1-to-Many Relationship’. As the related entity of the related entity, select the previously created intermediate entity - in this case, the Registration entity. This will create a lookup on the related entity (this will need to be added to the related entity’s form), which can be used to select the primary entity.

By making the lookup field required on the intermediate entity, the intermediate entity will always contain a reference to the N:N endpoints. In our example, registrations (and their corresponding contacts) will be accessible from the Event entity, as well as the corresponding relationship attributes. Sub grids can be used to display records, and rollup fields are an excellent choice for displaying additional information, such as: Number of tickets purchased, Number of attendees, etc.