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.

Native N:N Relationships

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:

  • If Associations is in the Look for, you can see which associations have contacts, but you cannot add columns from the contact entity.
  • If Contacts is in the Look for, you can return contact columns for contacts in associations, but you cannot add columns from the association entity.

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:

  • With Advanced Find, you can start with the parent record and filter on values from the child record, but you cannot include columns from the child record.
  • With reports, you can start with the parent record and filter on values from the child record…and you can also include columns from the child record, effectively “looping” through all of the child records for each of the parent records.

Advantages and Disadvantages of Native N:N Relationships

The primary advantage of native N:N relationships is how easy they are to create and understand.

However, there are several disadvantages:

  • As I showed above, you cannot use Advanced Find to build a view that contains columns from both sides of the relationship.
  • You also cannot import data using the Import Data Wizard. For example, if you created the N:N between contact and association in CRM and then wanted to import a big list of association members (contacts) and get them attached to the associations while importing…this isn’t possible.
  • You also cannot take advantage of workflows with the native N:N approach. For example, you might want a notification workflow to run automatically when a new member is added to an association. Or you might want a workflow to create a member record when some condition is satisfied. Sadly, neither of these are possible with the native N:N approach. L

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

Manual N:N Relationships

The so-called “manual” approach to creating many-to-many relationships adds two components not used in the native approach:

  • A third entity – often referred to as the “junction” or “intersection” entity – is used to contain “instances” of the relationship. For example, with associations and contacts, you’d create a custom entity called something like “Association Membership”, or maybe just “Membership”. If you were building an event management application, you might have contacts and events related with an N:N, and create a custom entity called “Registration” as the intersection entity.
  • Instead of creating a direct N:N, you relate the two entities indirectly, by creating 1:N relationships to the intersection record type.

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:

  • You can create an Advanced Find view on the Membership entity, and include any fields you want in the view, whether from Membership, Contact or Association.
  • If you’ve got some combination of contacts, associations and memberships in spreadsheets, you can import them all into Dynamics CRM. (And for bonus points, the Data Import Wizard in CRM 2011 lets you create custom entities and fields on the fly, as well as associate child to parent records, so you could do everything in one pass: create the custom association and membership entities and import the data!)
  • Workflows can run on the Membership entity, so the scenarios I mentioned above are possible along with anything else you can think of.

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:

Connections and Connection Roles

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:

  • The Connection record is the intersection record.
  • The Connection Role defines the N:N relationship, specifically the two “parent” records each of which will have a 1:N relationship to the Connection record.

(So Many) Many-to-Many Options: Which to Use?

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:

Native N:N 

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.

 Examples:

  • Custom entity Industry with an N:N to Account
  • Add a custom N:N relationship between the Competitor and Territory entities to track which competitors are active in which territories
  • Custom entity Color with an N:N to Contact (you don’t track your contacts’ favorite colors???)

 Manual N:N

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.

 Examples:

  • Associations and Members
  •  Events and Registrations (1:N from Contact to Registration, 1:N from Event to Registration)
  •  Subscribers and Subscriptions (1:N from Contact to custom entity “Subscription”, 1:N from custom entity “Subscription Product” to Subscription)

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.
Examples:

  • Referrals (Contact to Contact, Contact to Account, Contact to Opportunity)
  • Former Employee (Contact to Account, Lead to Account)
  • Board of Directors (Contact to custom entity “Board”, Lead to Board)