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

Saving Images in MS CRM

Have you ever found yourself wanting to store images as attributes on your entities? Out of the box Microsoft CRM does not directly support image attributes, leaving you to come with an alternation solution such as store the images on a file store and linking the path as a string attribute in the CRM database.

My first thought was to simply store images as a base64 string in a text field, but the largest text field supported (i.e. documented) is 5,000 characters – not quite enough for my favorite picture of my dog, Loki. However, since Microsoft CRM supports one-to-many relationships, it occurred to me the base64 string could be broken up into 5k chunks and stored as child entities to a master image entity.

The actual construction of the relationships is rather straightforward, but the real problem comes when associating an image to a specific entity. If you make the image a child of the entity, then it is not possible to associate other types of entities with the same image entity. What if you flip the relationship? After poking around in the database I found some of the built-in entities supported this type of relationship, in a loosely coupled manner. Rather than create a direct relationship between entities and images, you can create references to the entities Object ID with an associated Object Type Code to complete the relationship.

Once I put all of these pieces together I was able to add images to any entity in the system. Displaying them just takes reassembling the pieces and streaming them back to the browser. A topic for another day…

Welcome

Welcome to the AdvantageWorks blog where we will publish information on our upcoming releases, software innovations, and the future of CRM and how it applies to the “Information Worker”. We look forward to communicating with you – whether you are a member of our user community, a software developer, or a curious passer-by.

The AdvantageWorks Team