Transform Multi-Select / Delimited fields into CRM Many-To-Many Relationships.

In many source systems (such as salesforce.com), the data from multi-select pick-lists is stored in a memo field as a long list of semicolon delimitated values:

ID	Value
1	Option1;Option2;Option4
2	Option1;Option3

When upgrading to Microsoft Dynamics CRM, you may choose to convert those individual fields into N:N (many to many) relationships, but first you’ll need to de-concatenate those values into multiple rows along with the id of the source record.

ID	Value
1	Option1
1	Option2
1	Option4
2	Option1
2	Option3

How To:

The following example code demonstrates converting a semicolon delimited text field on the opportunity record into a list that is then matched to a related entity and any missing many-to-many relationships are identified for creation.

First, I’m expanding the contents of a text field named “new_semicolon_delimited_field” that I imported into an Opportunity record from SFDC along with the opportunity id, into a table variable named “@XReference”.

Next, I join the resulting data with the associated (“Filterednew_associated_list”) entity using the name to match – this gets the GUID of the associated record.

The result of this query is a dataset with the opportunity ID, and the related record ID for each N:N relationship between the records. 

In the last step, I check for the existence of each unique pair in the relationship table (“Filterednew_opportunity_new_associated_list” )that holds the N:N records linking the two entities  and only return the ‘missing’ pairs.  

The results of the script produce the many-to-many pairs needed. To actually create those many-to-many relationships in CRM, it’s just a simple insert using your favorite data integration tool – Scribe Insight, or SQL’s SSIS with either the CozyRoc or the KingswaySoft CRM Adapter for SSIS.

Here’s the SQL needed for the extract:  (and a big shout-out to Pinal Dave @ SQL Authority for some excellent references on stitching the CTE and the XML methods together.)

 
 
/* 

/* Create a Table Variable to hold the needed cross-reference pairs. */

DECLARE    @XReference TABLE

        (

         Entity NVARCHAR(20)

        ,opportunityid UNIQUEIDENTIFIER

        ,delimitedstring NVARCHAR(100)

        ,relatedrecord UNIQUEIDENTIFIER

        );

/* 

Burst the field "New_Semicolon_delimited_field" from the opportunity record 

into individual rows - along with the opportunityid and the entity name for 

clarity. Insert the results into the table variable @XReference. 

*/

WITH  CTE_DelimitedStrings

        AS ( SELECT

               'Associated_List' EntityName

              ,opportunityid

              ,CAST('<i>' 

                + REPLACE(FO.new_semicolon_delimited_field, ';', '</i><i>') 

                + '</i>' AS XML) AS DelimitedString

             FROM

               dbo.FilteredOpportunity AS FO

           )

     INSERT    INTO @XReference

            ( 

             Entity

            ,opportunityid

            ,delimitedstring

            )

            SELECT

               EntityName

              ,opportunityid

              ,x.i.value('.', 'VARCHAR(100)') AS DelimitedString

            FROM

               CTE_DelimitedStrings

            CROSS APPLY DelimitedString.nodes('//i') x ( i );

/* 

Query the resulting table variable and join the now de-delimited strings 

against the related associated_list entity (the delimited values match 

the 'name' field) 

*/

SELECT

   XR.EntityName

  ,XR.opportunityid

  ,XR.delimitedstring

  ,FAL.new_associated_listid relatedrecordid

FROM

   @XReference AS XR

LEFT JOIN dbo.Filterednew_associated_list AS FAL

   ON FAL.new_name = xr.delimitedstring

/* 

Finally - check the Opportunity - Associated List relationship table for 

the existence of the pair. Exclude any pairs that already exist by only  

returning records where there is no match. The results can be inserted  

into CRM via integration job to create the many-to-many relationships   

between the opportunity and the new Associated List entity.  

*/

LEFT JOIN dbo.Filterednew_opportunity_new_associated_list AS FOFL

   ON XR.opportunityid = FOFL.opportunityid

      AND FAL.new_associated_listid = FOFL.new_associated_listid

WHERE

   FOFL.new_opportunity_new_associated_listid IS NULL   

 

Hope that this example helps you out - if it does, or if you have suggestions for improvements - please leave a comment.  :)

-- @ScottSewell

Comments

[Ian Smith]

Looks good Scott,

I was in the process of implementing this very functionality as a proof-of-concept for a prospective client, so this is timely and relevant for me.

Question - upon producing the many-to-many pairs, could you recommend an approach to get that info into CRM Online records with Scribe or SSIS? I imagine there's a way to code for the creation of the needed N:N records programmatically as opposed to using Scribe or SSIS (in the cloud) to create the records...

My application is pulling the comma-delimited values from a web form, so I'm having to process this all on-the-fly as the web form data is processed into CRM (i.e., it's not a data migration from SalesForce and I wasn't planning on having to use Scribe or SSIS).

[Scott Sewell]

Hi Ian -

I have used both Scribe and SSIS for creating the N:N records - it's actually rather simple - it's just another entity in the system, albeit one with no form and effectively 2 fields plus an ID.
(Just be mindful that it does not allow duplicate relationships - which makes sense - so avoid the errors by avoiding attempting to insert dupe N:N records.)

If you're not planning on using SSIS or Scribe, here's an example of creating them programmatically.
http://msdn.microsoft.com/en-us/library/gg509062.aspx#BKMK_CreateNNEntityRelationship

[Saram]

Hi Scott,

I am using Scribe Insight to pull data from salesforce in to a SQL Server DB and I need to do take data from a multi-picklist in Salesforce and put it into a child table in SQL Server. It sounds like your solution would work great, but I am a little confused about where the SQL script would run. I know Scribe lets you run a SQL script on the connection either before or after the job is run. Is this where you would place the script you provided? If you could provide a little more specific info about how you used this script with Scribe, I think it would clear things up for me.

Thanks,
Sara M.

[Scott Sewell]

Sara - In cases where I need to perform transforms on datasets of any size using Scribe, I'll push the some or all of the data from SFDC to an staging table in a local SQL database, then perform the transformation in SQL. Finally, I take the results and upload them into CRM from SQL (via Scribe.) - Scott.

[Saram]

Thanks Scott for the response. That makes a lot of sense. It is not quite the answer I was hoping for, but it does make sense.

--Sara