Walkthrough: Extended N:N Relationships in CRM 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.
So, how do you do it?
Let’s start by creating an intersection entity between Contacts and Cases. Let’s call it “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.
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.
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:
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:
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.
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):
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.
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:
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:
Similarly, we can add a sub-grid to the Contact entity displaying related cases. Now let’s look at the result:
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