View All Blog Posts

CRM 2011 - Import Data and Create Relation to Account Entity

10 May 2011

This is an example to show you how to use Dynamic Columns to lookup values in a related CRM entity in order to create a Parent Child relationship as in Sales History to Accounts using Data Synchronisation Studio>.

In this example I created a very basic sales history Excel spread sheet that contains our Internal Account Number and the value.

Data

We need to lookup the Account Number against the Account entity to get the Guid of the Account to sync into the Sales History entity. We use Dynamic Columns to Load the Account Entity, build a Dictionary of Account Numbers against Account ID's and use that to decorate the Source Data with a new Dynamic Column AccountID.

#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

class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    //Category Index
    Dictionary<string, Guid> accountLookup = new Dictionary<string, Guid>();
    
    /// <summary>
    /// Create a Virtual ID column for our Key
    /// </summary>
    public Guid id 
    {
        get 
        {
            return Guid.NewGuid();
        }
    }
    
    /// <summary>
    /// Return the Account ID via the Lookup Dictionary
    /// </summary>
    public object accountid
    {
        get 
        { 
            if ( accountLookup.ContainsKey(this.accountnumber) )
                return accountLookup[this.accountnumber];
            
            return null;
        } 
    }

    public override void Setup()
    {
        //Get a DataTable object of the related List via a new CRM Reader
        MSCrmDataSourceReader reader = new MSCrmDataSourceReader();        
        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"]);
    }
}

So now we can map our source data to our Sales History Entity in CRM.

Data Source A

Schema Map

After we import the Data into CRM we now have Sales History items with the related Account defined correctly.

CRM

Note: In this example I created a virtual ID column as I had no row in my source Excel file, this causes all entries to be deleted and added on each run as the ID changes every time. In reality you would create a key based on multiple columns i.e. Account,Year,Month for example or store a source reference instead.

Posted at 1:31 PM | By Sean Cleaver |

Would you like to try Data Sync and Ouvvi for your Data Integration projects?

or DOWNLOAD NOW

Recent Blogs