Share via

Dealing with many 1:M CRM relationships

  • Article

I while back I mentioned in the CRM newsgroups that it was possible to create multiple relationships between two entities. For the most part this is true, but there are some hoops to jump through and each of those hoops is ringed in fire. More specifically, using this approach to solve the "many relationships" problem will plant you squarely in unsupported, probably-can't-upgrade, land.

 

The problem I'm talking about is where you have a single parent entity - the 1-side, and you want more than 1 1:M relationship from that parent entity (let's call it P) to a child entity (smartly named C). Let's try an example. Say we have a Course that has an Author and an Instructor. Now, ignoring for a moment that CRM can't deal with additional party types and won't allow multiple relationships to SystemUser, we'll call Author and Instructor simply views over the same set of data. That is, they are the same entity with different roles.

 

In CRM v3.0 you'd implement this by creating the three entities, setting up the Course-Author and Course-Instructor relationship, hiding the Instructor entity, and writing a callout to keep the Author and Instructor data in sync. That'll work and keep you supported. There are a few caveats that will cause some serious grief: you can't use the quick create functionality from an Instructor lookup, you need to keep the security consistent, and you can't completely hide the Instructor type as a separate type.

 

Well, there is a way around this. It's a hack but it's been successfully hacked for some internal development deployments. Create the three entities as you normally would but keep the child entities Instructor and Author "empty". Set up the relationships using the configuration tools. You now have three entities with two relationships much like you had in the supported approach. Now, customize both Author and Instructor in identical ways by adding all the attributes that the type needs. When you're done you'll end up with two identical entities pointing at two different database structures. You're still in supported land.

 

Do not add any instance data to either Author or Instructor yet.

 

Next, change all the display attributes for the Instructor entity so that it looks like an Author (this really isn't necessary and might make sense usage-wise if you treat them as different). Ready to jump into unsupported territory? Open the metadata database and find the Instructor entity (let's assume that Author is the primary view) in the Entity table. You'll see a few columns that describe physical table mappings for the core and extension tables. Change the table names to reflect that the Instructor entity should instead point at the Author tables. You'll want to make a few modifications to the Instructor attributes as well so that they reflect the underlying physical Author attributes (I think the primary key in the base and extension table is the only attribute you need to really worry about).

 

Now, every time you add an Instructor or Author the data ends up in the same physical table. That means that any query over either entity will return the same set of data. So, your lookup control from Course to Author will present the same physical data as Course to Instructor. As far as CRM is concerned you have two different entities with two different relationships. You end up with copies of all the web service methods and the schemas will look nearly the same (their logical views will reflect the as-configured names, not the physical names). But, at a physical level you only have one copy of the data that you look at and edit, there are no synchronization issues, and there's no tricky callout code to handle multi-master updates.

 

I might have missed a few steps here because I'm doing this from memory and not on a live CRM installation. If you find that some of the steps seem incorrect (missing, out of order, unnecessary) then post a comment here so everyone gets a chance to learn along with you.

Comments

  • Anonymous
    June 14, 2006
    Why not? I was just thinking about yesterday's post and had one of those disturbing thoughts that I get...
  • Anonymous
    June 14, 2006
    Do you anticipate any problems with exporting this sort of customization to another system?
  • Anonymous
    June 14, 2006
    Like I said, this is an unsupported modification. Things might work, they might not. My assumption is that this sort of customization won't survive the export / import process at best. At worst it'll probably destroy something. The first approach that I mentioned, using callouts with multiple entities, will work, but there are a lot of moving parts.