Support Portal ContactGet in touch

Creating a Simple Rollup Field

   Words by Paul McQuillan

   on 08/02/2019 08:30:00

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:

image

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

image

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:

image

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:

image

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.

image

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:

image

Then calculates as:

image

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:

image

Report as:

image

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

image

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:

image

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

image

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.

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

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/customize/define-rollup-fields

Dynamics 365 – Rollup Fields – Important Points

https://community.dynamics.com/365/b/sachinbansalmicrosoft/archive/2018/05/07/dynamics-365-rollup-fields-important-points 

Introducing Calculated and Rollup Fields

https://www.akaes.com/blog/ms-dynamics-crm-2015-understanding-calculated-and-rollup-fields/

  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.