Managing M-M relationships

Dynamics CRM 4.0 has capability of creating custom entities and creating relationships with either system or custom entity. However in case of custom entities and M-M relationship there is often a requirement for additional parameters to qualify that relationship. For example Entity 1 has M-M relationship with Entity 2 and the relationship has further classification attributes such as Attribute 1 and Attribute 2. CRM 4.o does not allow creatin of additional attributes once the M-M relationship is created.
Following is the Business requirement:

MM relationship
The above figure explains the business problem in general. To understand the intricacies of the problem imagine that our company deals with sports associations and sportsmen in those associations. Our sales team needs a system to maintain the information about these entities. Out of the box account entity in CRM 4.0 does not suffice to fulfill our requirements for the management of these two entities so we make custom web pages to maintain these custom entities. These custom entities have a drop-down for the selection of category (sportsman, sports association). Now for the sports associations we want to store the information regarding the states and counties they serve in US. We call these states and counties as “Coverage Areas”. So there is a relationship, M-M between our product/Entity Sports Association and “Coverage Area”.

CRM allows creating entities and relationship between those entities as well. However a simple many-to-many relationship would not serve the purpose as ideally on the entity form there should be a sandwich control and sales representatives should be able to quickly select the coverage areas. Moreover even if M-M relationship is created it would not be scalable so other attributes cannot be added to this relationship (This is how CRM 4.0 works). There are two solutions here in our case. However each should give results explained in image below. Sports association form should have the following section that allows for quick selection of coverage areas (state, counties).

coverage areas (state, counties)

Solution 1:

One solution is to make a custom web page for this entity. In that custom web page one can populate the left side of sandwich control with the all the states and counties. This would mean that there would be two more entities in system namely state and county. States entity would have all the states and county entity would have all the counties. Then for the states and counties associated with Sports Association there should be some way to store that. One could be to store in a hidden field in concatenated form and the other could be to create yet another entity for this. However as important thing to note is that as it’s a custom web form that is used for the custom entity so the solution would be dreadful in terms of performance. Javascript will be employed to fetch these entities and on “Save” button first of all pick up the selected values from the sandwich control and populate hidden field and then save. However this is one solution that can be opted for.

Solution 2:

This is the solution that I would recommend. In this case we don’t need a custom web page. One can use out of the box text areas for sandwich control pane and CRM buttons (blogs for creating custom buttons are available on net). Then we need to create the relationships and also to configure them so they are scalable. So we again create an entity that creates all the states and counties and another additional entity to store relationship between counties and associations.

In my next blog I will give the details on how we implemented solution 2.