Calculated Fields - New in CRM 2015
In previous versions of Dynamics CRM, calculations required developer support. It required additional code to be written and deployed through plug-ins, javascript, etc.
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
- Currency
- 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)
Example
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
Comments
- Anonymous
November 21, 2014
Awesome, now we can really keep a nice and clean data model, and still do all the extra stuff we always need to do to make the data model functional. - Anonymous
November 25, 2014
This is great. isn't it. I just wish we could calculate Age from Birthdate, and Next Birthday from that.. - Anonymous
November 27, 2014
Thanks for this! I just got my hands on 2015 today.
Question: Is there a system date / current date that I can call using these formulas before the record is saved for the first time? When we create a new transaction activity, I want to be able to set a default follow update immediately at today + 21, but still let the user overwrite. Any advice? - Anonymous
January 05, 2015
Hi, Is it possible to get the Current Date in the Calculated Field Formula? - Anonymous
January 23, 2015
Great article. I hope someone can assist with this question:
I'm trying to create a calculated field in an Opportunity where a check is made to see if the associated Contact has the Email (emailaddress1) field populated.
I created a new Opportunity field called "Capture e-mail" which is a two-options data type calculated field type. When I edit the calculated field and try to set the following condition:
If Contact (Contact) Email contains data then set capture contact e-mail value to completed
However, when I look at the drop down list for the contact field in the IF condition, the drop down list seems to not have all the fields for contact. In fact, this is an alphabetical list that only goes down to the letter 'D'. The last entry in the list is 'Do not allow phone calls'.
Since I cannot scroll down the list past the letter 'D', I cannot choose 'Email' from this list.
Any ideas on what I may be doing wrong here?
Thanks,
Jason
BTW - We are using CRM Online 2015 (just upgraded this week) - Anonymous
February 04, 2015
Given a field date of birth and a field Age - can we use it to calculate age? - Anonymous
June 21, 2015
With Now() you can get current date time in calculated fields - Anonymous
April 08, 2016
I'm looking to extract the month only from the created date and populate it in a calculated field called "Created Month"Is this even possible using the calculated fields? - Anonymous
May 25, 2016
Can we compare a datetime tyoe value to current date? [e.g. if AnyDate == today()] ? is it really possible to put such condition in a business rule in CRM? - Anonymous
July 08, 2016
Is it possible to do a "One Month Later"? - Anonymous
February 09, 2017
Is it possible to override calculated fields? Since this is just being added, and I am setting a "Now()" calculation, the dates that should be displayed for certain records come before "now", and a straight-forward Data Import will not update these old records with appropriate dates.