Many-to-Many Relationships

Does Microsoft CRM 3.0 support many-to-many relationship? Sure, just create two one-to-many relationships using a intermediary table and you can create as many complex relationships that you want. Easy for the developer… but what about the end user?

Let’s say you want to relate students to class given a student can enroll in many classes and a class contains many students. A classic many-to-many situation. Ok, what do we do?

1. Create a new entity – let’s call it crm_roster
   
2. Create a one-to-many relationship from crm_student to crm_rosterhalf way there!
   
3. Create a one-to-many relationship from crm_class to crm_roster
   
4. Voila! A many-to-many relationship is born!!
   

So now what? Say you are an end-user and you want to add students to a class, what do you do?

1. Open the class entity
   
2. Choose the Roster tab and click the New buttonWhat are you presented with? A list of students to pick from? Nope. You are presented with a dialog for editing the relationship (roster item), but not the roster itself. So continuing on….
   
3. You click the class search icon to open yet another dialog to select the students. Can you pick multiple students? Nope. Is this list filtered by students associated with this particular school (assuming you have more than one school in the system)? Nope.
   

Is there a better way? Of course there is! If you look carefully, Microsoft is using several many-to-many relationships between system entities such as users to roles. If you go to the user settings page and click the manage button, you are not presented with a dialog to edit a single relationship, but rather a checklist to select the roles associated to the user. Exactly what we are after!

Can you use this magic dialog? Unfortunately not. But don’t give up. There is still hope.

Solution available in Many-to-Many Relationships part II