From the MVPs: Dynamics CRM 2015 Calculated Fields
This is the 47th in our series of guest posts by Microsoft Most Valued Professionals (MVPs). You can click the “MVPs” tag in the right column of our blog to see all the articles.
Since the early 1990s, Microsoft has recognized technology champions around the world with the MVP Award . MVPs freely share their knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology. Of the millions of individuals who participate in technology communities, around 4,000 are recognized as Microsoft MVPs. You can read more original MVP-authored content on the Microsoft MVP Award Program Blog .
This post is by Dynamics CRM MVP Leon Tribe . Thanks, Leon!
Dynamics CRM 2015 Calculated Fields
CRM 2015 is now out and available. If you are interested in seeing it, get a 30 day trial as these are now provisioned as CRM 2015 by default.
Obviously there are a lot of new features in the new version but one which particularly excites me are the calculated fields. This is a feature common to many CRM systems but, until CRM 2015, the only way we could emulate the behaviour in Dynamics CRM was through script on the form (or possibly plugins and workflows).
CRM 2015 changes the games in this regard. Fields can now be added to CRM and set to automatically calculate using Excel-like formulae. Given the potential of this I thought I would walk through setting up the fields and the functions available.
How to Set Up Calculated Fields
To set up a new calculated field, you create the field, as normal, and you will see a Field Type drop down. Changing this from Simple to Calculated makes the field a calculated field.
To define how the field is populated, you click the Edit button and set the field up, in a similar way to how Business Rules are set up in CRM 2013/2015.
In this example, my new field gets populated with a concatenated version of the address. For the Action formula, the only fields you can access are those on the same entity; you cannot use fields on related entities.
The field is presented as read-only, which makes sense, and refreshes on save and load but not on change. What strikes me is the similarity to Excel. If you are familiar with setting up Business Rules in CRM 2013 and you have used Excel formulae, CRM’s calculated fields will feel completely natural.
The Available Formulae
Compared to Excel, the first release of formulae is quite limited. However, I expect this will grow over time. Even so, there are a number really handy functions in the mix.
Text and Multiple Lines of Text
For Text fields we have:
CONCAT: The equivalent of the Excel CONCATENATE formula, this allows you to put two strings together, whether they be fixed text e.g. “hello world” or a field in CRM
TRIMLEFT/TRIMRIGHT: These trim a number of characters from a string, starting from the left or right, respectively. It is sort of the opposite of the LEFT/RIGHT functions in Excel. Initially I was thinking I could use this for generating the initials of a Contact, for example, but without a function to calculate the length of the string in question, this is not possible.
Unless the string you wish to manipulate has a very well defined format e.g. an Account Number, I am not sure these functions are too useful.
Unfortunately, we do not have the option of creating a calculated field for a Multiple Lines of Text field, so you will have to stick to a normal Text field.
Option Sets and Two Options
For Option Sets, there are no formulae as such but you can make a calculated field reflect the value of another Option Set which uses the same global Option Set as its basis, even if it is on a related record.
In the case above, my new calculated field (NewOSField) will reflect the same value as an unspecified Option Set field on the associated Parent Account. There is also the option of changing the Type to ‘Value’ and fixing the value, based on the item values in the Option Set, rather than basing it off another field in the system.
For Two Options, the experience is similar but you can base it off any other Two Options field from a related entity or fix the value to, for example, “Yes” or “No”.
Whole Numbers, Currency Fields, Floating Point Numbers and Decimal Numbers
Other than the standard calculation functions (+, -, *, /), there is not a lot for number manipulation. A couple of functions I would like to see are DIV/MOD functions because, with a function to convert types e.g. Date to Number (casting functions are also missing at this point), you can determine what day of the week a date falls on and then use this for determining things like work days and weekends.
In the case of Floating Point numbers, it is not possible to make a calculated field. The option simply is not there.
Date and Time Fields
Date and Time fields have the largest set of functions:
ADDDAYS/ADDHOURS/ADDMONTHS/ADDWEEKS/ADDYEARS: You provide a date and the number of units to add. You can only add whole numbers of units so it is not possible to increment a time by, say, half an hour or 15 minutes.
SUBTRACTDAYS/SUBTRACTHOURS/SUBTRACTMONTHS/SUBTRACTWEEKS/SUBTRACTYEARS: The same as the ‘ADD’ functions, except removing time. Again, only whole number increments are possible.
In the absence of the MOD/DIV functions previously mentioned, another option would be ADDBUSINESSDAYS etc. This would be very useful for both workflows and for calculated fields.
Conclusions
The addition of calculated fields is a great plus for CRM 2015. While the initial offering of functions are limited, there is still lots of potential for use and expansion in future releases. I recommend you have a play and see how you can make use of this and the other great features of CRM 2015.