Microsoft Dynamics CRM 2015 has introduced Calculated fields to assist non-developer customisers to perform some easy calculations within Dynamics CRM. If you do not know much about Calculated fields, watch the following short video from Microsoft.
This blog post is not about how to use calculated fields. To know more on how to define and use calculated field, browse to “Define calculated fields”
I wanted to discuss here the intricacies of the calculated fields.
Calculated fields are virtual fields, not physical one i.e. such fields are not committed to database.
How is a calculated field calculated?
Calculated fields are defined as “computed field” in the BASE table in the database and the computation logic is stored in a custom database function within the MSCRM database. Hence, when a form loads or views renders with a calculated field, the database performs the calculation and return the value to the application layer.
What are the implications of this design?
- Calculated fields are available in forms, views and SELECT statement running queries against an on-premises implementation.
- Since this is a virtual field and only available when retrieving the record; no workflows or plugins will fire on update of a calculated field.
- Calculated field value is not available within a plugin code.
- Duplicate detection rules are not triggered on calculated fields.
- The calculated field values are not displayed in the CRM Outlook Offline mode in the tile views or on entity main forms.
Calculated fields can span two entities only with all AND or all OR conditions.
What are the implications of this?
- In the formula only fields from the current entity and related parent entities can be used.
- Only All AND Conditions or all OR conditions possible while defining a calculated field. Multiple levels of AND/OR, a mix of AND/OR is not supported.
- A calculated field can’t contain a calculated field from another entity that also contains another field from a different entity (spanning three entities):
Calculated fields cannot have circular reference in calculation.
What does this mean?
Calculated fields calculation logic can include another calculated field or even a rollup field; however a calculated field cannot refer itself in the its formula or two calculated fields cannot reference each other in their respective formulas. CRM will throw circular reference error in both cases. A maximum number of chained calculated fields is 5.
Some more additional bits
- A roll-up field can’t reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity.
- Saved Queries, Charts and visualizations can have a maximum of 10 unique calculated fields.
- Sorting is disabled on:
- A calculated field that contains a field of a parent record.
- A calculated field that contains a logical field (for example, address field).
- A calculated field that contains another calculated field.