{"id":132,"date":"2022-01-06T09:27:07","date_gmt":"2022-01-06T09:27:07","guid":{"rendered":"https:\/\/blog.citrus-lime.com\/crmc\/?p=132"},"modified":"2022-01-06T09:27:08","modified_gmt":"2022-01-06T09:27:08","slug":"creating-a-simple-rollup-field","status":"publish","type":"post","link":"https:\/\/blog.citrus-lime.com\/crmc\/creating-a-simple-rollup-field\/","title":{"rendered":"Creating a Simple Rollup Field"},"content":{"rendered":"\n

Roll-up Fields in Dynamics allow us to perform a SUM or COUNT of Child Records that then \u2018rolls-up\u2019 to a Parent Record.<\/p>\n\n\n\n

The most obvious example of this could be a simple COUNT of the Number of Opportunities recorded in CRM for a particular Client \u2013 so we could then view a simple Number in the Account Form that would quickly inform us of the number of Opportunities.<\/p>\n\n\n\n

This then becomes doubly helpful in Reporting where we might then use the \u2018Count of Opportunities\u2019 Roll-up Field in our Reports based on the Account Entity.<\/p>\n\n\n\n

Here we will look at creating a basic Roll-up Field and see how this is done in Dynamics.<\/p>\n\n\n\n

Scenario \u2013<\/strong> 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 \u2013 then we could look at adding a Roll-up Field to COUNT the Number of Open Tasks for an Opportunity.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Creating the Field \u2013 <\/strong>first of all we add the Field to the Opportunity Entity as we would any other Dynamics Field \u2013 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.<\/p>\n\n\n\n

But, before we save the Field into CRM, we select \u2018Rollup\u2019 as the Field Type:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Edit the Rollup Logic \u2013 <\/strong>when ready with the Field, we can click EDIT to start defining the logic behind our Rollup Field.<\/p>\n\n\n\n

Where are we rolling up from? <\/h3>\n\n\n\n

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<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

How do we Filter our Roll-up? <\/h3>\n\n\n\n

Typically we will not want a simple COUNT but a COUNT based on a particular Filter \u2013 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:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

What COUNT or SUM do we want to Roll-up?<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

In our example, we can simply define the Field to count the number of Tasks returned by our Filter for that Opportunity:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We can then save our new Field into CRM.<\/p>\n\n\n\n

Add to the Form and Publish<\/h3>\n\n\n\n

We can then open the normal Form Designer in Dynamics and position our new Field on the Form.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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 \u2013 to this end, we can use the Rollup Field as we would any other Field in the Advanced Find:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Then calculates as:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

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 \u2013 so we can produce a report on our Open Opportunities that have no Follow-up Task recorded:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Report as:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Giving us a handy use for our Roll-up Field in helping us manage Opportunities that need further Follow-up.<\/p>\n\n\n\n

Going one step further..<\/h3>\n\n\n\n

As we see here Rollup Fields can do basic calculations and aggregations \u2013 however we expand on this and look at MAX and MIN operations within a Rollup Field.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

For this, we customise a new Roll-up Field as:<\/p>\n\n\n\n