A common bane in many developers lives is the inability to import marketing list members directly using an asynchronous import. Even in Microsoft Dynamics CRM 2011 this is still an issue. If you're reading this you've probably come across this too. The most common solution I've seen (and read) for this is importing one by one. A piece of code I came across recently did exactly this and (a stripped down version...) looked something like the following:
while (true)
{
var record = MarketingListCSVFile.GetNextCSVRecord();
if (record == null)
{
// eof - no more records to process
break;
}
Entity contact = GetContact(record[ContactField]);
AddListMembersListRequest request = new AddListMembersListRequest { ListId = MarketingList.Id, MemberIds = new Guid[] { contact.Id } };
AddListMembersListResponse response = (AddListMembersListResponse)this.Service.Execute(request);
// ...
}
AttachMarketingListToCampaign(MarketingList, MarketingListCSVFile.CampaignActivityCode);
... and so on. In practice this works, but there's a lot wrong with it. Aside from the fact that it's going to be slow due to hitting the server every time for each member, it's going to hammer that server until it get's all those members imported.
A better, and quite possibly the fastest way to get this imported is by using a custom "holding" entity, drive a Dynamic Marketing List off this entity and then convert this Dynamic Marketing List to a static marketing list (if required).
The Holding Entity
Firstly, we need to create an entity that has all the required fields on it that will allow us to drive a query off. The only requirement here is that it has a relationship to the Cotact entity, because a query for a Dynamic Marketing list must return a list of contacts. In my case I needed to link these contacts back to a campaign activity, which involved importing a code to give me the ability to do this. All in all my new custom entity contains the following:
- Contact (Lookup to contact)
- Campaign Activity Code (string)
The query
Next we need to make sure we can drive the correct query off this. Pop open an advanced find and select the contact entity type from the list. My query looked like this:
This will bring back all the contacts required for my marketing list. All good so far.
The import
Next up, how do we kick off an asynchronous import via the code? If you haven't done this before it's worthwhile having a read of the following links first. Once you've digested all, or at least the applicable parts of this you're ready to write your import:
Sample: Import Data Using Complex Data Map
Data Import Entities
You'll notice another problem I ran into when researching this, and it's how everyone has gone dog crazy on early bound objects. I'm not saying they're bad, but I'm not as big a fan as most. Or maybe I'm just a freak for late bound objects. Mainly because it saves me the pain of:
- Making sure everyone has the latest and greatest definitions in their project
- Waiting for some other bloke to create his entity before I can write my "something or other" that relies on just 1 field in that entity...
Several ways around the above, but as you may have gathered by now, my favourite is use late binding ;)
So, we can break an import and what needs to happen down to about 7 basic steps (8 if you want to wait for the import to complete):
- Create an import map
- Create all your column mappings linked to your import map
- Create the import
- Create the import file linked to the import and import map
- Kick off the parse step
- Kick off the transform step
- Kick off the physical import.
An interesting point to note is that you don't have to wait for the parse to complete before kicking off the transform and import. When MS CRM receives these requests will just queue them up until the others have completed.
When you're done you'll end up with code that looks something like this (You'll notice that this doesn't look exactly like the sites I linked above due to late binding):
var importMap = new Entity("importmap");
importMap.Attributes["name"] = "Import Map Name";
importMap.Attributes["source"] = CsvFileName;
importMap.Attributes["description"] = "Import Description...";
importMap.Attributes["entitiesperfile"] = new OptionSetValue(1); // 1 = Single Entity Per File
Guid importMapId = service.Create(importMap);
// Create a column mapping for the contact lookup field.
var contactColumnMapping = new Entity("columnmapping");
contactColumnMapping.Attributes["sourceattributename"] = "Contact";
contactColumnMapping.Attributes["sourceentityname"] = "Contact_1";
contactColumnMapping.Attributes["targetattributename"] = "new_contact";
contactColumnMapping.Attributes["targetentityname"] = "new_marketinglistcontact";
contactColumnMapping.Attributes["importmapid"] = new EntityReference("importmap", importMapId);
contactColumnMapping.Attributes["processcode"] = new OptionSetValue(1); // 1 = Process
Guid contactColumnMappingId = service.Create(contactColumnMapping);
// If you have special codes you may need a lookup mapping for the contact
var contactLookupMapping = new Entity("lookupmapping");
contactLookupMapping.Attributes["columnmappingid"] = new EntityReference("columnmapping", urnColumnMappingId);
contactLookupMapping.Attributes["processcode"] = new OptionSetValue(1); // 1 = Process
contactLookupMapping.Attributes["lookupentityname"] = "contact";
contactLookupMapping.Attributes["lookupattributename"] = "new_code";
contactLookupMapping.Attributes["lookupsourcecode"] = new OptionSetValue(1); // 1 = Source
Guid contactLookupMappingId = service.Create(contactLookupMapping);
// Create a column mapping for the campaign activity code field.
var campaignActivityColumnMapping = new Entity("columnmapping");
campaignActivityColumnMapping.Attributes["sourceattributename"] = "Campaign Activity Code";
campaignActivityColumnMapping.Attributes["sourceentityname"] = "Contact_1";
campaignActivityColumnMapping.Attributes["targetattributename"] = "new_campaignactivityid";
campaignActivityColumnMapping.Attributes["targetentityname"] = "new_marketinglistcontact";
campaignActivityColumnMapping.Attributes["importmapid"] = new EntityReference("importmap", importMapId);
campaignActivityColumnMapping.Attributes["processcode"] = new OptionSetValue(1); // 1 = Process
Guid campaignActivityColumnMappingId = service.Create(campaignActivityColumnMapping);
// Create a column mapping for the name field.
var nameColumnMapping = new Entity("columnmapping");
nameColumnMapping.Attributes["sourceattributename"] = "Name";
nameColumnMapping.Attributes["sourceentityname"] = "Contact_1";
nameColumnMapping.Attributes["targetattributename"] = "new_name";
nameColumnMapping.Attributes["targetentityname"] = "new_marketinglistcontact";
nameColumnMapping.Attributes["importmapid"] = new EntityReference("importmap", importMapId);
nameColumnMapping.Attributes["processcode"] = new OptionSetValue(1); // 1 = Process
Guid nameColumnMappingId = service.Create(nameColumnMapping);
// Create Import
var import = new Entity("import");
import.Attributes["modecode"] = new OptionSetValue(0);
import.Attributes["name"] = "Importing data";
Guid importId = service.Create(import);
// Create the actual file...
var file = new Entity("importfile");
file.Attributes["content"] = File.ReadAllText(CsvFileName);
file.Attributes["name"] = CsvFileName;
file.Attributes["isfirstrowheader"] = true;
file.Attributes["source"] = CsvFileLocation;
file.Attributes["sourceentityname"] = "Contact_1";
file.Attributes["importmapid"] = new EntityReference("importmap", importMapId);
file.Attributes["importid"] = new EntityReference("import", importId);
file.Attributes["targetentityname"] = "new_marketinglistcontact";
file.Attributes["size"] = ((string)file.Attributes["content"]).Length.ToString();
file.Attributes["fielddelimitercode"] = new OptionSetValue(2); // 2 = Comma
file.Attributes["datadelimitercode"] = new OptionSetValue(1); // 1 = Double Quote
file.Attributes["processcode"] = new OptionSetValue(1); // 1 = Process
file.Attributes["usesystemmap"] = true;
Guid fileId = service.Create(file);
var parseRequest = new ParseImportRequest { ImportId = importId };
service.Execute(parseRequest);
var transRequest = new TransformImportRequest { ImportId = importId };
service.Execute(transRequest);
// Assign the request the id of the import we want to begin
var request = new ImportRecordsImportRequest { ImportId = importId };
var response = (ImportRecordsImportResponse)service.Execute(request);
You most likely won't end up with all that code in 1 place like this, or at least I hope not! But that's the general gist of what needs to happen.
Dynamic Marketing Lists
So what the above gives you is a very quick way to get the data imported into MS Dynamics CRM. But how do we use this? Let's grab that fetch xml from the earlier query and inject our campaign code into that:
string fetchXml = string.Format(
"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>" +
" <entity name='contact'>" +
" <attribute name='fullname' />" +
" <attribute name='contactid' />" +
" <order attribute='fullname' descending='false' />" +
" <link-entity name='new_marketinglistcontact' from='new_contact' to='contactid' alias='aa'>" +
" <filter type='and'>" +
" <condition attribute='new_campaignactivitycode' operator='eq' value='{0}' />" +
" </filter>" +
" </link-entity>" +
" </entity>" +
"</fetch>",
campaignActivityCode);
Set up a new Marketing List, pop that into the "query" field of a Marketing list, and set the "type" to dynamic (1) and off we go.
Converting from a Dynamic to a Static Marketing List
Final step is to convert this to a Static Marketing List. This is easily achievable using the "CopyDynamicListToStaticRequest":
var copyDynamicListToStaticRequest = new CopyDynamicListToStaticRequest { ListId = ml.Id };
var response = (CopyDynamicListToStaticResponse)ml.Service.Execute(copyDynamicListToStaticRequest);
staticMarketingList = new MarketingList(ml.Service, response.StaticListId)
{ Name = MarketingListCSVFile.MarketingListName, Locked = false };
Job Done.
I'd like to take this opportunity to thank that Hetfield dude for being frikken awesome and passing on the awesome. More specifically, his idea of using a dynamic marketing list.
Word.
