Diagnosing the behavior of "cumulative" (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 on the market, since updating the information from the “descendants” on the “parent” has always represented a technical complexity, but it has been in demand and in demand now in many business tasks.

A rollup field is defined as a regular field in CRM, but with a formula for calculating the aggregated values ​​of the 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, which is also automatically generated by the CRM core. In this paper, we consider one of the aspects of working with cumulative fields, namely what happens at the moment of creation / update of such a field and what can go wrong.

Information about rollup fields can be found in the official and not so much information. 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 start in 24 hours. As the study of traces showed, updating the field leads to the fact that the InitialValueCalculationStatus value for this field in the dbo.RollupPropertiesBase table becomes 0. After the mass update task has completed, this value changes to 3. The set of values ​​for this field does not match the set of _State field values, available via meta date. The initial state of the “accumulative” field is not available 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 completely controlled within the system. The administrator can check the presence of the system task, stop or change the time it starts, but there is no possibility to control its execution except by checking its status. If for some reason the task did not work as it should, but this, as practice has shown, happens, the InitialValueCalculationStatus field does not become 3. At the same time, a regular (incremental) field update through another system task is performed and demonstrates successful behavior - status Success and no mistakes. This is because the regular update task checks whether the initial field calculation (InitialValueCalculationStatus = 3) was performed before starting the calculations, and if not, then skips this field. Thus, auto-updated fields may appear in the system, which are not updated and show outdated information.
As a solution, you can use the update field definition either through the CRM interface or through the use of RetrieveAttributeRequest and UpdateAttributeRequest . Simple re-saving of the field causes resetting the InitialValueCalculationStatus value and creating the task of mass updating field values.
')
The difficulty lies in diagnosing the existence of the problem as such, especially for cloud systems. As a solution to this problem, we can suggest checking the latest update dates of the cumulative fields, and if this value is “far” in the past, then take action. 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 of the last field update. Modifying the “Cumulative” fields do 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 has been received, you can form a query 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 trick associated with the ability to manually update the value of the “cumulative” field, accessible to users. In this case, the proposed analysis should be complicated - to display statistics on the set of recent field updates, it is possible to analyze related records, in general, show imagination depending on the circumstances.