I get asked about many-to-many (a.k.a. N:N) relationships more than just about any other single topic, so here’s a summary of how they work in Dynamics CRM 2011.
I’ll review both “native” and “manual” N:N’s, using a simple Association to Contact relationship to illustrate. This is a classic many-to-many: each association should be able to have multiple contacts associated with it, and each contact should in turn be able to be associated with many associations.
With native N:N relationships, you open up an entity for customization, click the N:N relationships link, and click the New Many to Many Relationship button and select the entity to create the relationship to. For the Association to Contact N:N relationship, I’ll start from the Association entity:
In the previous figure, notice that Do no Display is selected in the Display Option for each side. I only show that to illustrate a point: what this will do is prevent the relationship from being displayed on either form. You won’t be able to open the association form and associate a contact, or vice versa.
Interestingly, it also turns out that if you select Do not Display in the relationship properties, you will not be able to create an Advanced Find query between two record types related with an N:N. So the Do not Display option apparently means “do not display in Advanced Find”, as well as “do not display on the form”.
Normally I’ll select the following option (Use Plural Name), so my users can actually access the relationship from the respective forms:
After saving and publishing these customizations, here’s what it might look like from the standpoint of an association form, with a few contacts associated:
Remember: if I’d used the Do not Display option I mentioned above, nobody would ever see the Contacts link on the Association form, so you wouldn’t be able to associate contacts with associations, or see any records anybody else had managed to associate!
Once you’ve got records associated in N:N relationships, there are a number of ways you can see them, besides navigating to a form like I just showed.
Here’s an Advanced Find query that will show all active associations with associated contact records:
I used to think you had to use the Select underneath Contacts to add a condition like “Contact contains data”, but you don’t. The way I show it in the previous figure does that implicitly.
The next figure shows the flip side: all of the contacts that are associated with associations.
If you work through examples like that, what you will find is that although N:N relationships are symmetrical as far as defining them goes, they aren’t quite symmetrical when it comes to Advanced Find: while you can filter on the related record type in Advanced Find, you can only include columns from the primary entity. For example:
However, you can still kind of get what you need…in a way, sort of. For example, here’s an Advanced Find query that will return information about all of the contacts in Association 1:
You can’t include any fields from the association entity, but you can drill through it in Advanced Find to query for a specific one.
With me so far? If you’ve got the patience to build a report, and your users don’t mind running it, you can create a report, using the Report Wizard, that does include fields from both sides of the N:N equation. For example, the following figure shows a report created with the wizard, where Association is the primary entity and Contact is the secondary:
This illustrates a general point about the difference between Advanced Find and reports created with the Report Wizard:
The primary advantage of native N:N relationships is how easy they are to create and understand.
However, there are several disadvantages:
Fortunately, there’s another way to create many-to-many relationships that solves all of these problems. And while it takes a little more work to do it, it’s not that much more work. And it’s definitely a lot less work than taking the wrong approach first and then having to change your mind and regroup afterwards! J
The so-called “manual” approach to creating many-to-many relationships adds two components not used in the native approach:
Here are two Visio diagrams that should help clarify the difference between the two approaches:
Native N:N Relationship between Contact and Association:
Manual N:N Relationship between Contact and Association, using Membership Entity:
In one sense, these two approaches are equivalent: a contact can be linked with one or more associations, and each association can have one or more contacts linked with it.
But in Dynamics CRM there are lots of differences, and you can see from the way I created the Membership entity that the disadvantages of the native approach are solved by the manual one:
While the creation of both kinds of many-to-many relationships is conceptually similar in Dynamics CRM 2011 and CRM 4.0, the actual execution of it is a lot different, and a lot easier! And rather than a long drawn-out step-by-step, here’s a video that demonstrates how to do it:
And just in case two ways to create many-to-many relationships weren’t enough, there actually is one more: Connections and Connection Roles. I’ve written a couple of articles about these, and if you review the most recent one, you might be able to tell from the title that these are really a specific implementation of the manual N:N approach I just reviewed. Basically, the Dynamics CRM team created a framework to give more exposure to these useful N:N relationships and to make them easier to use. In the terms I used here, you can think about it like this:
So…of these three approaches, which is best? As always, it depends on what you need to do, but here are some rules of thumb you can use as guidance:
Probably the easiest to configure but the most limiting. Use when you only need to know that two records are connected to each other but you don’t need additional information about the connection itself.
A little more work to configure, but generally worth the effort. Use when in addition to knowing two records are connected, you also need information about the connection, such as its status, when it was created and so forth.
Connections and Connection Roles
As I mentioned above, these are actually a specific implementation of the Manual approach. And if you delve into this a little, you’ll find that the Connection entity is a bona-fide customizable entity. You can even customize it, adding custom fields to the connection form and so forth. But…be careful about overdoing it: there’s only one Connection entity, and customizations made for one Connection Role generally will not be applicable to another one.
One specific advantage of these is that a single connection role can connect records of different types (e.g., contacts can refer other contacts, accounts and opportunities)
This is a judgment call, but I’d say to use these when you need to track some information about the actual connections (such as when they’re created and how many there are…), but not that much.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13