Linking Contact to Account in Dynamics CRM 2011 Online

12 August 2011

Update: This feature has been made code free see this tutorial.

If you want to link related records in Dynamics CRM 2011 Online with Data Synchronisation Studio you should create a Dynamic Column on the Source Data to return the ID of the related record.

For example to link Contacts to Account you need to lookup the AccountID from the Account entity to get the internal CRM ID for the Account.

Below is screenshot from CRM showing that my record relates to the Customer "Simego Ltd"

CRM

What we need to do is load a List of Account ID's against the Account Number in the Setup method of the Dynamic Column Code like below. The code creates an instance of a CRMDataSourceReader and configures it to return the ID and Account Number from the CRM Server.

//Get a DataTable object of the related List via a new CRM Reader
MSCrmDataSourceReader reader = new MSCrmDataSourceReader();        
reader.Username = ((MSCrmDataSourceReader)this.DataSourceB.Reader).Username;
reader.Password = ((MSCrmDataSourceReader)this.DataSourceB.Reader).Password;
reader.CrmServerUrl = ((MSCrmDataSourceReader)this.DataSourceB.Reader).CrmServerUrl;
reader.Organisation = ((MSCrmDataSourceReader)this.DataSourceB.Reader).Organisation;
reader.Entity = "account";
//Create a Schema Map to return the ID and account number
reader.SchemaMap = new DataSchema();
reader.SchemaMap.Map.Add(new DataSchemaItem("accountid", typeof(Guid), true, false, false, -1));
reader.SchemaMap.Map.Add(new DataSchemaItem("accountnumber", typeof(string), false, false, false, -1));
//Load the Data Table
DataTable accounts = reader.GetDataTable();        
//Create a Lookup Index to help speed-up lookups
foreach(DataRow r in accounts.Rows) 
    accountLookup.Add((string)r["accountnumber"], (Guid)r["accountid"]);

Then we create a Dynamic Column that looks into the created Dictionary for an Account ID against the Account Number we have in the source data.

/// <summary>
/// Return the Account ID via the Lookup Dictionary
/// </summary>
public object accountid
{
    get 
    { 
        if ( accountLookup.ContainsKey(this.OrganisationID.ToString()) )
            return accountLookup[this.OrganisationID.ToString()];
        
        return null;
    } 
}

When we preview this data you see that we get a Guid for the "accountid" field which is what we need to synchronise into the Parent Customer column.

Preview

The Schema Map might look something like this where the accountid links to the parentcustomerid and we store the ID of the contact from the source system into the externaluseridentifier field.

Schema

Complete Dynamic Column Code

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using Simego.DataSync;
using Simego.DataSync.Providers.MSCrm2011;
#endregion

/// <summary>
/// An object that defines the Dynaimc columns for this project.
/// To Create new Dynamic Columns simply expose them as properties in this class.
/// </summary>
class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    //Category Index
    Dictionary<string, Guid> accountLookup = new Dictionary<string, Guid>();
    
    public string accountname { 
        get { 
            
            if ( !string.IsNullOrEmpty(Name) )
                return Name;
            
            return EmailAddress; 
        } 
    }
    
    public string firstname { 
        get { 
            
            string [] parts = Name.Split(' ');
            if ( parts.Length > 0 )
                return parts[0].Trim();
            
            return Name; 
        } 
    }
    
    public string lastname { 
        get { 
            
            string [] parts = Name.Split(' ');
            if ( parts.Length > 1 )
                return parts[1].Trim();
            
            return ""; 
        } 
    }
    
    
    /// <summary>
    /// Return the Account ID via the Lookup Dictionary
    /// </summary>
    public object accountid
    {
        get 
        { 
            if ( accountLookup.ContainsKey(this.OrganisationID.ToString()) )
                return accountLookup[this.OrganisationID.ToString()];
            
            return null;
        } 
    }

    public override void Setup()
    {
        //Get a DataTable object of the related List via a new CRM Reader
        MSCrmDataSourceReader reader = new MSCrmDataSourceReader();        
        reader.Username = ((MSCrmDataSourceReader)this.DataSourceB.Reader).Username;
        reader.Password = ((MSCrmDataSourceReader)this.DataSourceB.Reader).Password;
        reader.CrmServerUrl = ((MSCrmDataSourceReader)this.DataSourceB.Reader).CrmServerUrl;
        reader.Organisation = ((MSCrmDataSourceReader)this.DataSourceB.Reader).Organisation;
        reader.Entity = "account";
        //Create a Schema Map to return the ID and account number
        reader.SchemaMap = new DataSchema();
        reader.SchemaMap.Map.Add(new DataSchemaItem("accountid", typeof(Guid), true, false, false, -1));
        reader.SchemaMap.Map.Add(new DataSchemaItem("accountnumber", typeof(string), false, false, false, -1));
        //Load the Data Table
        DataTable accounts = reader.GetDataTable();        
        //Create a Lookup Index to help speed-up lookups
        foreach(DataRow r in accounts.Rows) 
            accountLookup.Add((string)r["accountnumber"], (Guid)r["accountid"]);
    }

}

You can download and use a fully featured version of Data Sync Studio for 15 Days to see how this CRM solution works. Click here to go to the Downloads

| |