Dynamics CRM Campaign Rollup

10 June 2015

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.

Data Preview

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

Data Set

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

| |