May 29, 2012
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
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.
Hope that this example helps you out - if it does, or if you have suggestions for improvements - please leave a comment. :)
TrackBack URL for this entry:
Listed below are links to weblogs that reference Transform Multi-Select / Delimited fields into CRM Many-To-Many Relationships.: