I am creating a series of blog posts that collectively are designed to help anyone preparing for the Microsoft Dynamics 365 Customer Engagement Core exam. (aka MB-200) In this post I will look at concepts around rollup fields.
You can see below that we have a section of the exam which covers managing entities and data. Within this section needing to know how to create and modify fields is referenced. Whilst this statement doesn’t explicitly reference rollup fields I think it should be a good guess that we don’t need to just know about simple 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. Below you can see that I have added a policies sub-grid to my account form.
You actually creating a similar example as part of your revision will be a very valuable exercise! (For example, In my revision I used the new form design in make.powerapps.com to create my changes and learnt quite a bit along the way!)
Next on my account I created a new currency field that will hold the total policy value. Notice that I have set the field type to “Rollup”.
Having clicked Add you may get a message similar to the one below., This is because the field needs to be saved before you can add the rollup logic.
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 count, max, min and average.) This is important to remember as rollup fields typically work with numeric values. Whereas we saw that calculated values could also work with strings. (The exception to this rule is dates as you can perform some date functions using rollup fields, as we will see later in this post!)
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 rollup into a monthly total and which into an annual total.
Also commonly in the filtering you might want to only include “active” records. As I might not want to include cancelled or expired policies which would be inactive records.
Tip: Notice the yellow warning which mentions the mass calculate system job.
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 (aka calculator icon) forces a refresh and the total changed to reflect the values shown below.
If I had waited for 12 hours the fields would have been refreshed with me manually selecting refresh.
Note: There are some limited capabilities with rollup dates. For example you could find the earliest or latest date from a child entity. (Using max / min functions) To illustrate this I have shown a max date on the screen below.
When you create a new rollup 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 hour to keep them up-to-date.
Calculation of a rollup Field is a recurring job. The administrator can change the timing and frequency of the recurring jobs used to rollup fields.
To change the recurrence of a rollup the administrator needs to find the system job for the calculation, opens it and selects the option to modify the reoccurrence. Below you can see that I have opened systems jobs, changed the view to give me all rollup field calculation jobs. I then use the actions menu to display and edit the recurrence.
Note: Rollup fields are not available on many to many (N:N) relationships.
Note: Calculated fields cannot be include in the rollup calculation.
The rollup 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.|
Some considerations to be aware of are listed below;
- When you manually refresh a field the maximum number of records considered for rollup is 50,000. If you exceed this when rolling up manually an error will be given. However this limit does not apply when the automated system job is used to calculated the rollup value.
- You can define a maximum of 100 rollup fields for the entire organization.
- You can define a maximum of 10 rollup fields per entity.
- A workflow wait condition cannot use a rollup field.
- You cannot rollup a rollup field.
- A rollup cannot reference a calculated field that references another calculated field.
- The rollup can only apply filters to the source entity or related entities simple fields or non-complex calculated fields.
- Rollup fields do not support many to many (N:N) relationships.
- Business rules and workflows always use the last calculated value of the rollup field.
- The rollup happens under the system user context. Meaning all users see the same rollup field value, regardless of security roll.
- If the precision of the aggregated field is greater than the precision of the rollup field, the aggregated field precision is rounded down to the precision of the rollup field, before the aggregation is performed.
- Rollup field aggregation uses only direct relationships explicitly defined in the rollup field definition, no other relationships are considered.
I hope this post has fully explained rollup fields and covered all of the points you need to learn for the MB 200 exam.