Connecting to and Using Dynamics N:N Relationships

N:N (or many to many) relationships can be created and managed in Data Sync.

Data Sync contains a specific provider for Dynamics 365 N:N connections, with two ways to connect to the provider:

1. Through the connection window.
2. Drag and drop from the connection tree view onto the data source.

Connecting via the Connection Window

To start, open the connection window and expand the Microsoft Dynamics folder. Then select the Microsoft Dynamics 365 - N:N provider option.

You will need to enter the URL to your Dynamics site into the URL field and Data Sync will automatically format this to the Organisation Service SOAP\WCF service endpoint. So you can just copy and paste the URL from your site into the field, e.g. https://<domain>.crm4.dynamics.com.

Select the authorisation type needed for your site, in this example we use the default Simego OAuth option, and then click Authorise Connection to sign in to Dynamics.

Once the connection is authorised select the relationship role and the relationship to connect to. Then click Connect to load this into the datasource window.

If you have not already saved the connection to this Dynamics site you can save it now by selecting Connect & Create Library Connection instead.

Connecting via the Connection Tree

The preferred and easiest way to connect to Dynamics CRM is to drag and drop the entity from the connections tree onto the data source, and then define the relationship in the properties field.

You can access the relationships by expanding your Dynamics connection and then opening the Relationships folder. You then need to highlight and drag the relationship to the source window.

Creating N:N Relationships

The below example will walk through how to relate one or more Competitors to Opportunities using out of the box entities.

The below image shows an existing opportunity that is linked to three competitors in CRM.

To have this created automatically we will firstly need to configure the source data (what we wish to import/synchronise with CRM).

Configuring & Connecting to the Source Data

In this example we will create a file containing the target relationships defined below.

Source Data Example

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

The above shows that we are mapping Opportunity 1 to Competitor 1, 2 and 3, and Opportunity 2 to Competitor 1 and 2.

You may notice that we do not have Target CRM Guids for competitor and opportunity, to overcome this we will use the Data Sync LOOKUP function to provide the transformation.

Defining the Lookups

For opportunity we will define a lookup linking Opportunity to the Dynamics entity Opportunity. Drag and drop the Opportunity entity from the connection tree onto the Opportunity column in Data Source A. In the Lookup Connection window alter the Lookup Target Column to be name. This will match the values based upon the opportunity name.

For competitor we will define a lookup linking the Competitor column to the Dynamics entity Competitor. Drag and drop the Competitor entity from the connection tree onto the Competitor column in Data Source A. In the Lookup Connection window alter the Lookup Target Column to be name. This will match the values based upon the competitor name.

To check the lookups are functional, add Lookup1_opportunityid and Lookup2_competitorid to the schema map, and then preview Data Source A to check that the data appears as it should. You should see guid values being returned for both ID columns.

Configuring the Target Data Source

We now need to connect to Dynamics using the N:N provider. You can either use the connector window or you can use the connection library and drag the opportunitycompetitors_association relationship into the target window. Alternatively you can right click onto it and select Connect to Target (B).

Configure the Schema Map so that Lookup1_opportunityid is mapped to opportunityid, and Lookup2_competitorid is mapped to competitorid. Check the key column box for both columns, as we are creating a composite key based on both values.

Compare and Sync

To finish click Run Compare to preview the changes and then click Synchronise to update Dynamics with the data. The relationship will now be configured and visible in Dynamics.

Properties

Property	Description
CrmAuthenticationProvider	Dynamics 365 authentication provider scheme to use.
OrganisationServiceUrl	URL to your Dynamics 365 Organisation Service SOAP\WCF service.
DateTimeHandling	DataTime handling: Utc or local.
EntityID	Entity id column name.
FetchXMLFilterExpression	Filter expression to add to the generated FetchXML to filter the records.
PageSize	Number of records to return from CRM in each request, maximum 5000.
Relationship	The relationship name
RelationshipRole	The relationship role type.
Timeout	Service HTTP Request Timeout.
UpdateBatchSize	Number of updates to send to server in each request.