Many-to-Many Relationships the Old-Fashioned Way

Sometimes, Old-Fashioned is Best

For example, here are four:

1. A person might be a director on many boards, and each board of directors can have many members.
2. An even might have many attendees, and any particular contact might attend multiple events.
3. Your firm might have many different product lines you’d like to market to accounts or contacts, and you might want to flag each potential customer to receive marketing materials about one or more of those product lines.
4. A church may have many parishioners, but any particular parishioner might hedge his bets a little and join a few different churches just in case.

OK, enough already — you get the point.

There are two basic ways to model things like this in Dynamics CRM:

• Dynamics CRM 4 introduced a so-called “native” N:N relationship, where you simply add a custom N:N relationship between two entities directly. This is simple, it doesn’t matter which side of the N:N you create the relationship from, and I’ve covered it pretty thoroughly in articles like this one.
• After a few recent questions from Trick Bag readers, it occurred to me that I’d somewhat neglected the alternative approach, the so-called “manual N:N”, which I refer to as old-fashioned since it’s been around forever or at least since Dynamics CRM 3.0 came out. Here’s some guidance on what to use when, and a video I recorded on the good old-fashioned manual approach.

Many-to-Many Relationships: Native or Manual?

Like I said, native N:N’s are easy, but there are a couple of problems with them:

• When you associate two records using this approach, there’s no form to fill out — you just make a direct relationship between the two records. For example, if you had a custom “Board of Directors” entity with a native N:N to the contact entity, you could open the form for a board, click “Contacts” in the details section, and click Add Existing Contact.
• What this means is that apart from the association between the two records, there’s no additional information. For example, you couldn’t track a board member’s specific position, the duration of their term and so forth.

In fact, both of those points are symptoms of the same underlying problem: native N:N relationships don’t have a junction, or intersection entity to join them — all they have is the relationship itself. In addition to not being able to track any information about the relationship, this also means for example that you wouldn’t be able to import records: since there’s no intersection record, there’s nothing to import. It makes reporting problematic as well, since all you have to work with is the N:N relationship…and again, no additional information about the relationship.

The manual approach solves these problems, and it really isn’t much more complicated, apart from requiring the creation of the intersection entity. What you do is create custom 1:N relationships from the entities on each side of the N:N you need, to the intersection entity, and place the lookup fields on the intersection entity’s form.

Referring back to examples 1-4 above, here’s how I would do it in each case:

1. Manual. After creating the custom entity for “Boards”, create one for “Board Memberships”. Create 1:Ns from Contact to Board Membership and from Board to Board Membership. Give Board Membership attributes like “Term Duration”, “Member Type” and so forth.
2. Manual. Two custom entities: Event and Registration. 1:Ns from Contact to Registration and from Event to Registration. Registration has attributes like “Status” (use Status Reason maybe), “Registration Fee”, “Special Dietary Preferences” and the like.
3. Native. This one I’d do with the native approach. If all I care about is whether an account should receive marketing materials for specific business lines, it’s probably not necessary to track anything else about the “status” of their interest in those lines!
4. Manual, definitely. 1:N from Contact to Affiliation (custom), 1:N from Church (custom) to Affiliation. Affiliation has fields like “Commitment Level”, possibly “Start” and “Stop” date fields and so forth.

Here’s a video that illustrates how to create the manual variety of many-to-many relationsnhips: