An “Under The Hood” Look at Many to Many Relationships in Microsoft CRM 4.0

One of the great new features of Microsoft CRM 4.0 is many to many relationships.

Let's take the following scenario: A soda distributor (or pop for those up north) wants to track which products are carried on which routes. This is a many to many relationship, because each route can have many products, and each product can be carried on many routes.

To track this relationship, their CRM Administrator sets up a many to many relationship between the entities new_route and new_soda. Now they can associate many routes with many sodas.

So next they pull up the record for their Diet Cherry Fizz Cola and associate it with two routes, Route 1 and Route 2. What just happened from a data perspective?

1. When they created a many to many relationship, Microsoft CRM created a new entity to store the relationship data. This is a hidden entity—you won't see it from the list of entities to customize. The default entity name that Microsoft CRM uses is a concatenation of the two entities that are being related—in this case called new_new_route_new_soda. The good news is that you can specify whatever name you want for this entity, so they chose a name that would make more sense, such as New_soda_route. This is highly recommended to make life easier when you write reports.
2. There are only 3 attributes in this entity: The GUID for the New_soda_routeid, the GUID for the new_routeid, and the GUID for the new_sodaid. Here's a look at the values that now exist in the New_soda_route Filtered View:

New_soda_routeid                                        New_routeid                                                New_sodaid

A1A05FA1-1AD0-DC11-81C8-0003FF1D0014    E0B10854-11D0-DC11-81C8-0003FF1D0014    4747EA66-0ACD-DC11-811F-0003FF1D0014

C1F64FA4-1DD0-DC11-81C8-0003FF1D0014    60881558-1DD0-DC11-81C8-0003FF1D0014    4747EA66-0ACD-DC11-811F-0003FF1D0014

Notice how the view only includes the GUIDs, it does not include the names of these values. Also, notice how the system created a record for each of the routes associated with the Diet Cherry Fizz Cola GUID—so if we had 50 routes tied to Diet Cherry Fizz Cola, we would have 50 recordsin the entity, one for each route, each referencing Diet Cherry Fizz Cola. So if we had 500 routes and 500 sodas, with each soda carried on each route, this entity would have 250,000 records.

Good stuff to know for anyone who wants to extract data from the MSCRM database or write SRS reports. Also, an important configuration consideration—since additional attributes cannot be added to the many to many entity, in cases where it is necessary to track additional characteristics of the relationship, a custom cross-reference entity would still be required. For example, if the soda company wanted to specify how many cases of Fizz Cola were required on a weekly basis for each route, they will need to manually set up a Soda Route cross reference entity.