Diagnosing the behavior of rollup fields in Microsoft Dynamics CRM 2015

The "cumulative" data type (rollup) appeared in Microsoft Dynamics CRM 2015. It allows you to receive aggregated information along the descendant-parent line. Such functionality is undoubtedly in demand in the market, since updating information from “descendants” on the “parent” has always been a technical difficulty, but it has been demanded and demanded now in many business tasks.

A rollup field is defined as a regular field in CRM, but with a formula for calculating the aggregate values ​​of related records. When creating and updating a field, a system task is created that calculates the value of this field on all available records with this field. In the future, the values ​​of this field are updated incrementally every hour using another system task, also created automatically by the CRM core. In this paper, we consider one of the aspects of working with cumulative fields, namely what happens at the time of creating / updating such a field and what may go wrong.

Information about rollup fields can be obtained from the official and not veryinformation. There, however, not everything is described. When a field is created (updated), CRM registers a system job for the initial calculation of the field value in all available records. This task is automatically scheduled to run after 24 hours. As the study of traces showed, updating the field leads to the fact that the value of InitialValueCalculationStatus for this field in the dbo.RollupPropertiesBase table becomes 0. After the bulk update task completes, this value changes to 3. The set of values ​​of this field does not match the set of values ​​of the _State field, available via meta date. The initial state of the “cumulative” field is not accessible through the public API, which makes diagnosing the state of such a field difficult for cloud systems where there is no access to the database.

The initial calculation of the values ​​of the created / updated field is fully controlled within the system. The administrator can check the availability of a system task, stop or change the time of its launch, but there is no way to control its execution except by checking its status. If for some reason the task did not work as it should, but practice has shown that it happens, the InitialValueCalculationStatus field does not become equal to 3. At the same time, the regular (incremental) field update through another system task is performed and demonstrates successful behavior - Success status and no mistakes. This is because the regular update task checks before starting the calculation whether the field was initially calculated (InitialValueCalculationStatus = 3), and if not, it skips this field.
As a solution to the problem, you can use the field definition update either through the CRM interface or through the use of RetrieveAttributeRequest and UpdateAttributeRequest . A simple field preservation causes the InitialValueCalculationStatus value to be reset to zero and the task of mass updating the field values ​​to be created.

The difficulty lies in diagnosing the existence of the problem as such, especially for cloud systems. As a solution to this problem, we can propose checking the latest update dates of the cumulative fields, and if this value is "far" in the past, then take measures. How far depends on specific business processes.
You can get a list of "cumulative" fields through a standard meta-date request:

var request = new RetrieveAllEntitiesRequest()
{
   EntityFilters = Microsoft.Xrm.Sdk.Metadata.EntityFilters.Attributes,
   RetrieveAsIfPublished = true,
};
var response = (RetrieveAllEntitiesResponse)crmService.Execute(request);

With the subsequent extraction of attributes from the received records (EntityMetadata entity):

var rollupAttributes = entity.Attributes.Where(a => a.SourceType == 2);

Now we need to extract the Date attribute. Each rollup field consists of three - the field itself with the formula and type 2 (SourceType == 2), the Date field and the State field. We are interested in the Date field, because it contains the time the field was last updated. Changing the Cumulative fields does not affect the ModifiedOn field.

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

After the list of fields for verification is received, you can form a request for these fields, sorted by the date the field was updated.

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 ec = crmService.RetrieveMultiple(qe);

Having received the list of records, you can process it and decide what to do with rarely updated fields.

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
      }
   }
}

There is a small catch associated with the ability to manually update the value of the “cumulative” field, available to users. In this case, the proposed analysis needs to be complicated - to display statistics on the set of the latest field updates, it is possible to analyze related records, in general, show imagination, depending on the circumstances.