Dynamics CRM 2011 N:N Relationships

This guide will demonstrate how to create N:N (Many to Many) Relationships in CRM 2011 with Data Synchronisation Studio. (requires Data Sync release 3.0.586 or greater) For this example we will use out of the box entities and show how to relate 1 or more Competitors to Opportunities.

Below shows an existing Opportunity linked to 3 Competitors in CRM we will create a Data Sync project that can create this automatically.

Configure Source Data

First up we need to configure what it is were going to import/synchronise with CRM. To keep things simple we will create a CSV File containing the Target Relationships.

CSV Data

This Data shows we are Mapping 'Opportunity 1' to Competitor 1,2 and 3 and 'Opportunity 2' to Competitor 1 and 2. You will notice that we do not have the Target CRM Guids for the Competitor and Opportunity here so we will use the Data Sync Lookup Function to provide this transformation.

Configure Data Source A

First connect Data Source A to the CSV File that contains the relationships.

Next we define a Lookup for Opportunity -> CRM Opportunity.Name by adding a Lookup to the Opportunity Column linking to CRM Entity Opportunity and setting the Join Target column to the Name of the Opportunity.

Next we define a Lookup for Competitor-> CRM Competitor.Name by adding a Lookup to the Competitor Column linking to CRM Entity Competitor and setting the Join Target column to the Name of the Competitor.

Now we can test the Lookups by creating a Schema Map of OpportunityId and CompetitorId from the Lookup Columns.

Then Check that the Data Looks good.

Configure Data Source B

Connect Data Source B to the new 'Microsoft Dynamics CRM 2011 - N:N' provider and choose the Relationship 'opportunitycompetitors_association'.

Now configure your Schema Map so that you map the Lookup1_OpportunityId to OpportunityId and Lookup2_CompetitorId to CompetitorId and ensure that the Key Column is checked for both columns as were creating a Composite Key based on both values.

Now you can Compare and Sync to update CRM with the Data and when you check CRM you will see the Relationship is now configured.