How to create a rollup field in Dynamics 365

Creating a rollup field in Dynamics 365 can allow a business to monitor various important metrics and to obtain some key data insights. For example, if you want to see how many open sales opportunities are related to an account and how much revenue they could potentially bring, or you want to see how many open cases you have across all your customer service accounts, then a rollup field can provide this data, at a glance. This article will give more details about how they work, and explain how they can be created and edited.

What is a rollup field in Dynamics 365?

A rollup field in Dynamics 365 contains an aggregate value that is computed by counting all the records related to a specific record, or a value that’s calculated over a specific hierarchy.

When you create a rollup field, this creates two accessory fields: <fieldname>_date and <fieldname>_state. Data in the _date field is of the Datetime type (date and time), and data in the _state field is of the integer type (whole number between 1 and 10). If the value for the rollup field has been calculated, then the _date field will show the date and time of its most recent calculation. The number in the _state field indicates one of the following states:

0 - NotCalculated - The field value is yet to be calculated (there will be no date or time in _date field).

‍1 - Calculated - The field value has been calculated (see the last update time in _date field).

‍2 - OverflowError - The field value calculation resulted in an overflow error.

‍3 - OtherError - The field value calculation failed due to an internal error. The following run of the calculation job should fix it.

‍4 - RetryLimitExceeded - The field value calculation failed because the maximum number of retry attempts to calculate the value was exceeded, due to a 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.

Benefits of rollup fields

• There’s a wide selection of functions available to aggregate data, and full filter support for aggregation.
• Seamlessly integrate with the user interface. Rollup fields can be included in reports, forms, views, and charts.
• Complementary with calculated fields. Rollup fields can be used as part of a calculated field, and vice versa.
• They are solution components, and can be easily transported between organizations and distributed in solutions.

What calculations can we obtain?

Calculations for a rollup field in Dynamics 365 are carried out as asynchronous system jobs, working in the background. If you have administrator privileges, you can navigate to System Jobs under Settings, then click View, and go to Recurring System Jobs. Here you’ll see that there are two types of job that relate to rollup calculations: Mass Calculate Rollup Field and Calculate Rollup Field.

Mass Calculate Rollup Field is a job that is created for each rollup field, and it calculates the specified value in every record that contains this field. When the field is either created or updated, the job will run 12 hours later by default, and then again some years in the future. The scheduling is set up so that it will only run during a business’ non-operational hours, and this can be adjusted by anyone with admin privileges.
Calculate Rollup Field is a job that, for all the existing records for a specified entity, carries out incremental calculations for all rollup fields. It has a maximum setting of one hour for how often it will recur. This job incrementally calculates the rollup field values for all records that have been modified since the last Mass Calculate Rollup Field job, and it is created automatically when the first rollup field on an entity is created, then deleted when its last rollup field is deleted.

To modify the recurrence frequency of these jobs at any time, select Actions at the top of the relevant job, and choose the relevant option.

There’s also an option to recalculate the values manually, if you’re online. Within the form for a particular entity, you can click the refresh button next to the rollup field, provided you have Write privileges on the source entity.

How to create rollup fields in Dynamics 365?

Firstly, you’ll need to find your source entity (account, opportunity, etc.) and create a new field within it. Select the relevant Data Type, and then for Field Type, select Rollup from the drop-down menu.

You will have to Save the field to continue the creation process.

Open again the field and select the Open Rollup button.

It will open up an Edit button, and you can click on this to start defining your field.

The three main sections for the definition of a rollup field

Source Entity

Source Entity is set by default to be the entity in which you created the rollup field. In this section you can choose whether to include a hierarchy (parent/child entities) in your rollup field calculation, and you can also apply other filters and conditions.

Associated Entity

Associated Entity allows you to specify which other entity you will aggregate values for. If a hierarchy has been included, then this will be optional. Once the related entity has been selected, other filters can be added, like with the Source Entity.

Aggregation

Aggregation lets you choose which function you will apply to aggregate your data. The options are: SUM, COUNT, MIN, MAX, AVG.

Once editing is complete

Save and Close conditions for the rollup field, then Save and Close the field window. You can now navigate to a form that you want to add the rollup field to, for example, the Account form, and add the new rollup field.

In the form view, you can see the rollup field displaying the specified values, with a calculator symbol to indicate that it’s a rollup field. There should also be a refresh button that allows it to be manually updated. When you hover over the button, it will tell you when the field was last updated.

What to consider when creating a rollup field in Dynamics 365

When you create a rollup field in Dynamics 365, there are a number of restrictions and conditions that apply, and it’s worth being aware of the limits of the feature.

Maximum rollupfields - No more than 100 can be defined for the organization, and a maximum of 10 rollup fields per entity.

‍Workflows - A workflow cannot be triggered by the rollup field updates, and a workflow wait condition cannot use a rollup field.

‍Applying arollup to the rollup field - Not possible.

‍A rollup can’t reference a calculated field,if that calculated field uses another calculated field. - This is the case even if all the fields of the other calculated field are on the current entity.

‍Filters can only be applied to: - the source entity or related entities, simple fields, or non-complex calculated fields.

‍A rollup can only aggregate values over related entities with the 1:N relationship. - It can’t aggregate over the N:N relationships. Also, a rollup can’t be done over the 1:N relationship for the Activity entity or the Activity Party entity.

‍Business rules, workflows or calculated fields. - Always use the last calculated value of the rollup field.

‍A rollup field is aggregated under the system user context. - All users will see the same value for the rollup field. Rollup field visibility can be controlled through the field level security (FLS), by restricting who can access the rollup field.

‍Rounding of figures. - If the precision of the aggregated field is greater than the precision of the rollup field, its values will first be rounded down to the precision of the rollup field (from three to two decimal places, for example).

‍Rollup field aggregation usesonly direct relationships. - hese relationships have to be explicitly defined in the rollup field definition. Indirect relationships to the source entity will not be included.

How can I learn more about rollup fields in Dynamics 365?

Creating a rollup field in Dynamics 365 can be essential for a business’ data insights. They’re a great way to show important aggregated data without having to manually carry out complex calculations or extensive research, and just one of the incredibly useful features of Microsoft Dynamics 365.

If you want more information on rollup fields, or any of the other benefits of Dynamics 365, reach out to Gestisoft today. We’re experts on the full range of Dynamics 365 features, and we can provide you with training and guidance, as well as consultation on what is the best CRM solution for your business. Gestisoft also offers custom implementations of the Dynamics 365 software, tailored to your own specific needs and interests.