Roll-up Fields in Dynamics allow us to perform a SUM or COUNT of Child Records that then ‘rolls-up’ to a Parent Record.
The most obvious example of this could be a simple COUNT of the Number of Opportunities recorded in CRM for a particular Client – so we could then view a simple Number in the Account Form that would quickly inform us of the number of Opportunities.
This then becomes doubly helpful in Reporting where we might then use the ‘Count of Opportunities’ Roll-up Field in our Reports based on the Account Entity.
Here we will look at creating a basic Roll-up Field and see how this is done in Dynamics.
Scenario – if we take the scenario where we want to be aware of any Opportunities in CRM that do not have any Open Tasks for Follow-up – then we could look at adding a Roll-up Field to COUNT the Number of Open Tasks for an Opportunity.
This will then allow us to run an Advanced Find to find any Opportunities that have 0 Pending Tasks, and so the Opportunities that do not have any defined Next Steps.
Creating the Field – first of all we add the Field to the Opportunity Entity as we would any other Dynamics Field – specifying Whole Number or a Decimal Data Type for the Field, as we will need a Data Type that is capable of the SUM or COUNT logic we want.
But, before we save the Field into CRM, we select ‘Rollup’ as the Field Type:
Edit the Rollup Logic – when ready with the Field, we can click EDIT to start defining the logic behind our Rollup Field.
Where are we rolling up from?
Our first step is to pick what Related Entity in CRM we want to roll-up from, this will be the Child Entity that we want to COUNT or in some way SUM into our Parent Record. In our example, this would be the Tasks regarding the Opportunity
How do we Filter our Roll-up?
Typically we will not want a simple COUNT but a COUNT based on a particular Filter – so in our example we would only want to COUNT Open Tasks so that we are not also including Closed Tasks in the Count. We would add this logic by specifying the Filter here:
What COUNT or SUM do we want to Roll-up?
As the final step in our Rollup Logic, we specify what calculation we want the Field to calculate from our Child Records and insert into the Parent.
This can be a particular calculation on Fields in the Child Records to build reporting logic, or can be a simple COUNT of the records involved.
In our example, we can simply define the Field to count the number of Tasks returned by our Filter for that Opportunity:
We can then save our new Field into CRM.
Add to the Form and Publish
We can then open the normal Form Designer in Dynamics and position our new Field on the Form.
The Rollup Field will then (naturally enough for a calculated field) be presented as Read-Only, however this will also come with a Date Field for when the Rollup Calculation was last updated.
Dynamics 365 will automatically calculate Rollup Fields every 12 hours as part of a system-wide refresh process; but we can also force a recalculation by clicking the Recalculate button.
Rollup Fields give us basic calculations that can make the Form more useful to the User, but their power in CRM really comes into play when coupled with Views and Advanced Find to return groups of records based on this Calculated Field – to this end, we can use the Rollup Field as we would any other Field in the Advanced Find:
Then calculates as:
This can then produce a Report of Opportunities that have multiple Follow-up Tasks in place, or particularly Opportunities which do not have a Follow-up Task in place .i.e. where our Rollup Field has calculated to 0.
This ability to run a View on the presence of a negative can be quite difficult in Dynamics, and so the use of a Rollup Field in this instance allows us to produce this type of report more easily – so we can produce a report on our Open Opportunities that have no Follow-up Task recorded:
Giving us a handy use for our Roll-up Field in helping us manage Opportunities that need further Follow-up.
Going one step further..
As we see here Rollup Fields can do basic calculations and aggregations – however we expand on this and look at MAX and MIN operations within a Rollup Field.
When combined with looking at a Date Field this can be used to find the most recent record in the past or future, and this gives a handy tool for not just tracking the number of Tasks for an Opportunity but also the Last Completed Task as the last follow-up to that Opportunity.
For this, we customise a new Roll-up Field as:
- Rollup all Regarding Tasks
- Where the Task is Completed
- Calculate as the MAX of the Task’s Actual End
This will then give us the last Follow-up Date for each of our Opportunities which we can add into our Views and Advanced Finds:
We can similarly implement a Next Scheduled Follow-up Date field in the same way:
- Rollup all Regarding Tasks
- Where the Task is Open / Active
- Calculate as the MIN of the Task’s Due Date
This builds us a simple system of tracking Last, Next and the Count of Follow-up Tasks for an Opportunity.
This focuses particularly on Tasks associated to an Opportunity, but can be used with any Activity instead of just Task by using the Related Activity Relationship in CRM.
This gives us a practical usage of Roll-up Fields that we can apply in our uses of Dynamics.
Whilst a mainstay of functionality in Dynamics for some time now, this forms part of the MB-200 Exam for anyone looking for Dynamics / Power Platform Core Certification - and so is a useful area to be up to speed on.
Particularly in terms of:
* The difference between Calculated Fields vs Rollup Fields
* How often Rollup Fields are recalculated
* How Synchronous Calculated Fields compare to Asynchronous Logic in Dynamics - and how this compares to Rollup Fields.
* Where to use Power Automate / Flow vs where to use Rollup Fields
* Virtual Fields vs Logical Fields in the Common Data Service
* What is the Common Data Service compared to CRM? https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-intro
Further Reading on Roll-up Fields
The following articles give further information on using Rollup Fields in Dynamics 365
Define rollup fields that aggregate values
Dynamics 365 – Rollup Fields – Important Points
Back in the day for CRM 2015 - Introducing Calculated and Rollup Fields