CRM2015 introduces a new feature called ‘rollup fields’ which has the same objective as our AutoSummary product. However, there are significant architectural and functional differences between rollup fields and AutoSummary. In the following article, we are going to look at the new rollup field functionality and how it compares to AutoSummary.
When a new field is created, a new option is available called ‘Field Type’. Depending on the data type (Has to be Whole Number, Decimal Number, Currency, or Date and Time), the user will be able to select ‘Rollup’. Doing so, will enable an ‘Edit’ button to the left of this field.
By clicking the edit button, the details of the relationship used, filters and aggregation method can be defined. ‘Source’ is always set to the entity on which the field resides. Use Hierarchy enables the selection of the self-referential relationship that is marked as ‘hierarchical’ (there can be only one of these per entity).
The ‘Related Entity’ section allows the selection of a child relationship (note: you cannot select a many-to-many relationship) to use when querying the records. Filters are optional but allow the child records to be refined so in the example above, we are only looking for opportunities with an estimated close date that is after the 1st of Jan 2014. Another limitation here is date fields do not have the same evaluators as advanced find so you cannot use things like ‘this year’, this financial period, last X years, months etc. It is worth noting that AutoSummary makes use of the ‘Rollup Query’ functionality which provides the same functionality as advanced find. This is currently far more powerful than the filters available in the rollup field details.
Finally, the aggregation is specified (count, sum, min, max). The following aggregation options are available for the following field types (note there is no Average aggregation);
Whole Number – SUM, MIN, MAX, COUNT
Decimal Number - SUM, MIN, MAX, COUNT
Currency – SUM, MIN, MAX
Date and Time – MIN, MAX
When added to the form, calculated fields appear with a small calculator symbol to the left and are always disabled.
The rollup fields get updated in two ways, either on a schedule which I will elaborate on below, or by manually clicking the refresh symbol to the right of the field.
When any rollup field is added, a new system job is added with a system job type of ‘Mass Calculate Rollup Field‘. This system job is intended to run only once for all records contained within the entity of the rollup field. If that field happens to be the first rollup field added to the entity, another system job with a type of ‘Calculate Rollup Field’ is added with a system job name of ‘Calculate rollup fields for the <entity name> entity’. This can be seen within Settings > System Jobs then switching the ‘View’ to ‘Recurring System Jobs’. The purpose of this job is to run periodically and re-calculate the rollup fields.
It is important to note that the system job named ‘Calculate rollup fields for the <entity name> entity’ has the ability to be modified (drill in to the record, then click ‘Actions’ > Modify Recurrence).
The calculate rollup fields system job defaults to run every hour (official Microsoft materials indicate this defaults to 12 hours however testing in the beta version seems to default to 1hr) but can be reduced to every X hours or days if performance issues are encountered. Every hour is the most frequent option.
From a performance perspective, this approach is ‘light weight’ as system jobs are not generated each time a child record is added/deleted/amended although the trade-off is the calculations could be up to an hour out of date. Whether this is acceptable is of course dependant on the business scenario.
Rollup field Limitations & Considerations
Rollup fields cannot be used as workflow trigger fields or in wait conditions.
Modified on/Modified by fields do not get updated each time the rollup fields are re-calculated.
Rollup fields cannot be audited.
No more than 100 rollup fields per organisation and 10 per entity may be added.
Child fields cannot also be rollup fields
Child fields if marked as calculated cannot reference fields outside of the entity on which they reside.
Rollup fields cannot traverse N:N (many to many) relationships
When a rollup field is added, two additional fields are added to store the ‘state’ and ‘last calculated’ date.
Adding a rollup field will execute the calculation for all records in the system the first time the system job runs. This should be considered & tested of millions of records exist.
If a hierarchical field is selected for the rollup field, it will calculate to a maximum depth of 10.
AutoSummary Limitations & Considerations
If a new definition is added, existing data will only be updated as child records are added/removed/amended unless a bulk workflow execution is performed across all child records to trigger an update.
AutoSummary uses system jobs just like plugins and synchronous workflows so care must be taken not to overload the sandboxed processing service which has a 2 minute timeout. For on premise deployments, the plugin registration tool can be used to move the AutoSummary plugins to an isolation mode of ‘None’ which removes the 2 minute restriction.
AutoSummary definitions only ‘roll up’ across 1 relationship so for self-referential hierarchical relationships, the values will only be rolled up from the child records (not grand child etc).
Which tool should I use?
Use the new rollup fields IF
A new rollup field is to be introduced and values across hundreds of thousands or millions of records need to be updated.
It is acceptable to update the rollup field on a scheduled basis (1hr or greater).
You need to perform a count of records across several levels of self-referential relationships (greater than 2 but less than 10)
You need to perform regular imports or updates of thousands of child records and need to avoid triggering a system job for each create/update operation.
Use AutoSummary IF
It is important for the rollup field to be updated in real-time (synchronous) or within a few seconds/minutes (asynchronous as per a workflow job) of the child record being added/deleted/changed.
You need more than 10 rollup fields to update on a parent entity.
You need more than 100 rollup fields in your organisation.
You need to populate your rollup field with an average value.
You need to trigger a workflow when the summary field on the parent entity changes.
You need to update existing fields or a field that is of type ‘Floating Point Number’)
You need to have a waiting workflow that evaluates the summary field on the parent entity.
You need to count records across a many-to-many (N:N) relationship.
You have complex filter requirements that can be met with the advanced find editor (i.e. nested AND/ORs and evaluators on related records)
You have filter requirements that use date evaluators such as ‘this year/financial period’, last X months etc.
You need to automatically trigger the rollup calculation onchange of a field on the parent record.
With the introduction of rollup fields in CRM2015, it is fair to say AutoSummary will be less prevalent in our projects, however, it is quite apparent that AutoSummary still has a place in the toolset of our consultants and our customers.