Dynamics CRM 2011 N:N Relationships

31 August 2012

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.

Opportunity

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

Opportunity Competitor
Opportunity 1 Competitor 1
Opportunity 1 Competitor 2
Opportunity 1 Competitor 3
Opportunity 2 Competitor 1
Opportunity 2 Competitor 2

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.

Data Source A

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.

LookUp

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.

LookUp

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

Schema

Then Check that the Data Looks good.

Preview

Configure Data Source B

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

Connect

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.

Schema

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.

Opportunity

| |