Setting up calculated fields

Similar to rollups fields, calculated fields are another recent addition to Dynamics 365's configuration capabilities that significantly reduces the need for custom code extensions.

As the name suggests, calculated fields allow you to set the value of an attribute based on another field, a function, or a formula. Furthermore, calculated fields may trigger based on a set of conditions.

In this example, we will configure a field to calculate the difference in months between two dates: (student start date, and student end date).

Getting ready

Similar to the previous recipes, a System Customizer or higher security role is required to perform the configuration as well as a solution to contain the changes.

How to do it

1. Navigate to Settings | Solutions | Packt.
2. Create two new fields by clicking on Entity | Contact | Fields | New as follows:
   • Start Date of type Date Time
   • End Date of type Date Time
3. Create a new Calculated field and enter the following details:
   • Display Name: Months for graduation
   • Data Type: Whole Number
   • Field Type: Calculated
4. Click on Edit next to the Field Type drop-down.
5. In the Calculated dialog under Condition (Optional), click on Add condition.

6. In the If row, enter the following details and click on the tick box:
   • Entity: Current Entity (Contact)
   • Field: Contact Type
   • Operator: Equals
   • Type: Value
   • Value: Student
7. Under Action, click on Add action, enter DIFFINMONTHS(packt_enddate, packt_startdate), and then click on the tick button:

8. Click on Save and Close on the rollup field dialogue and the attribute dialogue.

How it works

In this recipe, we used a point and click configuration to set up a calculated field to calculate the number of months' difference between a graduate's start date and end date.

In step 5 and step 6, we defined the condition, and in step 7, we defined the calculation formula.

Calculated fields translate to synchronous server-side code executions. They are similar to plugins and are executed during stage 40 of the execution pipeline of a post update or create message.

Given that the execution takes place on the server side, the users will only see the changes right after a save event is triggered. Unlike business rules and JavaScript customization, the result is not instantaneous. They do not trigger after the condition is met on the form frontend.

Similar to rollup fields, calculated fields are read-only and do not take into account a user's security roles.

There's more...

Calculated fields are a powerful addition; they can be used in many scenarios, some of which are:

• Number calculations, (for accounting, weights, and so on)
• Retrieving values from related entities
• Constructing a string based on other attributes

The TechNet article (https://technet.microsoft.com/library/dn832103.aspx) covers calculated fields in a few examples. The article also covers some limitations of calculated fields. Among them are the following:

• Calculated fields cannot trigger a plugin or workflows
• Once a field is created as simple, you cannot convert it to a calculated field without deleting it; (something to consider when upgrading old versions)
• A calculated field cannot reference itself, but it can reference another calculated field or rollup field (limit of five chained fields)
• Values in the calculated formula can come from the current entity or a direct parent (no access to 1:N or N:N entities)
• Up to 10 unique calculated fields can be used in saved queries, charts, and visualizations
• You cannot define a maximum or minimum metadata property on a calculated field

At the time of writing, in addition to basic arithmetic operations, the Dynamics 365 supports the following built-in formulas with their respective return types:

Function syntax	Return type
ADDDAYS (whole number, date and time)	Date and Time
ADDHOURS (whole number, date and time)	Date and Time
ADDMONTHS (whole number, date and time)	Date and Time
ADDWEEKS (whole number, date and time)	Date and Time
ADDYEARS (whole number, date and time)	Date and Time
SUBTRACTDAYS (whole number, date and time)	Date and Time
SUBTRACTHOURS (whole number, date and time)	Date and Time
SUBTRACTMONTHS (whole number, date and time)	Date and Time
SUBTRACTWEEKS (whole number, date and time)	Date and Time
SUBTRACTYEARS (whole number, date and time)	Date and Time
DIFFINDAYS (date and time, date and time)	Whole Number
DIFFINHOURS (date and time, date and time)	Whole Number
DIFFINMINUTES (date and time, date and time)	Whole Number
DIFFINMONTHS (date and time, date and time)	Whole Number
DIFFINWEEKS (date and time, date and time)	Whole Number
DIFFINYEARS (date and time, date and time)	Whole Number
CONCAT (single line of text, single line of text, ... single line of text)	String
TRIMLEFT (single line of text, whole number)	String
TRIMRIGHT (single line of text, whole number)	String

See also

• Setting up the rollup fields