I’ve always been a big fan of the Relationships feature in CRM, and that’s going back to version 3.0. I was always finding uses for it. It was one of those unheralded features that was both built-in and tremendously versatile. CRM 2011 has turbo-charged Relationships with the advent of Connections. Relationships are being deprecated in favor of Connections. If you had a lot of Relationships records in CRM 4.0, you will still see them in 2011 when you upgrade, but you can’t create new ones. Plus you won’t want to once you see what Connections can do, but that’s for another blog post. But what if you want to convert all those Relationship records to Connections in 2011? You came to the right blog.
Converting Connections to Relationships can be tricky, and that’s because of the way the Relationships table is set up. Similar to Connections, for every one Relationship between two records there are two Relationships records. For example, if you have a relationship record in your CRM between contacts Teddy Ballgame and Babe Ruth, there are technically two records in the CustomerRelationship table. In the contact record, users only see the same relationship when they look at either contact, but that’s just the CRM Team making it look easy again. Here’s a screen print of what a Relationships record looks like in the database itself:
Each record is expressed twice. I’ve pointed out the customerrelationshipid and converserelationshipid with my fancy arrows there and it just so happens I’ve arranged the dataset there to show each relationship in pairs. Even that can be tricky, but we’ll take care of all that.
The real crux of the matter is that if you merely insert a Connection record for each record in the CustomerRelationship table, you’ll wind up with a boatload of duplicates. And there is no clean way to clean those out. So in the spirit of an ounce of prevention, here is what you need to convert your Relationships to Connections:
1. An ETL tool. In my case, I used SCRIBE, which made a difference in terms of how I constructed my SQL. I used some SCRIBE functions to do lookups which I otherwise might do in SQL.
2. A SQL statement that will have your ETL tool only process one record for each relationship.
I’ll let you come up with your ETL tool of choice, but help out on number two. The SQL came down to two important components. First, it was ordering the dataset so that both Relationship records are on top of each other, and second coming up with a way to indicate which record was to be processed (or in our case filtered out of the final result set) and which one was not. Let’s take a look at some SQL Management Studio work:
What I’ve shown here is my Common Table Expression (CTE) . The second (not shown) part filters out those records where the “Diff” column is zero (or zed if you are so inclined). I’ve also commented out names of even Relationship Roles as this is just an example. However, you will need to have a Connection Role for each Relationship Role you are trying to recreate. Mine was such a small amount that I did this manually in the front end but this can be done with an ETL tool like SCRIBE if needed. And this would be uncommented in your result set as this would be used in setting up the Connection Role in the Connection record.
Getting back to the two main SQL components, you’ll need to order by the customerrelationshipid in your SQL statement (note you cannot use order by in a CTE) and include what I’ve done with the modulo function. (For a great read on how this is used in SQL, use the help files with Management Studio or bing “SQL modulo”. For a great piece of fiction on this subject, read Cryptonomicon by Neal Stephenson. You are welcome in advance on both.) With the SQL now in place your ETL tool would handle the dirty work of inserting the Connection between records. Once you insert one Connection between records, another one is already created by the application, just like the good old days of Relationships.
Some helpful tips:
1) As noted, set up your Connection Roles first.
2) Check your work before (get expected counts) and after (get actual counts). As a rule of thumb, you should only be inserting with your ETL half the number of total relationships. However, the front end should show the same number of Connections and Relationships. The former will be shown in your SQL statement, and the latter will be shown in an Advanced Find querying the Connections entity.
There is certainly more detail here, but this is the heart of it. Thanks to our own Joel Lindstrom for his assistance on the subject. If you have questions on this or need some assistance on your 2011 upgrade, please give us a call at (864) 250-2170 or email us at [email protected].
Post by: James Diamond,
