How to Create Rollup Fields (aka Rollup Columns) in Dynamics 365 CRM

Rollup Fields (now officially called Rollup Columns) can be extremely useful when you need an aggregated value using data from multiple Dynamics 365 CRM records. For instance, you could use a Rollup Field if your VP of Sales wants to see the total estimated revenue from open Opportunities for each Account directly on the Account Form.

In this article, I’ll explain how to create and use Rollup Fields, plus some limitations to be aware of.

• How to Create a Rollup Column (the New Way)
  • Video Walkthrough
• How to Create a Rollup Field (the Old Way)
• Rollup Field Functions
• Rollup Field Use Cases
• Limitations

Microsoft has now renamed Fields to Columns (and Entities to Tables) and put all the settings into the Power Platform. Below are instructions for both the new and the old approach.

If you want to create a Column or Field that calculates its value based on data from within a single record or its parent, check out Calculated Fields instead.

Note: There are now 5 different Dynamics 365 apps that serve various CRM needs. In some contexts, you may also see those products referred to as “Customer Engagement” or “CE” apps. Rollup Fields work the same no matter which of those apps you’re using.

How to Create a Rollup Column in Dynamics 365 (The New Way)

You can create a Rollup Column by adding a new Column to your Table, choosing Rollup as the Column Type, choosing your Data Type, and building your logic in the same manner as a business rule or a Calculated Column.

Detailed steps and screenshots below. We’ll follow the example of showing the total estimated revenue from open Opportunities on each Account directly on the Account Form.

1. In the Power Platform, create a new column for the relevant Table — in this case, Account.
2. Choose your Data Type as you would when creating any new Column (single line of text, currency, etc.).
3. Select Rollup as the Behavior.
4. Click the Edit button below Behavior. (It appears once you’ve selected Rollup as the Behavior.)
5. Build the logic of your Rollup Column. Six components make up the logic behind your Rollup Column:
   • Source Entity: This will default to the Table where you are creating your Rollup Column. In this case, that’s Account.
   • Use Hierarchy: Choose YES if you would like to include a parent/child relationship in your Table to roll up data across those relationships. In our example, we want to see total estimated revenue for all parent and child Accounts, so we’ll choose YES.
   • Source Entity Filters: You have the option of choosing additional filters/conditions for your source Table. If we wanted to see total estimated revenue for all related Accounts only in a certain city, or with a range of Account numbers, this is where we could select those filters.
   • Related Entity: Choose if you want to use data from a related Table in your Rollup Column. Since we want to get the total estimated revenue from each Account’s open Opportunities, we’ll choose Opportunities as our related Table.
   • Related Entity Filters: Just like source Table filters, you can choose additional filters for your related Table.
   • Aggregation: Here is where you choose the aggregate function for your data: SUM, COUNT, MIN, MAX, or AVG. In this case, we choose SUM.
6. Save and close your conditions and then save and close your Column window.
7. Open the Form you would like to add your new Rollup Column to. In our case, this is the main Account Form.
8. Add your new Rollup Column to your Form.

Now when we open our Account Form, we see our new Rollup Column, Estimated Opportunity Revenue. Looking at the Account called A. Datum Corporation, we can see that our Rollup Column calculates the sum of all the Opportunities for the Account:

The calculator icon identifies this as a Rollup Column. You can manually refresh your calculation online by clicking the Recalculate button.

Note: The “Last Updated” column will be automatically created and added to the form. There may be additional fields that get created based on the data type of the column.

Video Instructions for Creating Rollup Columns in D365

How to Create a Rollup Field in Dynamics 365 (The Old Way)

We no longer advise that you use the older Rollup “Field” approach described below. Instead, we advise you to use the new instructions for Rollup “Columns.” Use the information below with caution.

You can create a Rollup Field by adding a new field to your Entity, choosing Rollup as the Field Type, choosing your Data Type, and building your logic in the same manner as a business rule or a Calculated Field.

Detailed steps and screenshots below. We’ll follow the example of showing the total actual revenue from each Account directly on the Account Form.

1. Create a new field for the relevant Entity — in this case, Account. (If you are on the D365 new experience, you will create a new “column” for the relevant “table.”)
2. Choose your Data Type as you would when creating any new field (single line of text, currency, etc.).
3. Select Rollup as the Field Type.
4. Click the Edit button. (It appears once you’ve selected Rollup as the Field Type.)
5. Build the logic of your Rollup Field. Six components make up the logic behind your Rollup Field. I’ve labeled them a through e in the screenshot below.:
   1. Source Entity: This will default to the Entity where you are creating your Rollup Field.
   2. Use Hierarchy: Choose YES if you would like to include a parent/child relationship in your Entity to roll up data across those relationships. In our example, we want to see total Account revenue for all parent and child Accounts, so we choose YES.
   3. Source Entity Filters: You have the option of choosing additional filters/conditions for your source Entity. If we wanted to see total revenue for all related Accounts only in a certain city, or with a range of Account numbers, this is where we could select those filters.
   4. Related Entity: Choose if you want to use data from a related Entity in your Rollup Field. Since we want to get the total actual revenue, we’ll choose Invoices as our related Entity.
   5. Related Entity Filters: Just like source Entity filters, you can choose additional filters for your related Entity.
   6. Aggregation: Here is where you choose the aggregate function for your data: SUM, COUNT, MIN, MAX, or AVG. In this case, we choose SUM.
6. Save and close your conditions and then save and close your Field window.
7. Open the Form you would like to add your new Rollup Field to. In our case, this is the main Account Form.
8. Add your new Rollup Field to your Form.

Now when we open our Account Form, we see our new Rollup Field, Total Account Revenue. Looking at the Account called Adventure Works, we can see that the field calculates the sum of all the Invoices for the Account:

The calculator icon identifies this as a Rollup Field. You can manually refresh your calculation online by clicking the Recalculate button. When you hover on the button, you can also see a popup that indicates when the last update happened.

Rollup Field Aggregate Functions

There are 5 different rollup functions, called “aggregate functions,” to choose from:

• SUM
• COUNT
• MIN
• MAX
• AVG

All of those will be self-explanatory to most people who have used functions in Excel.

Rollup Field Use Cases

• Similar to the example above, if your VP of Sales wanted to see total actual revenue of won Opportunities for an Account, we could create a Rollup Field for that.
• A Rollup Field is also perfect if, for instance, the service department wants to see the number of high priority open Cases across all Accounts.
• You can include Rollup Fields in forms, views, charts, and reports.
• You need to have a Rollup Field as part of a goal metric.
• And you can even use a Rollup Field as part of a Calculated Field.

Limitations of Rollup Fields (aka Columns)

• Rollup Fields are read-only.
• There is a maximum of 100 Rollup Fields per instance and 10 Rollup Fields per Table (aka Entity).
• Workflows cannot be triggered by Rollup Field updates.
• Rollup Fields are only supported over related entities with a one-to-many relationship. A many-to-many relationship is not supported.

The values of the Rollup Fields are calculated by system jobs that run asynchronously in the background every hour. Mass updates run every 12 hours. Administrators can set when a job is run or pause the job. Also, users, when online, can manually refresh the field at any time.

Rollup Fields are one of the many features in Dynamics 365 CRM solutions designed to improve efficiency and deliver quick insights. You may also want to read our instructions for calculated fields and multi-select option sets.

If you’d like experts to improve or audit your D365 configuration for you, or if you want personalized advice, please contact us.