Calculated Fields - New in CRM 2015
This additional code required developers to maintain and update them which causes additional costs. As more records are is created in Dynamics CRM and as the business logic in these calculations evolves, there is a higher risk for performance degradation.
With the release of Microsoft Dynamics CRM 2015, two new fields are being introduced to help some of the more common calculations done in CRM now; Calculated fields and Rollup Fields. Rollup fields and calculated fields can be used independently or complementary to each other.
In this blog post I'll talk about Calculated fields.
Calculated fields lets you, as a System Customizer, define the value of that field by using calculation operators or functions. A developer no longer needs to write code to accomplish such a task. The calculated fields comprise of calculations that use fields from the current entity or related entities.
For example, you might want to know the weighted revenue for an opportunity which is based on the estimated revenue from an opportunity multiplied by the probability.
Or, you might want to automatically apply a discount, if an order is greater than $500.
Calculated Fields are not limited to numeric fields. For instance, a calculated field can set a Phone Number for an opportunity based on the Account or Contact information.
They can also work with date fields as well.
A calculated field can contain values resulting from a various amount of operators. Let’s take a look at what consists of a calculated field.
Calculated Fields are very flexible in the terms of what Data Types are supported. We support the following data types:
- Single Line of Text
- Option Set
- Two Options
- Whole Number
- Decimal Number
- Date and Time
To make a field Calculated you:
1. Pick a Data Type, eg. "Date and Time" or "Currency"
2. Set the Field Type to "Calculated",
3. Click the Edit button next to the Field Type drop down to create the field and open the Editor
Creating a calculated field uses the same editor as Business Rules or Business Process Flows.
The editor has been enhanced in CRM 2015, specifically for Calculated Fields we've added intellisense. This allows you to type in the logical or display name of a field or function and get suggestions of the available fields or function similar to what has been typed.
When setting the value of a calculated field, you have the ability to choose different functions or operators that allow the data to be modified in certain ways. We’ll quickly cover the different functions and operators available based on Data Types.
The Math Operators are the typical math operators so we won’t really go into them here.
- = (Equal)
- + (Plus)
- - (Minus)
- / (Divide)
- * (Multiply)
The String Functions are
- CONCAT(single line, single line, single line…)
- TRIMLEFT(single line, whole number)
- TRIMRIGHT(single line, whole number)
CONCAT add single lines of text together. These can be fields or can be actual lines of text. If using lines of text, the text will need to be encased with double quotes. For instance, we want to concatenate the Contact’s First name with the Account Name.
If we simply Concatenate those two fields, it would have show SidneyBlue Yonder Airlines (sample) without a space between Sidney and Blue. You can see below, a space was added in between the two fields.
TRIMLEFT takes a single line of text and trims that number of characters starting from the left and working it’s way to the right.
TRIMRIGHT takes a single line of text and trims that number of characters starting on the right and working it’s way to the left.
Finally, we have Date Time functions which is going to take a date time and either add or subtract the specified numbers of Hours, Days, Weeks, Months, or Years.
- ADDHOURS(Whole Number, Date and Time)
- ADDDAYS(Whole Number, Date and Time)
- ADDWEEKS(Whole Number, Date and Time)
- ADDMONTHS(Whole Number, Date and Time)
- ADDYEARS(Whole Number, Date and Time)
- SUBTRACTHOURS(Whole Number, Date and Time)
- SUBTRACTDAYS(Whole Number, Date and Time)
- SUBTRACTWEEKS(Whole Number, Date and Time)
- SUBTRACTMONTHS(Whole Number, Date and Time)
- SUBTRACTYEARS(Whole Number, Date and Time)
In this example I've created a custom entity with two custom (Date and Time) fields; AnyDate and OneWeekLater (calculated)
When I created the OneWeekLater field I picked the Data Type (Date and Time), then I chose Calculated for the Field Type, and finally clicked the Edit button to create the field and enter the Editor
In the Editor the Date functions available to me allowed me to calculate the date one week after the date picked in the AnyDate field (using AddDays(7, new_anydate)) and stick that date into the OneWeekLater field
Testing the calculated field
Picking "Dec 25 2014" for AnyDate
Calculated field displays "Jan 1, 2015"
I hope you'll enjoy Calculated Fields in CRM 2015 once its there