Background: One-to-Many Relationships

The most frequently encountered relationships between Dynamics CRM records are “one-to-many”, often referred to in shorthand as “1:N”. On any Dynamics CRM form, these are exposed as the links you see on the left-hand side. Basically, every link you see there is a record type to which the record on the form has a 1:N relationship. For example, the following figure shows an account form with the Contacts link selected. This displays all of the contact records which are related (via the “Parent Customer” lookup field on the Contact form) to the current account record

.

Before moving on to many-to-many relationships, let’s examine 1:N’s from a different perspective. Look at the next figure. It shows the flip side of the 1:N from account to contact. That is, it shows an N:1 from contact to account:

This shows how it looks from the standpoint of the “child” record. In this example, there can be multiple contact records associated with one account record, so on the contact form the account (”Parent Customer”) is selected with a lookup, from which only one account record can be selected. If you wanted one contact to be able to have multiple parent accounts, the simple 1:N from account to contact won’t let you do that. That’s an example of where you’d need a “many-to-many”.

Many-to-Many Relationships

One-to-many (1:N) relationships are common, but there are plenty of situations that require a many-to-many (”N:N”) relationship. For example, a single contact might attend one or more events; and a single event might have multiple attendees. In my experience, N:N relationships are more common than you might at first think, and there are some subtleties about how they’re implemented in Dynamics CRM. Let’s look at a few examples.

A commonly requested customization is to make the relationship between records and “Owners” more flexible. Most records in Dynamics CRM are so-called “user owned” records, which simply means that each one is associated with a single user — the “Owner” you can see an example of in the following screenshot of an Opportunity form:

Comparing this to the previous figure, you can see there’s an N:1 relationship from Opportunity to User (referred to as “Owner” on the form), which is equivalent to saying there’s a 1:N from User to Opportunity.

In this scenario, the request might be to associate more than one user with an opportunity. For example, you might have an account manager, an executive sponsor, and a sales engineer, each with certain required tasks in the sales process. Suppose you knew for sure that those three roles were the only ones you needed. In that case, you could create two new custom 1:N relationships from User to Opportunity, and add the associated lookup fields to the opportunity form:

I only had to add two, since the “Owner” was already there, and I just changed the label on the form to “Account Manager”. This works fine, but it’s hard-wired to only three users per record. What if you don’t know how many different users might need to be associated with a record? That’s a job for a true N:N relationship. The following figure shows how this can look. I’ve created a custom N:N relationship between Opportunity and User, and I referred to it as “Stakeholder”. The screenshot shows an opportunity form open with the “Stakeholders” link selected.

This looks similar to the very first screenshot in the section on 1:N’s, but it’s different in an important respect: with an N:N relationship both sides of the relationship behave exactly the same. In this example, that means that if you open a user record you can see all the opportunities for which the user is a stakeholder:

So, not only can each opportunity be associated with lots of stakeholders, each stakeholder (a.k.a. “User”) can be associated with lots of opportunities: a true N:N relationship.

To create an N:N like the one I just illustrated, assuming you’ve got System Administrator privileges, follow these steps:

1. Open the Opportunity form’s customization UI, by clicking Settings, Customization, Customize Entities, and then double-clicking Opportunities in the list.
2. Click N:N Relationships, then click the New Many-to-Many Relationship button on the toolbar.
3. Select User in the “Other Entity” section, and configure the other properties like this:

   

Then click Save and Close twice, then publish and test your customizations.

Just remember: the value in the “label” field is how the entity in each section will be referred to from the form for the other entity. This can be confusing, and it’s probably easiest to start by using the default “Use Plural Name” option in the Display Option drop-down.

Limitations and Subtleties of “Native” N:N Relationships

The approach I just described is often referred to as a “native” many-to-many relationship. It’s a new feature in the current version of Dynamics CRM, and can be used instead of the alternative, the so-called “manual” approach. The manual approach is not new, by the way, and simply consists of two 1:N relationships. For example, I implement N:N relationships between contacts and events by creating 1:N relationships from each of those two to a third entity, “registration”. A 1:N from contact to registration, and a 1:N from event to registration gives me an N:N between contact and event.

These native N:N relationships are easy to create, but have at least one important limitation, and some subtleties that can be confusing at first. I’ll cover those here.

• Limitation #1: All you know is whether a relationship exists – you don’t know anything about the relationship. The easiest way to understand this is to think about the manual N:N I just described. With that approach, the third entity – often referred to as the “junction” or “intersection” entity – can contain useful information. If I want to know the status of a registration, or what the registration fee was, then the manual approach works better.
• Limitation #2: While you can use Advanced Find to query for which records are involved in an N:N relationship, you cannot add columns from the opposite side. Basically, the problem here is that Advanced Find doesn’t really work for native N:N relationships. The way I think of it is that with Advanced Find, you start at the level of the child (or “N”) record, and work your way back up. So if I want to do a query on opportunities and return in the result set lots of information from account records, I have to start with opportunities. But if two entities are related with a native N:N relationship, there is no parent or child entity, both entities being peers, and without a 1:N relationship, Advanced Find doesn’t work!

Limitation #2 is actually more general than the Advanced Find example I illustrated with. What else in Dynamics CRM depends on 1:N relationships and makes you start at the child record and work your way back up? Correct: Workflows!

Hence the following question from a very patient TrickBag reader:

I recently created a custom entity (Network Event)that has a N:N relationship to Contact. What I’m trying to do is identify Network Events at which I meet contacts (new and existing).  So for any Network Event there may be several Contacts, and for any Contact there may be several Network Events.  I used the native N:N relationship, and it works just as I want it to.

What I am having a problem with is creating a Workflow that will update two custom fields on the Contact record to show the date of the event and the name of the event.  I’m just trying to indicate the last activity with my Contacts.  I’ve been successful in creating workflows for Tasks, E-mail, Appointments, etc. that update the two custom fields on the Contact record.

When I try to create A Workflow from the Network Event that will update the Contact record, the Contact is not an option as a Related Entity.  What am I missing?

Stephen, I’m sorry it took me so long to answer, and I’m sure you know this by now, but the answer is you aren’t missing anything except that intersection entity I mentioned above. Neither Advanced Find nor Workflows gives you a handle to columns from entities on the other side of a native N:N relationship. That’s why you could do it for your Task, E-Mail and Appointment activities: they all have N:1 relationships to contacts. But the way you modeled your contacts and networking events won’t work for that. Try creating a third entity, “Networking Event Attendance”, and give it fields for the date and name of the event. Then, when you write your workflow on the Networking Event Attendance entity, it will work fine.

And in Conclusion…

Well, this article certainly turned out longer than I thought it was going to be when I started it. I guess if you’ve read this far, you’re really committed, so I’ll make two more observations:

1. Although Advanced Find doesn’t work with the native N:N approach, the Report Wizard does work. In the opportunity to user/stakeholder N:N example I used, you can create a report with Opportunities as the parent record type and “Stakeholders” as the related record type, and you can group on opportunity, displaying within each group one row for every stakeholder.
2. Another good example of where an N:N relationship comes in handy has to do with synchronizing records to the Dynamics CRM Outlook client. Suppose you and your users want to synchronize records locally (Local Data Groups, in the Outlook client), and you can’t come up with any convenient criteria to use. You might put a checkbox on a form that’s a Yes/No to synchronize…but the problem with that is if it’s selected and used to synchronize…it synchs for everybody! A good solution here is to create a native N:N between the entity in question and the User entity. Call it “Synching Users” or something like that, and let users add themselves as a “synching user” for any record they need to synch. It works great.

If you read this far I’m eternally grateful, not to mention impressed with your dedication. Thank you very much, and good luck with all your relationships, Dynamics CRM and otherwise.

P.S., if you liked this article, you will positively adore my upcoming Dynamics CRM Essentials sessions, especially the ones on Customization. I hope to see you in an upcoming session!