Team Blog

Keep up to date with everything going on at Simego.


10 June 2015

Dynamics CRM Campaign Rollup

This is an interesting use-case for Data Sync to be able to Rollup Contacts linked to Campaigns via Marketing Lists to be used with Marketo.

 

The idea being we need to add a custom text field to the Contact Record that will contain a list of Campaigns that the Contact is assigned to.

 

i.e. Campaign 1|Campaign 2

 

This involves a bit of FetchXML and C# code in Dynamic Columns to bring together.

 

First the FetchXML to Find all the marketing Lists and Campaigns that the Contact is related to.

 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="list">
    <attribute name="listname" />
    <attribute name="type" />
    <attribute name="createdfromcode" />
    <attribute name="lastusedon" />
    <attribute name="purpose" />
    <attribute name="listid" />
    <order attribute="listname" descending="true" />
    <link-entity name="campaignitem" from="entityid" to="listid" visible="false" intersect="true">
      <link-entity name="campaign" from="campaignid" to="campaignid" alias="ab">
        <attribute name="name" />
      </link-entity>
    </link-entity>
    <link-entity name="listmember" from="listid" to="listid" visible="false" intersect="true">
      <link-entity name="contact" from="contactid" to="entityid" alias="ac">
        <attribute name="contactid" />
        <attribute name="emailaddress1" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

You can use this with the FetchXML Data Sync Provider to return this data.

 

This provides the data in rows as normal we now need to roll this up to create the string with the Campaigns concatenated.

 

image 

 

This Dynamic Column code, first reads each row storing it in a Dictionary against Email Address and using a Trick to throw away each row it sees. Then during the End() method we iterate over the Dictionary returning new Rows from the Data we collected from the FetchXML result.

 

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;

using Simego.DataSync;
#endregion

/// <summary>
/// This module of code allows you to define some columns in code or Dynamic Columns
/// To Create new Dynamic Columns simply expose them as properties in this class.
/// </summary>
partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    private Dictionary<string, CampaignRollup> campaignMembers = new Dictionary<string, CampaignRollup>(StringComparer.OrdinalIgnoreCase);
    
    private bool result = false;
    private CampaignRollup _current = null;
        
    public Guid? ContactID { get { return _current != null ? _current.ContactID : default(Guid); } }
    public string EmailAddress { get { return _current != null ? _current.EmailAddress : null; } }
    public string Campaigns { get { return _current != null ? string.Join("|", _current.Campaigns) : null; } }
                
    class CampaignRollup
    {
        public Guid ContactID { get; set;}
        public string EmailAddress { get; set; }
        public List<string> Campaigns { get; set; }
        
        public CampaignRollup()
        {
            Campaigns = new List<string>();    
        }
    }
        
    public override bool BeginRow()
    {        
        if(result == false) 
        {
            if(campaignMembers.ContainsKey(acemailaddress1))
            {
                var cr = campaignMembers[acemailaddress1];
                cr.Campaigns.Add(abname);
                cr.Campaigns.Sort();
            }
            else 
            {
                var cr = new CampaignRollup { ContactID = accontactid, EmailAddress = acemailaddress1 };
                cr.Campaigns.Add(abname);
                
                campaignMembers.Add(acemailaddress1, cr);
            }
        }
        
        return result; // return false to skip row from results.
    }
    
    public override void End()
    {
        result = true;
        
        foreach(var key in campaignMembers.Keys)
        {
            _current = campaignMembers[key];            
            Table.Rows.Add(Table.NewRow());
        }
        
        _current = null;
    }
}


 

This then provides us with a Data Set like this

 

image

 

Which can be easily synchronised back to the Contacts Entity updating a Field so that is contains the Campaigns.


13 May 2015

Values Store Service and Incremental Sync

Requires Data Sync Release : 3.0.912+

 

Data sync has had incremental Sync mode for sometime all this requires is that you set Incremental mode and return a subset of Data from your Source that you want to compare and Sync with your target.

 

The trick is how to easily update the filter condition on your source to automatically move on each time you sync. With Ouvvi you can use the project properties and access the last sync time, however outside of Ouvvi it’s a little trickier.

 

Introducing the Values Store Service this is a small Web Service hosted on the Web (you will need internet access for this) that can be used to Store, Retrieve and Update simple string values. This Service is a simple REST service and we have created a nice wrapper in Data Sync for you.

 

image

 

In order to implement the Incremental Sync with the Values Store service you would use Project Automation to get a value, update the source filter, synchronise and at the end update the value.

 

This is quite simple via the ValueStoreService object that exposes 2 methods GetValue and SetValue.

 

The API requires a guid ID value and a string Key value combined these must be unique.

 

The code below is an example of how you might use this, in Start() we get a Value from the Store if this is the first time i.e. there is no value in the store we return a default value of Today-1 Year.

 

We then use this value with a Helper Function GetFetchFilterXmlForModifiedSince which will return the CRM Fetch XML for us based on a DateTime value. We update the CRM fetch XML Filter property on the source with this and the Compare process starts.

 

After the Sync has completed and everything was ok we then update the Store Value with the Time and Date when the process started so that the filter moves on. At this point if you compare again you will see zero results since there have been no changes on the source and the source returns no records.

 

class ProjectAutomationOverride : Simego.DataSync.Automation.ProjectAutomationShim
{    
    private string AppID = "{903A4B0A-586D-4666-95F4-EB858DBEF1F3}";
    private string AppKey = "AccountsSync1";
private DateTime SinceValue; private DateTime StartValue = DateTime.UtcNow; public override void Start() { SinceValue = ValueStoreService.GetValue(AppID, AppKey, DateTime.Today.AddYears(-1)); DataSourceA.FetchXmlFilterExpression = DataSourceA.GetFetchFilterXmlForModifiedSince(SinceValue); } public override void End(ProjectAutomationResult result) { if(result.Success && result.HasChanges) { ValueStoreService.SetValue(AppID, AppKey, StartValue); } } }

 

If you want to host your own version of the Values Store Service contact us for the details and assistance in installing the service and re-directing the Data Sync ValueStoreService for your version.


8 May 2015

Update To Excel Data Sync Provider

Requires Data Sync Release : 3.0.908+

 

Previously the Excel Data Sync provider was based on the Microsoft Excel Driver for OleDB this was Jet for XLS under 32 bit and ACE12 for XLSX or 64 bit. This had many issues the main issue being 32 and 64 bit support problems. This accounted for a good proportion of the HelpDesk Tickets.

 

Now we have a new provider for Excel the OpenXML Excel Provider and as the name suggests this is based on the OpenXML SDK.

 

image

 

This provider also supports DELETE Operations which is nice, not much more to say really simply connect to an Excel Sheet and go.


8 May 2015

Data Sync Automation Item Events

Requires Data Sync Release : 3.0.908+

 

We’ve been busy adding a new feature to Data Sync Project Automation that allows you to execute your own C# .NET Code during the synchronisation. This new feature is currently still in development build 3.0.908+ but available today in the latest beta build for you to try.

 

The events are as follows:-

  • BeforeAddItem
  • AfterAddItem
  • BeforeUpdateItem
  • AfterUpdateItem
  • BeforeDeleteItem
  • AfterDeleteItem

 

The before events allow you to cancel the operation by setting the Sync property false, you could then provide your own implementation.

 

The after events run after the item is committed and in certain providers will return the items identity in the target system. For example in Dynamics CRM this will return the Entity Guid ID value. In SQL Server the identity will return the value of SCOPE_IDENTITY on Insert.

 

We have also added some helper functions, for example the SQL Server provider now has ExecuteNonQuery and ExecuteScalar functions. This allows you to easily write back to the source record.

 

For example setting a field called LastSync to the current time.

 

public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
{
    DataSourceA.ExecuteNonQuery("UPDATE Northwind2 SET LastSync=? WHERE ProductID=?", DateTime.Now, item.Key);
}

This can actually be simplified via the UpdateSourceRow method

 

public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
{
    DataSourceA.UpdateSourceRow("LastSync", DateTime.Now, item);
}

 

Not all providers support the automation events yet the providers with support for Automation Item events are.

 

  • SQL Server
  • OleDB Database
  • Odbc Database
  • Dynamics CRM 2011-2015 Entity Items (Batch off i.e. Batch=1 and Threads=1)
  • Podio Items, Members, Contacts

11 December 2014

Export Active Directory User Group Membership

In this example we show how you can use Data Sync to create a Data Set from your Active Directory that returns all the Groups that your users belong to.

 

The idea is that we want a rows for each user and group like this below.

 

image

 

By default Data Sync will return the Group membership (Member Of) as a Comma separated List Like this Builtin,Remote Desktop Users,Simego Remote Desktop Users,Users

 

We need to use a little Trick in Dynamic Columns to override the Row loading and expand this list into multiple rows for each group copying the values across the rows.

 

Go into Dynamic Columns and use the code below to expand the Member Of column into multiple rows.

 

 

using System;

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    public override bool BeginRow()
    {        
        if(!string.IsNullOrEmpty(MemberOf))
        {
            var parts = MemberOf.Split(',');
            
            if ( parts.Length > 1 )
            {
                foreach(var s in parts)
                {
                    var new_row = Table.NewRow();
                    foreach(var c in Table.Columns) 
                    {
                        if ( c.ColumnName == "Member Of" )
                        {
                            new_row[c.ColumnName] = s;
                        } 
                        else 
                        {
                            new_row[c.ColumnName] = Row[c.ColumnName];    
                        }
                    }
                    
                    Table.Rows.AddWithIdentifier(new_row, ADPath);
                }
                
                return false;
            }
        }
                            
        return true; 
    }    
}

Since the DSID is no longer unique we need to use a combination of 2 columns for the Key we are using DSID and Member Of.

 

image

 

Now when you look at the results you get Rows for each Group and User.

 

image


3 December 2014

Copy Accounts address to Contacts address records

In this example we show how you can use Data Sync to copy the address details from the parent account record to the contact record in Dynamics CRM.

 

Within Dynamics CRM the parentcustomerid field defines the relationship to the parent Account entity record. What we need is to setup Data Sync to pull in the details of the Account record via a Lookup.

 

First setup Data Sync so that the Source and Target are mapped to the same contact entity. Then create a Filter to only return the rows where we have a parentcustomerid value.

 

Setup

 

Add a Filter to exclude rows where the parentcustomerid is NULL i.e.

NOT(ISNULL(parentcustomerid))

 

Filter Options

 

We also need to disable DELETE and ADD actions on the Target since we only want to UPDATE the records. (The filter may exclude rows which would create DELETE actions on the target an alternative is to use the SyncAtoBIncremental mode in Project Settings).

 

Target Actions

 

Next we add a Lookup on parentcustomerid to account.accountid

 

Lookup Configuration

 

Now we just map the Account Lookup Column values to the Contact record.

 

Final Schema Map

 

When we Compare A->B we now get an UPDATE action to copy over the values from the Account record to the Contact Record.

 

Compare Results


2 December 2014

Implementing an Address Verification Web Service

This is an example showing how you can use a 3rd party system with Data Sync to verify or clean up your addresses in your CRM system or Database.

 

The principal idea is go through each address and pass the details to the 3rd party and then mark the record Valid or Invalid and set a Date value for the test to prevent too many API calls. If the service you use returns corrected address data you could then use this to update the address.

 

I am using a basic address validator service from http://www.address-validator.net/index.html this will just return whether the address is valid or not.

 

I will use a simple SQL Table for this example however it would be easy to adapt to Dynamics CRM or SharePoint.

 

CREATE TABLE [dbo].[Addresses] (
    [ID] int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    [UUID] uniqueidentifier DEFAULT(newid()) NOT NULL,
    [FirstName] nvarchar(255) NULL,
    [LastName] nvarchar(255) NULL,
    [Company] nvarchar(255) NULL,
    [Address] nvarchar(255) NULL,
    [City] nvarchar(255) NULL,
    [County] nvarchar(255) NULL,
    [State] nvarchar(255) NULL,
    [ZIP] nvarchar(255) NULL,
    [Country] nvarchar(255) NULL,
    [Phone] nvarchar(255) NULL,
    [Fax] nvarchar(255) NULL,
    [Email] nvarchar(255) NULL,
    [Web] nvarchar(255) NULL,
    [DateLastChecked] datetime NULL,
    [Valid] bit DEFAULT(0) NOT NULL
)

 

There are 2 fields in here that are going to control the process DateLastChecked and Valid, so the DateLastChecked will be updated with the Date we ran the check and Valid will be updated whether the address passed the validation.

 

First Step is to connect the Source and Target in Data Sync to the same Table as we want to update the Table based on the result of the Web Service.

 

image

 

Next we want to drop into the code editor to write the code to call the service and update the values on the source. Go to View->Dynamic Columns and Enable Dynamic Columns.

 

First we write the code to provide us with the Dynamic Columns to map to the Target and a place to call the Web Service. We want to create virtual columns that we update with results from the Web Service. So the code below creates virtual/dynamic columns for the Address fields and calls them My* and we also make a check to ensure we only check addresses that are 90+ days old.

 

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;

using Simego.DataSync;
#endregion

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{    
    public DateTime MyDateLastChecked { get; set; }
    public bool MyValid { get; set; }
        
    public string MyAddress { get; set; }
    public string MyCity { get; set; }
    public string MyCounty { get; set; }
    public string MyState { get; set; }
    public string MyZip { get; set; }
        
    public override bool BeginRow()
    {
        MyAddress = Address ?? string.Empty;
        MyCity = City ?? string.Empty;
        MyCounty = County ?? string.Empty;
        MyState = State ?? string.Empty;
        MyZip = ZIP;
        
        MyDateLastChecked = DateLastChecked ?? DateTime.Parse("2014-01-01");
             
        MyValid = Valid;
        
        //Check Addresses that are 90 Days old.
        if(MyDateLastChecked < DateTime.Today.AddDays(-90))
        {        
            //Check Address here
            
            MyDateLastChecked = DateTime.Today;
        }
        
        return true; // return false to skip row from results.
    }
}


Next we re-configure the Schema Map to map these My* columns to the Target.

 

image

 

Before we write the rest of the code we need to ensure that the .NET 4.0 System.Web.dll assembly is loaded into Data Sync, to do this go to File->Properties and add an Assembly reference for C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll (this path maybe different on your machine).

 

image

 

Now we configure the rest of the code to call the Web Service and set the Valid result.

 

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;

using Simego.DataSync;
#endregion

using System.Web;
using System.Net;
using System.IO;

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    const string APIURL = "http://api.address-validator.net/api/verify";
    const string APIKEY = "my-api-key";
    
    public DateTime MyDateLastChecked { get; set; }
    public bool MyValid { get; set; }
        
    public string MyAddress { get; set; }
    public string MyCity { get; set; }
    public string MyCounty { get; set; }
    public string MyState { get; set; }
    public string MyZip { get; set; }
        
    public override bool BeginRow()
    {
        MyAddress = Address ?? string.Empty;
        MyCity = City ?? string.Empty;
        MyCounty = County ?? string.Empty;
        MyState = State ?? string.Empty;
        MyZip = ZIP;
        
        MyDateLastChecked = DateLastChecked ?? DateTime.Parse("2014-01-01");
             
        MyValid = Valid;
        
        //Check Addresses that are 90 Days old.
        if(MyDateLastChecked < DateTime.Today.AddDays(-90))
        {        
            var postData = string.Format("StreetAddress={0}&AdditionalAddressInfo={1}&City={2}&PostalCode={3}&State={4}&CountryCode={5}&Locale=en&APIKey={6}",                
                HttpUtility.UrlEncode(MyAddress),
                HttpUtility.UrlEncode(""),
                HttpUtility.UrlEncode(MyCity),
                HttpUtility.UrlEncode(MyZip),
                HttpUtility.UrlEncode(MyState),
                HttpUtility.UrlEncode(Country),
                APIKEY);
                        
            var postBytes = System.Text.Encoding.UTF8.GetBytes(postData);
            
            //Do the Address Lookup here...                    
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(APIURL);
            
            request.Method = "POST";
            request.ContentLength = postBytes.Length;
            request.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
            
            using(var requestStream = request.GetRequestStream())
            {
                requestStream.Write(postBytes, 0, postBytes.Length);
            }
            
            using(var response = request.GetResponse())
            {
                if(response != null)
                {
                    using(var responseStream = new StreamReader(response.GetResponseStream()))
                    {
                        var resultString = responseStream.ReadToEnd();
                        Trace.WriteLine(resultString);
                        
                        dynamic results = Json.Decode(resultString);
                        
                        MyValid = results["status"] == "VALID";
                    }
                }                
            }
            
            MyDateLastChecked = DateTime.Today;
        }
        
        return true; // return false to skip row from results.
    }    
}

 

That’s all there is to it, if your service returns an updated address you can then use this result with the My* virtual/dynamic columns to update the Database with the corrected address details.

 

image

 

The same concept can be used to clean-up, fix or decorate your data with data from other systems via Web Service calls or other similar systems.


1 December 2014

Deleting Records from an External List

This is an example of using Data Sync to clean up your data by deleting records you have in a separate list or document.

 

In this example we have an Excel Spreadsheet that contains the Record ID numbers of the records we wish to delete from a SQL Table.

 

image

 

To setup a project that will delete the records listed in this project we need to use a Lookup function with the source and target mapped to the same data source. The idea here is that we want to return from Datasource A only the records we wish to keep.

 

First setup the project with the Source and Target mapped to the same Datasource. This will always do nothing since both sides are equal.

 

image

 

Next add a Lookup to the Key Column (ID) to match the ID values from the Excel Spreadsheet. Set the Data Type to match the Spreadsheet type and Target Column to be ID from the Spreadsheet.

 

image

 

Next add an Expression in the Source Filter to return only those records we wish to keep. i.e. the records that are not listed in the Excel Spreadsheet. We use a test for NULL so if the result is NULL we want to keep the record.

 

The Expression in the case is ISNULL(Lookup1_ID)

 

image

 

Now when we compare A->B the result is a DELETE set that matches the records in the Excel Spreadsheet.

 

image

 

This same procedure can be used with virtually any data source and the Delete list can come from many different sources such as a SQL Query, FetchXML Expression etc.


21 November 2014

Data Sync and Windows 10

Data Synchronisation Studio looking good on Windows 10 Tech Preview.

 

image


3 November 2014

Re-writing File Paths

Requires : Data Sync Release Version 3.0.888 or higher.

 

The Data Sync File System provider now allows for the re-writing of Filenames and Paths which is handy if you want to clean up some directories prior to importing into SharePoint. You can even use it to remove all the directories to create a list of files.

 

Start by configuring a simple project that maps the source folder to a target folder.

 

image

 

This source directory contains a few folders and five documents.

 

image

 

To remove all the folders we simply map the Filename to FullFilename and remove the Path and FileName from the schema map.

 

image

 

This produces a directory containing the files with no directories.

 

image

 

Now imagine we want to use the path with the filename combined we can easily do this with a Calculated Column to create a new filename. For example below we use the FORMAT function to concatenate the Path and Filename to produce a new filename. Mapping this to FullFilename re-writes all the filenames on the target.

 

image

 

Producing the following result.

 

image

 

Then with a dynamic column you could write a function to calculate a path based on when the file was last modified allowing you to group files into directories by date.

 

Here we create a new path for the file based on the last time the file was written to and put then into folders grouped by month.

 

image

 

The schema map is slightly different since we want to map Filename and Path this time.

 

image

 

This time we get folders named as Dates and the files that we’re modified placed under them.

 

image

 

As you can see using the FileSystem provider to clean up your directories prior to importing into SharePoint is now quick and easy with a little bit of code you can almost do anything.