Background
One of our customers sends out monthly information together with an invoice to a subset of their contacts. These contacts are first added to a marketing list, and the contacts' names, address information and OCR number is then
extracted. When exporting these members to an Excel file (so it can be sent for printing), our customer has had massive problems to even generate the file. Once we succeeded, we realized that the generated Excel file
is about 550 MB in size, for about 40k rows and less than 10 columns of name and address information. After a while, we figured out that Dynamics CRM generated an additional 160 hidden columns, containing no data. Deleting
these columns brought the file size down to a more reasonable 4 MB. These columns alternated between being named "processid" and "processts".
This problem does not occur when exporting e.g. invoices from advanced find, so I'm curious to know if Dynamics CRM does something special because we're running a plugin at export time in this case.
Details
The flow our customer uses, in more detail, is the following:
The PostContactRetrieveMultiple plugin
protected override void Execute(PluginVars variables)
{
if (variables.Context.InputParameters.Contains("Query") && variables.Context.InputParameters["Query"] is QueryExpression)
{
QueryExpression objQueryExpression = (QueryExpression) variables.Context.InputParameters["Query"];
//Generate and fill the ocr number field when requested
if (objQueryExpression.EntityName == Contact.EntityLogicalName && objQueryExpression.ColumnSet.Columns.Contains("company_generatedocr"))
{
if (objQueryExpression.LinkEntities.Count > 0 && objQueryExpression.LinkEntities.Count(le => le.LinkToEntityName == ListMember.EntityLogicalName) > 0)
{
var contacts = ((EntityCollection)variables.Context.OutputParameters["BusinessEntityCollection"]);
Guid relatedListGuid = (Guid)objQueryExpression.LinkEntities.First(le => le.LinkToEntityName == ListMember.EntityLogicalName).LinkCriteria.Conditions[0].Values[0];
CampaignExtensions.GenerateOcrNumbersForCollection(contacts, relatedListGuid, variables.Dao);
}
}
}
}
The CampaignExtensions (snipped irrelevant code)
The OCREngine used below is our tool for creating OCR numbers based on sequence numbers for e.g. contacts and campaigns, as well as interpreting the created numbers when the corresponding invoice is paid.
public static void GenerateOcrNumbersForCollection(EntityCollection entityCollection, Guid marketingListId, DataAccess dao)
{
var campaignSequenceNumber = GetCampaignSequenceNumberFromList(marketingListId, dao);
foreach (var entity in entityCollection.Entities)
{
string sequenceNumber = entity.GetSequenceValue(dao); //Get the sequence number for the contact
var spec = new OCRSpecification();
spec.DonorNumber = sequenceNumber;
spec.CampaignNumber = campaignSequenceNumber;
entity.Attributes.Add("company_generatedocr", OCREngine.GenerateOCR(spec));
}
}
The result
When exporting members for a marketing list with only one member, the file contains only one set of "processid" and "processts" columns (see below). When exporting 40 000 members, it contains 80 sets of these columns. These columns are not present in the view in Dynamics CRM.
Complete Name Generated OCR processid processts
Henric Fröberg 800004450000165
My questions
We're using Dynamics CRM 2016 on-premise, rollup 1 (8.1.0.359), but we've had difficulties exporting Excel files for marketing lists before installing rollup 1.
As far as we can see, there's is something inherent in the platform that adds these extra columns. We've tried exported members from a marketing list with only columns from the default solution, and the processid and processts columns are still added. We were, however, able to get around this, by observing two facts.
Observations
processid and processts columns added are inversely proportional to the PageInfo.Count property of the query.Actions
Hence, the following modifications to the query seems to be working so far:
Code
This was done by adding a PreContactRetrieveMultiple plugin, which modifies the query accordingly. The plugin basically looks like this:
public class PreContactRetrieveMultiple : CrmPlugin
{
protected override void Execute(PluginVars variables)
{
if (variables.Context.InputParameters.Contains("Query") && variables.Context.InputParameters["Query"] is QueryExpression)
{
QueryExpression objQueryExpression = (QueryExpression) variables.Context.InputParameters["Query"];
var processQuery = query.LinkEntities.FirstOrDefault(le => le.LinkFromAttributeName == "processid");
if (processQuery != null)
{
query.LinkEntities.Remove(processQuery);
}
query.ColumnSet = new ColumnSet(query.ColumnSet.Columns.Distinct().ToArray());
query.ColumnSet.Columns.Remove("processid");
query.ColumnSet.Columns.Remove("processts");
query.PageInfo.Count = 5000;
}
}
}
Result
As the PageInfo.Count has been increased, it means that the view will load these 5000 records at once (instead of the regular 50/100/250). As a result, loading the page often takes some time and the "Unresponsive page" warning often pops up. But by waiting a little bit, the view will load. When exporting, the processid and processts will still show up in the file, but in much smaller numbers as PageInfo.Count has been increased. The file size is now much reduced and more reasonable for the amount of data it contains.
Answers to the questions