Rollup Fields Diagnostic in Microsoft Dynamics CRM 2015

Introduction

Rollup fields were introduced in Microsoft Dynamics CRM 2015. They are designed to retrieve aggregated information from child records on a parent one.

Rollup field is defined as a usual CRM field with a formula to calculate aggregated values from child records. A system job is created automatically to calculate rollup value on all existing records with this field when the field is created or updated. It is called “Mass Calculate Rollup Field”. Another recurring system job is created by CRM core to calculate changes in this field every hour incrementally. This tip is about what can go wrong when a rollup field is created or updated.

Background

Proper information about rollup fields can be found in official and unofficial sources. Still some aspects are not covered there.

Problem Description

When a rollup field is created or updated together with creation of a “Mass Calculate Rollup Field” system job, the system updates value InitialValueCalculationStatus in dbo.RollupPropertiesBase table with 0 (zero). When the job completes its calculations, this value is changed to 3 (three). InitialValueCalculationStatus has different from <rollup field>_State field set of available values. There are no public APIs to access value of InitialValueCalculationStatus, i.e., it is not available through the meta-data. Thus for cloud-based CRM monitoring of a rollup field initial state is challenging because there is no access to SQL database.

Initial calculation of rollup field values on existing records is fully controlled by the system. A system administrator can check if a “Mass Calculate Rollup Field” system job was created and if it returned “Success” when it is completed. He or she can change time when the job is run, but there not much more that can be done. If the job for some reason did not work properly, the InitialValueCalculationStatus field does not get value 3. Still regular system job that updates the field every hour continues to work correctly and shows success status when it completes. It is due to check that this job performs when it starts. It checks value of InitialValueCalculationStatus column for each rollup field per entity. If the value does not equal 3, the field is skipped without error. Thus some rollup fields start to show outdated information without any errors.

Solution

There is an official technical solution for such an issue. A rollup field definition is to be updated either through UI, or through meta-data requests: RetrieveAttributeRequest and UpdateAttributeRequest. Saving a field definition (no changes have to be made) will cause the system to create “Mass Calculate Rollup Field” system job.

The challenge is in revealing of the problem existence, especially for cloud-based systems. Checking last update date of a rollup field is a possible solution as each rollup field has <rollup field>_Date field with last date of this field calculation. If the date is heavily outdated, than it is the sign of the issue. List of available rollup fields can be retrieved with the help of meta-data request:

var request = new RetrieveAllEntitiesRequest()
{
   EntityFilters = Microsoft.Xrm.Sdk.Metadata.EntityFilters.Attributes,
   RetrieveAsIfPublished = true,
};
var response = (RetrieveAllEntitiesResponse)crmService.Execute(request);
var rollupAttributes = entity.Attributes.Where(a => a.SourceType == 2);

rollupAttributes is an IEnumerable with EntityMetadata records. Rollup field date attribute is a separate attribute and it is to be retrieved separately. This field is really important because changing of rollup field does not affect ModifiedOn field value on a record.

foreach (var rollupAttribute in rollupAttributes)
{
var dateAttribute = entity.Attributes.Where(a => a.LogicalName.ToLower() == 
		rollupAttribute.LogicalName.ToLower() + "_date").FirstOrDefault();
}

With list of attributes on hand, it is possible to create a late binding request to get actual values ordered by date:

var qe = new QueryExpression();

qe.EntityName = entity.LogicalName;
qe.ColumnSet = new ColumnSet();
qe.TopCount = 1;

var order = new OrderExpression();
order.AttributeName = rollupField.DateField.LogicalName;
order.OrderType = OrderType.Descending;

qe.Orders.Clear();
qe.Orders.Add(order);
qe.ColumnSet.Columns.Clear();
qe.ColumnSet.Columns.Add(rollupField.DateField.LogicalName);

var entityCollection = crmService.RetrieveMultiple(qe);

Here, entityCollection is a list of records that is either empty if there are no records of this type, or consists of one top record. Processing logic depends on current business processes:

if (ec.Entities.Any())
{
   var lastUpdateDateRaw = ec.Entities[0].Attributes[rollupField.DateField.LogicalName].ToString();
   DateTime lastUpdateDate;
   if (DateTime.TryParse(lastUpdateDateRaw, out lastUpdateDate))
   {
      if (lastUpdateDate.ToLocalTime() < DateTime.Today.AddDays(-1))
      {
         //Put your logic here
      }
   }
}

Points of Interest

Rollup field values can be refreshed manually through UI or through CRM web service call. <Rollup field>_date value will be refreshed as well in this case. Thus the approach above is to be adjusted to include some kind of statistics on date fields.

Note

This tip is a translation of my original publication.

History

• 11^th October 2015 - Initial release