CRM Calculated Rollup Field Types

Dynamics CRM 2015: Field Types

We look at calculated field types and rollup field types

By Paddy Byrne

 

This post covers my experience with a new feature in Dynamics CRM 2015 which allows you to specify whether you would like new fields which you create to be either Calculated or Rollup.

This new functionality comes in the form of a "Field Type" property available when you create a new field for an entity.

Field Types 1
The first option I'll cover is the Simple option. This specifies that the field will be just like the fields in previous CRM versions and will not be automatically calculated or rolled up. There's nothing new here so there's no need to cover this field type in any more detail.

 

Rollup Fields

The second that I'll cover is the Rollup field type. This field type is useful when you want to aggregate values from related records. In this example I'm adding a field named 'Claim Miles' to my custom Expense Claims entity. I expect the value in this field to be automatically set to the total of the 'Claim Miles' values in each of my related child 'Expense Claim Line Items'.

So I've chosen the Rollup option for my field type. Clicking on the Edit button will create the field and allow me to configure my Rollup field.

Field Types 2
The Source Entity information is already present so the first thing I need to do is add the Related Entity Information which I can select from a drop down menu available by clicking the 'Add related entity' text. I select my entity (in this case 'Expense Claim Line Items') and click the tick symbol.

Field Types 3
Next, I have an option to add filter options. In this exercise I only want to count the number of Claim Miles on 'Submitted', 'Rejected' or 'Paid' Expense Claim Line Items so, I'll filter by the relevant Status Reasons.

Field Types 4
All that's left now is for me to add the Aggregation. In this case I want the field to calculate the SUM of the all the 'Claim Miles' fields on the associated Expense Claim Line Items but you can choose to calculate the SUM, MAX, MIN or COUNT of any field on the related records as long as the data types are compatible.

Field Types 5
That's all the information I need to configure my new rollup field so I can Save and Close both the configuration and the field details windows.

Field Types 6

Calculated Fields

Lastly, I'll explore Calculated fields. Creating a field of this type will allow you to calculate the values contained in other fields and populate this one automatically.

So, I've created the field as normal but this time I'll choose the Calculated option for my field type. As before, clicking the Edit button will allow us to configure the calculation.

Field Types 7
The first option we have is to add conditional statements. This is done in the same way as you would when you create a Workflow or Dialog. In my case I'm going to populate a field named 'Mileage Claim Total' with the product of two other fields 'Rate (pence per mile)' and 'Miles Claimed' so I don't need to add a conditional statements but below is a screenshot to show you what they look like.

Field Types 8
After adding I add my conditions (if any) it's time for me to add my calculation. Clicking on 'Add action' allows me to start configuring the calculation.

Field Types 9
Clicking in the field after the '=' symbol displays a drop down list which allows us to select numerous different formula to build of calculation. In this case I want to multiply the values of two fields so I'll start by selecting the first one 'Rate (pence per mile)'. This populates the white text area. I can then add a '*' symbol to specify which action to take. After typing this another drop down list is displayed prompting me to add another field from which I'll choose Claim Miles.

Field Types 10
Once my formula is complete I can press the 'tick' icon and add an ELSE statement in the same way I can for the IF statement if required. In this case I don't have any conditional statements so I won't add anything.

Outcomes

So now that we have looked at how to add these fields let's see what they look like in action. On my Expense Claim form I have a calculated field called 'Mileage Claim Total' which uses two other fields on the form - 'Claim Miles' and 'Rate (pence per minute)'. In my example the value of my Claim Miles field is 55 and my Rate field is 50 so my Mileage Claim Total is calculated at £27.50. So what happens if I change the value of my Rate field to 40? Nothing happens immediately but on saving the form my Mileage Claim Total is recalculated to £22.00 and updated right before my eyes.

Field Types 11
That's the Calculated field type tested but what about my rollup fields?

I have a rollup field on my Expense Claim form called 'Small Purchases'. It is configured to find the total of all the values in the 'Small Purchases' field on each Line Item. At the moment, the value is set to £20.00 because I have two line items with values of £15.00 and £5.00. I change the £5.00 to £10.00 so I would expect to see a £5.00 change to the field on the parent Expense Claim, but I don't. I try navigating away from the page and returning to see if something's caching but the value remains unchanged so I wonder what the value will be when I view the field in Advanced Find. It still hasn't changed. Then I notice that on the form, when I hover over the rollup field there is a 'refresh' or 'sync' icon and hovering over this displays a tooltip saying 'Recalculate'. Pressing this does indeed recalculate the value correctly.

Field Types 12
Having read the very detailed Microsoft TechNet article on rollup fields I can confirm that the fields are calculated every hour.

In conclusion, rollup and calculated fields aren't without their limitations but they are an excellent new addition to Microsoft Dynamics CRM which really let you leverage functionality previously only available through the use of custom code.