NEIL PARKHURST

As I prepare for my MB2-716 exam I’m producing a series of blog posts that collectively should help others revising for the MB2-716 Certification. (Microsoft Dynamics 365 customization and Configuration.) This time I will look at rollup fields.

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. Below you can see that I have added a policies sub-grid to my account form.

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 count, 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.

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.

Tip: Notice the yellow warning which mentions the mass calculate system job.

Tip: Also notice that you can  use the entity hierarchy when creating rollup fields. I explain this here.

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.

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.

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

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 roll up fields and covered all of the points you need to learn for the MB2-716 exam.
