Skip to content

Walkthrough: Extended N:N Relationships in CRM 2011

August 11, 2011


N:N relationships in CRM 2011 have come a long way, and nowadays it is quite straightforward to model any N:N relationship between entities.

However, there is still one common case that I encounter regularly that requires a little of extra work to be modelled effectively, but with the new features of CRM it has become now much simpler to implement, and it can be done without writing any code with acceptable results.

Here is the scenario:

The need to extend N:N relationships

Imagine to have this requirement: You want to be able to associate contacts to cases and specify additional data related to the association:

  • The role that the contact has with regards to the case
  • The cost involved

Multiple contacts can be associated with a case, and multiple cases can be associated with a contact.

You could use the “Connections” feature of CRM, but this won’t give you the opportunity to enter the additional data you need.

In the past, implementing this requirement involved the creation of plugins and JavaScript, but with the introduction of the sub-grid procedure you can now go 90% of the way via configuration.

So, how do you do it?

The Method

Let’s start by creating an intersection entity between Contacts and Cases. Let’s call it “Case Involvement”:

Case Involvement

Case Involvement

Notice that the “Duplicate Detection” box is checked.

The “Name” attribute needs to be big enough to contain mapped values (more on this later), so I am increasing its default size of 100 to 500.

Name Attribute

Name Attribute

Now let’s create the two additional fields we need: Role and Cost. For the role I’m using an OptionSet that contains the values Technician, Auditor, Inspector and Subject Matter Expert.

Extra Data

Extra Data Fields

Next I’m going to create the two 1:N relationships that, together, represent the N:N relationship between Contacts and Cases. I can do this quickly by creating two new Lookup fields. Let’s start with the relationship with Contact:

Contact Relationship

Relationship with Contact

Note that the lookup field is marked “Business Required”.

By clicking on “Edit Relationship(Advanced” at the bottom of the form I can edit more properties, but before doing so I need to save the relationship and publish the entity, otherwise I won’t be able to add mappings to it. Once the entity has been published, I can configure the relationship I just created by adding a mapping between Contact.FullName and CaseInvolvement.Name:

Relationship Mapping

Relationship Mapping

This is why it is important that the Name attribute in the Case Involvement entity is big enough to contain the source mapping field (in this case Contact.FullName, which is 160 chars long).

The reason why I am going this extra step with the Name attribute is that Name is the primary attribute of the Case Involvement entity, and it is therefore required. However users are often confused about providing a name for a relationship, so I don’t want to make this attribute modifiable by the users, but I still have to fill it. Mapping will take care of this.

In the same way I now create a relationship between Case and Case Involvement, with a mapping between Case.Title and CaseInvolvement.Name. The only difference in this case is that I changed the relationship behaviour to “Parental”. This is because Case Involvements will “follow the case”, i.e. if a case is deleted or re-assigned, the involvement will go through the same fate. Otherwise you might find yourself with orphaned Case Involvements wich are not related to cases anymore.

Case Relationship

Case Relationship

Next let’s modify the Case Involvement Form. Note that I’ve added the extra data (Cost and Role) and the two Lookup fields (Contact and Case):

Case Involvement Form

Case Involvement Form

In addition, the Name field has been marked “Not Visible by Default”, which is a great new feature that will hide the field from the user, therefore lessening their confusion. However, because of the mappings, this field will be populated anyway. You can be a little more sophisticated and create the content of this field by yourself using JavaScript, for example concatenating the Case Title and the Contact Full Name and the Role, but this solution will do for now.

Uncheck Visible by Default

Uncheck "Visible by Default"

Next we create two Views for the Case Involvement: one named “Involvements for Cases” to display information about contacts that are associated with the Case, and the other named “Involvements for Contacts” to display information about cases that are associated with the Contact. Note that the default field “Name” has been removed from the views, since we really don’t want to have anything to do with it, that each view contains the Lookup field of the “other” entity, and that each view pulls some fields about the “other” entity that can be useful.

So “Involvement for Cases” pulls the City of the Contact, and “Involvement for Contacts” pulls the case customer. You can add as many fields as you want to the view, and this is a neat way to display information of related contacts in the case form (see below), without actually having a direct relationship between the two entities.

Involvement For Cases

Involvement For Cases

Involvement For Contacts

Involvement For Contacts

Now we are almost ready, let’s publish the customizations and look at the Case Entity. I’ll add a sub-grid that is displaying Case Involvements to the Case form:

Involved Persons

Involved Persons

Note that as the default view I chose the view for cases that I created above.

Optionally, I can also delete the relationship link to Case Involvement from the form, since, again, it is only confusing for the users:

Case Form

Case Form

Similarly, we can add a sub-grid to the Contact entity displaying related cases. Now let’s look at the result:

The Results



As you can see, the grid displays information about the contacts, their role and cost. I can add new Case Involvements and the process is intuitive, and I can directly access each contact by clicking on its name. One more note: since we have removed the primary field from the sub-grid view, if you want to open an existing Case Involvement you need to double-click on the record instead than single clicking on the name. There are many ways around this, but the idea presented here is that you can now do this without writing a single line of code, which I think it’s pretty neat.

A simple improvement to this solution is to create a duplicate rule on the Case Involvement that matches Contact, Case and Role, so that if the user tries to create two entries that relate the same contact and case pair with the same role, the system will alert them.

Alberto “En-to-en” Gemin

One Comment leave one →
  1. Amit Kumar Chaurasia permalink
    October 15, 2012 20:48

    Thanks a lot..Your post way really valuable..Please do keep posting such knowledgeable blog.. Again thanks a lot..

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: