Using SmartConnect to Migrate Many to Many Relationships in CRM

As part of a migration from Microsoft CRM on-premises to D365 online using SmartConnect, I needed to migrate a Many to Many (N:N) Relationship entity.  With N:N relationships, a special entity is created called a Relationship (or Intersect) entity.  This entity has a relationship with each of the related entities and only stores the necessary values to define the relationship.  You can’t add custom fields to a relationship entity. 

Migrating this relationship entity requires using a couple special mapping features – advanced messages and additional columns.  In my examples, I am mapping a relationship between Opportunities and a custom entity called “Alliance RFQ Contact.” 

1. Select your relationship entity as the data source.

2. For the destination, you must use "Associate Record" rather than mapping directly to the     relationship entity. Check the "Include advanced messages" checkbox to display the “Associate Record” advanced message.

3. You will map 3 columns, all created using the Additional Columns feature:

1. Create 2 “Entity Reference” columns, one for each of the entities involved in the relationship (in this case, the Opportunity and Alliance RFQ Contact). You need to enter a name for the column, select the entity, and supply the GUID field for that entity from your data source for the map.  Example of an entity reference setup: 
   
   
   
2. Create a Relationship column showing the relationship between the two entities. Enter a name for the column, select the relationship entity as the “Entity” and also select that relationship as the “Reference.”  Example of a relationship column setup:

   
   
   

4. Finally, you can map your columns:

1. Map the first entity reference column to “Target.”
2. Map the second entity reference column to “Related Entities.”
3. Map the relationship column to “Relationship.”
   
   

About the Author

Maureen Franz
Access Programmer
Maureen has been a developer at HBS for 6 years.  During her time here, she has worked with several different technologies and has recently expanded into integrations using SmartConnect with multiple data sources/destinations, including Great Plains, CRM, SAGE, SQL Server, etc.