Calculation Fields for Microsoft Dynamics CRM
Calculated fields are a new addition to Microsoft Dynamics CRM enabling automatated calculations for your database - all without the need to rely on scary codes behind the wall.
For example, a calculated field could be defined for opportunity records to show the 'Total Amount with VAT' which automatically calculates the value by adding VAT to the total opportunity amount.
Calculations can involve any mathematical formulation which outputs into decimal, money, whole number field or string level operation (e.g. concat). This must be shown in a single line of text field, or date level operation (e.g. add days) which outputs into a date / time field.
Let’s apply our previous example by creating a calculated field that automatically adds the VAT to an opportunity value.
To start, create a new CRM field on the opportunity entity as you'd normally do.
If you've already upgraded to the latest 2015 edition you'll see 'Calculated' as a new field type option.
Once this is selected, click the edit button and enter the appropriate formula.
CRM offers an option to only run automated calculations if one of more conditions are met.
In this instance we've added an unconditional calculation that will add VAT at the current rate of 20% to the value of a 'Total Amount' field for all opportunitities and show the result in a new 'Total Amount Including VAT' field.
Click ‘Save and Close’ to create the field.
Once saved, all the active opportunity records that reference this field will have a up to date values applied in line with the formula.
Previously, Javscript or plugins could be applied to replicate calculated fields but neither solution had the capability to retrospectively apply calculations to existing records or update records automatically.
Unlike CRM rollup fields, another recent addition to Dynamics, calculated fields are updated in real-time presenting users with an instant calculation when a record is saved!
Using Calculations to Concatenate Field Values
Calculated fields can also be used to join values from multiple fields.
Using another scenario we'll create a new single line text field that will be added to the account entity which concatenates the account name and primary contact name using a hyphen as a field join.
For example, if 'David Brettell' is the primary contact for the account record named 'Preact CRM' the new concatenated field would appear as 'Preact CRM - David Brettell' if we have formatted this to prefix the account name ahead of the contact.
This process will utilize the lookup entity field extension in the calculated field formula to get primary contact full name.
In the example below the calculated value for the 'Set Account Detailed Name' field is a concat of the account name and a lookup of the full name of the primary contact.
Once applied the new field appears on the account entity form:
Please note, the lookup extension only works when the lookup is of type single entity. A multi-entity lookup (e.g. customer) is not supported.
Calculated Date Fields
Finally, calculated fields can be used for date and time values.
To demonstrate we'll create a new field for lead records named 'Follow up date' which should be calculated based on the purchase timeframe selected.
This ensures that a sales team always knows which closeable leads to focus to maximse their lead:order conversion.
For this example, we'll also apply some conditional rules to the calculation formula.
If the purchase timeframe for a lead is entered as 'Immediate' and Phone as a contact method is set to 'Allowed', the calculation should populate a 'Follow Up Date' field with tomorrow's date.
In the first instance an IF...THEN rule has been defined that will query the 'Do Not Allow Phone Calls' and 'Purchase Timeframe' fields on lead records:
Skipping forward we see the completed calculated field rule below.
For all new leads where the condition is triggered the 'Followup Date' field will be set to the following day.
A further ELSE / IF rule has been defined that will be triggered for other new leads that have a purchase timeline set to this quarter. In these instances the CRM automated calculation will set the followup field as 1 week later:
This example uses functions to add days and weeks to the calculated field value.
Several other built in functions are available, for dates these are:
ADDHOURS, ADDDAYS, ADDWEEKS, ADDMONTHS, ADDYEARS, SUBTRACTHOURS, SUBTRACTDAYS, SUBTRACTWEEKS, SUBTRACTMONTHS, SUBTRACTYEARS
For string level operations we've already covered the concat function, CRM also supports two further options:
TRIMLEFT and TRIMRIGHT
As implied these functions trim the value of the calculated field from either the left or right.
For example, a rule could be set to:
The result will cut the final 5 characters from the concatenated calculated field.
With the current CRM 2015 release there are several considerations and limitations with regards calculated fields:
- Action field - while editing formula allows intellisense capabilities which reduces chances of formula error due to inappropriate field names or functions
- Condition field - allows branching of conditions as well as joining several conditions together with AND/OR operators
- A calculated field cannot refer itself for calculation. However calculation field can refer other calculated fields, it allows maximum chain of 5 fields
- Existing simple CRM fields can’t be converted into calculated fields
- Calculated fields can’t be used as a trigger in plugin or workflows
We hope you found this a useful introduction to CRM calculated fields. Contact Preact to discuss how this functionality can be applied to your own database.