Entity Relationships in Dynamics CRM 4
Dynamics CRM 3.0 was a good platform for building custom database applications. I realize that not everybody thinks of a CRM that way, but if you need to customize a CRM, and if your CRM stores its data in SQL Server like Dynamics does, it’s an important point.
Anyway, as good as CRM 3.0 was, the current 4.0 release adds some important improvements that might sound a little geeky at first, but actually solve some important and common business problems.
Here is a troika of videos on the three major improvements, and I hope you like them:
Self-referential Relatonships
While this is probably the geekiest-sounding of the 3, it might be the most useful. Basically, this lets you create a relationship (one to many) between an entity and itself. This lets you create parent-child hierarchy relationships between records. For example, a Case record might be the parent record of other cases; an Opportunity might have child opportunities associated with it. Of course, once you’ve got this set up you can reporting and querying to see all of the relationships between existing records; but there’s another scenario where it’s even more useful: if you combine it with field “mapping”, you can have a nice simple approach to “cloning” records. Cloning refers to making a copy of a record in CRM — in 3.0 you needed code to accomplish this but if you combine a self-referential relationship with field mapping, you get this for free in the 4.0 version.
Here’s a recording I made on self-referential relationships in Dynamics CRM 4.0. This is a good example of how to use the relationship feature to get the “cloning” function I just mentioned:
System to System Relationships
In CRM 3.0, you couldn’t create a new custom relationship between out of the box entities such as Account, Contact, Opportunity, Lead, etc. Basically, the idea was that any relationship you needed between these so-called “system” entities was created by the product team. In 4.0 this restriction is lifted and it’s an important improvement.
In this recording, I show an example where you create new relationships between the User and Account entities, to implement a kind of a team approach for account management.
Many to Many Relationships
If you want a contact to be able to attend many events and an event to be attended by many contacts, you need a many to many relationship. In CRM 3.0, the only way to implement this was with two “one-to-manies”, using a so-called intersection entity such as registration. Essentially:
a 1:N from Contact to Registration
+
a 1:N from Event to Registration
= a N:N between Contact and Event
Sometimes you need this approach, since the intersection record can contain useful information (like registration status, payment, etc.).
But sometimes it’s overkill, and it’s always more complex than the new “native” many to many relationship you can implement in CRM 4.0. I recorded a fresh video on 5/27/2010 that illustrates the native N:N approach used to model the relationship of contacts to boards of directors. The N:N is between the contact entity and a custom ”board of director” entity. Depending on your requirements, the native approach may work fine. Or it may not…anyway, here’s the video:
Let me know if you find these helpful!
Get the Trick Bag via RSS? Who wouldn't want that?
David Challener Said,
October 23, 2009 @ 6:28 am
Hi Richard,
Your blog is very informative and I have used several of your useful workarounds – especially the count on reporting!
One issue we’ve run into recently is with regard to the many-many relationships and being able to run an advanced find on them. Is this something you have seen, been able to work around? I know I can query in code using linked entities and using the ids of the respective entities – this detail isn’t available in the advanced find and feels somewhat overlooked?
Cheers,
David
Richard Knudson Said,
October 23, 2009 @ 8:10 am
Hi David —
Thanks for the question! I was glad you asked it because I’d been meaning to write a post about that for some time. I made a quick recording on the subject and posted it: http://www.dynamicscrmtrickbag.com/2009/10/23/n-n-advanced-find/
Enjoy — let me know if you have any questions.
Richard
Steve Chapman Said,
December 1, 2009 @ 9:52 am
Richard,
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?
Richard Knudson Said,
January 19, 2010 @ 2:51 pm
Hi Steve,
I feel bad it took me so long to answer your question. The reason is, I liked it so much I wrote an article to answer it, and … well… the article took me a little while to write.
Anyway, here you go: http://www.dynamicscrmtrickbag.com/2010/01/18/many-to-many-relationships/
You’ll see the section at the end where I directly address your question. Good luck, and thanks for reading.
Richard
Gustavo Melo Said,
May 26, 2010 @ 12:14 pm
Hi Richard,
All of your videos are broken =(
Could you re-host them?
Thanks.
Richard Knudson Said,
May 26, 2010 @ 12:41 pm
Hi Gustavo — good catch!
After I migrated our web site to Joomla I fixed a bunch of the broken links, but I didn’t get all of them! I’ll try to get those three recordings updated today. Thanks for letting me know about it!
Richard
Jack Howell Said,
August 31, 2010 @ 4:21 pm
Hi Richard– Your video on N:N relationships really cleared up the basics for me on how to create them and what they are used for in CRM 4.0.
But, now that I have one created I have run into an issue–which I hope is a small one
Say I created the N:N relationship you described with Board of Directors and Contacts. I now have the ability to manually associate contacts with many boards and for boards to have many contacts.
But– how do I mass import this type of data if I have an excel sheet with every contact and the various boards that they are on? How do I link one contact record to many boards and how do I do it without creating duplicate contact records for the same person?
Sorry for being a litle long winded, but I hope it makes sense. Thanks again and keep up the good work!
Jack Howell
Richard Knudson Said,
August 31, 2010 @ 8:49 pm
Hi Jack,
Good question. Not long-winded at all. Unfortunately, you’ve hit precisely on the main limitation of the “native” N:N. I referred to it above but it’s trick it at first. In your honor, I recorded this video which should clarify the issues: http://www.youtube.com/watch?v=Ad82SxU0QDA
Cheers — Richard
Many-to-Many Relationships the Old-Fashioned Way Said,
September 2, 2010 @ 7:18 am
[...] 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. [...]
Many-to-Many Relationships the Old-Fashioned Way - Richard Knudson’s Microsoft Dynamics CRM Trick Bag Said,
September 2, 2010 @ 8:17 am
[...] 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. [...]
alfred greenwood Said,
September 16, 2010 @ 4:44 am
hi. an interesting article. i have a question about creating an n:n relationship between the ’subject’ entity and a custom entity. is this possible at all?
many thanks
Ranjit Said,
December 17, 2010 @ 6:52 am
Really very useful…
Simon Said,
March 21, 2011 @ 3:21 am
Hi Richard, I’ve been searching the web for an answer to an issue I am having and your article is the closest I have got.
(sorry if this is a bit long!). We’re doing email marketing through MS CRM 4.0 and the email marekting system has created two custom entities to store all the campaign data. I am trying to report on that data against the leads to whom we are sending emails.
Following reading your article above I have now created an N:N relationship between Leads and both of these entities, but when I come to the reporting module (using the reporting wizard), the fields relating to the custom entities are still not visible for selection.
Any idea what I may be doing wrong?