I am creating a series of posts that collectively should help anyone preparing for the CRM 2016 Customization and Configuration exam. (MB2-712)
In earlier posts I have looked at entities, fields and calculated fields. This post will continue that theme by reviewing the concepts connected with rollup fields.
When I reviewed calculated fields I showed how they could take data from the current entity or its parent. This is very useful but often you will have a requirement to work with information from child records. Imagine you have an entity called “Policy” and that is linked to an account. Each account could have multiple policies. It might be that you need to calculate the total value of all the policies for the account. To do that we’d need a rollup field.
As an example I have created a policy entity, given each policy a value and added that to my account. So on an account I can see the policies and their individual values.
Next on my account I create a new field that will hold the total policy value. Notice that I have set the field type to “Rollup”. Having saved the field I can then select the edit button to define the logic to rollup the policy value.
The details for rollup field looked like this.
Notice that I have selected my policies entity as the related entity.
I then said I want a sum of policy value. (Other options available to me are max, min and average.) This is important to remember as roll up fields only work with numeric values. Whereas we saw that calculated values could also work with strings and dates.
In my simple example I left the optional filter section blank. But this can be used to filter record. For example: If some policy values were annual and some were monthly I could decide which to roll up into a monthly total and which into an annual total.
Also commonly in the filtering you might see that we only want to count “active” records. As I might not want to include cancelled or expired policies which would be inactive records.
Once I have saved my new field I can add it to the form. Creating this one field actually created four fields. Two fields are used to hold the policy value. “Total Policy Value” and “Total Policy Value (Base)” this was because I created a currency data type. All currency fields hold a value in the base and local currency. Although it is often true that these values contain the same value. (When the base currency and local currency are the same!)
It also created last updated on and state fields. These fields are needed as rollup values are not updated real-time, meaning that in some circumstances you will want to know when they were last refreshed. And if it was successful.
Having saved and published my change when I first opened my form it looked like this;
Notice the fields were initially blank. But clicking the refresh icon forces a fresh and they changed to reflect the values shown below.
Note: If I had waited for 12 hours the fields would have been refreshed with me manually selecting refresh.
Rollup fields are calculated on demand like this using the refresh button.
When you create a new roll up field a process will run to roll them up 12 hours after the field is created. After that a system job will be fire every 12 hours to keep them up-to-date.
Calculation of a rollup Field is a recurring job, created per rollup field. It runs once, after you created or updated a rollup field. By default, the job will run 12 hours after you created or updated a field. If the field is modified, the job resets to run again in 12 hours after the update. The administrator can change the timing and frequency of the recurring jobs used to roll up fields.
To change the recurrence of a roll up the administrator needs to find the system job for the calculation, opens it and selects the option to modify the reoccurrence.
Rollup fields are not available on many to many (N:N) relationships.
Note: Calculated fields cannot be include in the rollup calculation.
The roll up field has a status; this is general not required but is useful if you get unexpected results. The possible values of the status field are shown below.
|0 => NotCalculated||The field value is yet to be calculated.|
|1 => Calculated||The field value has been calculated per the last update time in _date field.|
|2 => OverflowError||The field value calculation resulted in overflow error.|
|3 => OtherError||The field value calculation failed due to an internal error. The following run of the calculation job will likely fix it.|
|4 => RetryLimitExceeded||The field value calculation failed because the maximum number of retry attempts to calculate the value was exceeded due to high number of concurrency and locking conflicts.|
|5 => HierarchicalRecursionLimitReached||The field value calculation failed because the maximum hierarchy depth limit for the calculation was reached.|
|6 => LoopDetected||The field value calculation failed because a recursive loop was detected in the hierarchy of the record.|
I hope this post has fully explained roll up fields and covered all of the points you need to learn for the MB2-712 exam. J