Rollup Fields 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 actual revenue from an 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.
If you are on the Dynamics 365 “new experience” user interface, Rollup Fields will be called Rollup Columns.
If you want to create a 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 Field in Dynamics 365
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.
- 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.”)
- Choose your Data Type as you would when creating any new field (single line of text, currency, etc.).
- Select Rollup as the Field Type.
- Click the Edit button. (It appears once you’ve selected Rollup as the Field Type.)
- 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.:
- Source Entity: This will default to the entity where you are creating your Rollup Field.
- 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.
- 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.
- 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.
- Related Entity Filters: Just like source entity filters, you can choose additional filters for your related entity.
- Aggregation: Here is where you choose the aggregate function for your data: SUM, COUNT, MIN, MAX, or AVG. In this case, we choose SUM.
- Save and close your conditions and then save and close your Field window.
- Open the Form you would like to add your new Rollup Field to. In our case, this is the main Account Form.
- 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 Field functions, called “aggregate functions,” to choose from:
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 the VP of Sales wanted to see total estimated revenue of open 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
- Rollup fields are read-only.
- There is a maximum of 100 Rollup Fields per instance and 10 Rollup Fields per 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 another special type of field: multi-select option sets.
If you’d like experts to create or audit your D365 fields for you, or if you want personalized advice, please contact us.
Dynamics 365 CRM How-To eGuide
41 pages of step-by-step instructions for 6 different key tasks in Dynamics 365 CRM apps. Includes interactions with PowerApps and Flow!Get eGuide