I admit it: I’m a bear of little brain. In my first post I wrote about my favorite feature of CRM 3.0 – Filtered Views – but today I’m going to write about an area that makes my brain ache every time I have to deal with it: Entity Relationships.
Naveen Garg wrote a nice piece in August about Configurable Cascading Relationships, in which he shone a bright light on a powerful feature of Entity Relationships. I’m going to start right back at the beginning, however, because my brain starts to hurt long before the complexities of Configurable Cascading Relationships are encountered.
The basic idea behind entity relationships is not hard to understand, even for bears of little brain like me. I play guitar for a rock band, and I use CRM to manage our business affairs. I represent the places that the band plays as custom Venue entities in CRM. (Yes, it did occur to me that the Facilities/Equipment entity used by the Service Management module might work here.) Every Venue has a primary contact person, whom I represent in CRM with a Contact entity. To represent the fact that every venue has a primary contact, I need to create a relationship between my custom Venue entity and the built-in CRM Contact entity.
My little brain starts to hurt when I get to the Relationships dialog while building my custom Venue entity:
Here’s how I need to think to avoid confusion between the two relationship types: “I’m building a relationship in which one person might be the primary contact for many different venues, so I need to build a many-to-one relationship between the Venue entity and Contact entity”.
In the CRM world the entity at the “one” end of the relationship is the primary entity, and the entity at the “many” end of the relationship is the related entity. So I click on the “New Many-to-1 Relationship” button, and this is what I get:
Now I know that I’m on the right track, because the Venue entity has been correctly identified as the related entity – at the “many” end of the relationship – and I’m being asked to select a primary entity for the “one” end of the relationship.
I can confirm this by setting up the relationship (I gave it the display name Primary Contact), then looking at the list of Venue entity attributes, where I find a new lookup attribute that will enable me to pick a primary contact from a list of Contacts.
I add the new Primary Contact attribute as a field to the main Venue form, and I get exactly the behavior I was looking for:
I am, however, out of luck if I want to use the same method to create a secondary contact for the venue. If I try to create another many-to-one relationship from Contact to Venue, I’ll eventually hit this little gem of an error message, which is CRM’s way of saying that I can’t create two of the same kind of relationship between the same entities.
I can, however, create a one-to-many relationship between Venue and Contact entities to go with the many-to-one relationship that I already have. This gives me a Contacts link in the left hand navigation bar, and a way to associate as many contacts as I want with the venue, as long as I’m OK with the restriction that each of these contacts can only be associated with exactly one venue.
Which brings us to the problem of representing many-to-many relationships. As we’ve just seen, creating twinned one-to-many and many-to-one relationships between two entities does not produce the same effect as a many-to-many relationship: I can have one person be the primary contact for several venues, and I can have several other contacts for each venue, but only if the other venue contacts are not also contacts for another venue. What I can’t represent without a many-to-many relationship is the scenario where the same two people are both contacts for the same two venues.
Your best bet for faking out a many-to-many relationship is to build a custom entity that has one-to-many relationships out to the two entities you want to relate. Once you get everything set up right on all three entities, you’ll have a custom-entity-in-the-middle where you can see a list of contacts and an associated list of venues. On the Venue form the link to the custom-entity-in-the-middle might be labeled “Venue Contacts”, and on the Contact form the link might be labeled “Venues Represented”. The biggest drawback, however, is naming the records in the middle in such a way that they can be picked out of a list. You’ll be fine if the linking entity has some sort of independent reality (for example, a Lease entity representing a many-to-many relationship between Contacts and Apartments). But if the linking entity is merely a functional abstraction, you’ll be stuck. If you use un-intuitive record names (“XREF 1004”) nobody will be able to pick the records from a lookup, and if you use intuitive record names (“Venues represented by Bill and Steve”) you’ll find that they tend to read fine from one side of the relationship but not the other.