Create custom N to N relationship in Dynamics CRM

Disclaimer: It is recommended to use the existing OOB N: N relationship for an entity. The custom N: N relationship is only to be used for entities that do not support the N: N relationship.

As the title suggests, in this blog we are going to discuss how to create a custom N: N relationship. The topic is divided into the following parts:

1. Need of custom N: N relationship.
2. Implementing custom N: N relationship.

So, let's begin.
1. Need of custom N: N relationship

        One day, I received a requirement to develop functionality where a user can add multiple users and contacts for appointments and phone calls, and later on, he can send notifications to selected contacts or users via mail. The approach was simple, to create an N: N relationship between appointment to user and contact and then add a subgrid on the form for each entity. But for activity entities, CRM does not allow you to create an N: N relationship. 

Hence, a custom N: N relationship was needed to be created to achieve the required functionality.

2. Implementing custom N: N relationship

        Let's take the same scenario, where I have to implement the N: N relationship between appointment to user and contact, and the same relationship needed to be done for a phone call as well. The implementation is done in such a way, that you can implement custom N: N relationships for any entity (Note:- As I mentioned earlier, it is recommended to use OOB N: N relationship if available for an entity). We are going to perform the following steps, to implement the relationship:

First Create an Organization owned entity called Custom Relationship.

Create a field called 'Primary Entity' as OptionSet. Add option set values as 'Appointment' and 'Phone Call'(you can add entities that you want)

Create a lookup field for each entity option added in the 'Primary Entity' field. In my scenario, I have created for 'Appointment' and 'Phone call'.

Place all created fields on Main Form as read-only.

Set the Auto number for the primary field(Name) of entity (Optional step, but needed for the uniqueness of data)

Now Create N: N relationship with the user and then with contact.

Create a quick view form for each N: N relation entity.

So now we have created quick view forms for each N: N relationship grid.

Inside Security roles, Assign 'Read', 'Append' and 'AppendTo' permission for the custom relationship entity.

Publish Custom Relationship entity.

Now we are going to do customization in primary entities.

Note:- Below all steps are needed to be performed for each entity that added in the primary entity option set values. Here, I have to perform the below steps for the appointment and phone call entity.

Now, let's move on to the Appointment entity.

Create a new lookup field for the Custom Relationship entity.

In the Main Form, Insert Quick View Form of Custom relationship for user and contact.

Publish the changes.

Now we have to create a custom relationship record on appointment create. 

The below code is registered in appointment create. Here is the link for the complete code.

In the code, we are creating a Custom Relationship entity record related to the primary entity. Then we are updating the custom relationship record on the primary entity.

Register plugin with the below configuration for appointment entity.

Unsecured Configuration Description:

• lookupfield_LogicalName: It is the lookup field name of the appointment entity in the Custom relationship entity.
• lookupfield_primaryEntityValue: It is the Primary entity optionset field value for appointment from the Custom relationship entity. 

We are done with customization and development. It's testing time now.

As soon as you create a new appointment record, options to add user and contact will be shown on the form.

To retrieve all the users and contacts attached to the appointment, write a script to retrieve N: N relationship records of Custom Relationship record linked to the appointment.

Additional Customization:

• Due to any reason, if a user does not have 'Write' access on the primary entity of the record, you can add the below ribbon customization to prevent users from adding/removing records in the subgrid. Click here for the ribbon JavaScript function.

• For users to not have access to Custom Relationship record, you can remove navigation for Custom Relationship.