N:N Relationships
N:N (or many to many) relationships can be created and managed in Data Sync.
Connection
Data Sync contains a specific provider for Dynamics 365 N:N connections, with two ways to connect to the provider:
- Through the connection window.
- Drag and drop from the connection tree view onto the data source.
Connection Window
A previously specified Dynamics 365 Connection will automatically pre populate the majority of fields. To complete the connection enter any required credentials for your connection and define the relationship.
Choose 'Connect & Create Library Connection' to save the connection and then select 'Connect'.
Connection Tree
The preferred way to connect to Dynamics CRM is to drag and drop the entity from the connections tree onto the data source, and define the relationship in the properties field.
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 the Source Data
In this example we will create a CSV file containing the target relationships defined below.
CSV Data
| 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 Lookups
For opportunity we will define a lookup linking Opportunity to the CRM entity Opportunity. Drag and drop the CRM entity Opportunity from the connections tree onto the Opportunity column in Data Source A. In the Lookup Connection window alter the 'Lookup Target Column' to name.
For competitor we will define a lookup linking Competitor to the CRM entity Competitor. Drag and drop the CRM entity Competitor from the connections tree onto the Competitor column in Data Source A. In the Lookup Connection window alter the 'Lookup Target Column' to 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.
Configuring the Target Data Source
Select Connect Data Source on the target side (data source B) and connect to Dynamics CRM using the 'Microsoft Dynamics CRM N:N' provider. Set the relationship to 'opportunitycompetitors_association".
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
Compare A to B and then Sync to update CRM with the data. The relationship will now be configured in Dynamics CRM.
Properties
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
CRM entity id column name.
FetchXMLFilterExpression
Filter expression to apply to FetchXML expression.
PageSize
Number of records to return in each request, maximum 5000.
Relationship
CRM relationship name.
RelationshipRole
CRM relationship role type.
Timeout
CRM Service HTTP Request Timeout.
UpdateBatchSize
Number of updates to send to server in each request.