Workaround API Limitations eg CreatedBy and ModifiedBy

9 August 2012

This is a little trick you can use with Data Synchronisation Studio Project Automation Feature to workaround limitations with certain system API's such as Dynamics CRM.

For example you can't directly update the CreatedOn, ModifiedOn fields in Microsoft CRM via the Web Service API. However if you ignore the warnings about modifying the Database directly you can implement a little function at the end of the Sync to update the Database directly.

This example uses a slightly modified version of the Massive SQL Library by Rob Conery to simplify the SQL required and make it really simple.

Essentially at the end of the Synchronisation we run through the change set and update the fields in the Database Directly.

In this example the CreatedOn and ModifiedOn fields are part of the Sync operation as Updating them doesn't error it simply ignores their values this way they are part of the Changed row data.

The Data Source is simply a list of entityids, the CreatedOn Date and the ModifiedOn Date.

Change the model details from Account and Accountid to the correct entityId.

public override void End(ProjectAutomationResult result)
    {
        if ( result.HasChanges && result.Success ) 
        {
            string connection = "Data Source=.;Initial Catalog=SimegoLtd_MSCRM;Integrated Security=True;";    
            
            var model = new Simego.DataSync.Helpers.Massive.DynamicModel(connection, "Account", "AccountId", "System.Data.SqlClient");
            
            foreach(var update in result.CompareResult.Updated) 
            {
                model.Update(
                    new { 
                        CreatedOn = (DateTime)update.SourceRow[1].BeforeColumnValue, 
                        ModifiedOn = (DateTime)update.SourceRow[2].BeforeColumnValue 
                    }, (Guid)update.SourceRow[0].BeforeColumnValue);        
            }
            
        }
    }

This trick could be used with many different systems to workaround limitations in the API. For example the Same trick could be used with SharePoint to update the Database directly this way too.

| |