Posted by Paul Haag
Previously to Microsoft Dynamics CRM 2015, your only option to create a calculated field was to either create a plugin or JavaScript to perform the calculation. If you wanted to support immediate feedback when the user changed one of the dependencies in the front-end as well as calculation on imports, you often had to do both.
Previously to Microsoft Dynamics CRM 2015, your only option to create a calculated field was to either create a plugin or JavaScript to perform the calculation. If you wanted to support immediate feedback when the user changed one of the dependencies in the front-end as well as calculation on imports, you often had to do both.
New in Microsoft Dynamics CRM 2015
With CRM 2015, however, calculated fields can be added through the standard field editor. With standard availability for CRM 2015 now upon us, we’ve had a chance to play around with the real-world behavior of this new field type.
Using the editor to create calculated fields
The actual use of the editor to create the fields is fairly straightforward and no doubt will be documented adequately elsewhere, so I’m not going to go into that.
Lesser-known functionality
Instead, I’ve tried to detail some of the non-obvious functionality below.
- The formula editor assists with entering valid fields with type-ahead for field names (“Intellisense”). These fields are not filtered by type. For example, if you are creating a currency rollup field, text fields will appear in the list, but if you attempt to use one of them improperly, you will get an error message telling you that you can’t save the current definition.
- Using Intellisense results in the selected field being added at the end, not where the cursor was.
- Creating a calculated field appears to immediately set the value for all records.
- Calculated field values are persisted to the [entityname]Base table.
- If any of the fields included in the calculation are null, the calculated value will also be null. Unfortunately, there doesn’t appear to be a function to supply a default value to avoid that case, so it will either need to handled for the dependent fields themselves or with an exponentially increasing (for each dependent field) set of conditional statements within the formula.
- Using a decimal field within a whole-number calculated field results in the decimal value being rounded rather than truncated. The rounding occurs on the final value, rather than on each individual decimal field. So, 12.34 * 25 = round(12.34 * 5) = 309 instead of round(12.34) * 25 = 300.
- The division operator is a forward slash (/) not a backslash (\) and the multiplication operator is an asterisk (*) not an x. The string literal character is a double quote (“), not a single quote (‘). Modulo (percent symbol) and power (carat) operators don’t appear to be supported, but grouping mathematical operations with parentheses is.
- You can include decimal or whole number, but not date, fields in calculated string fields, but I couldn’t figure out how to control the formatting. If you use the field value directly, the decimal value is included without the currency symbol and with the precision defined by the source field (e.g. a calculated field that is CONCAT(“Total Revenue is “, totalrevenue) will show something like, “Total Revenue is 37.34”). However, if you perform a mathematical operation on the numeric field, it shows 10 decimal places (so, if totalrevenue is divided by 2 in the formula above, the result is, “Total Revenue is 18.6700000000”).
- When creating a formula for a single line of text calculated field, single line of text fields with their maximum length set to the field maximum (e.g. 4,000) cannot be used. However, if you change the max length to 3,999, add it to a formula and then change its length back to 4,000, the system will use it in calculations, with anything past 4,000 characters in the result being truncated. When you open the formula again, you will get an error at the top saying too-long field doesn’t exist.
- Not a surprise, but you cannot delete a field that is referenced in a calculation, even if it isn’t used anywhere else in the system (e.g. forms, views, etc.). This is definitely nice, since there is no such check when you are writing a plugin.
InterDyn Artis is a Microsoft partner located in Charlotte, NC. We specialize in ERP and CRM solutions, and offer implementation, customization, and support services for Microsoft Dynamics.