NEIL PARKHURST

As I prepare for my MB2-716 exam I’m producing a series of blog posts that collectively should help others revising for the MB2-716 Certification. (Microsoft Dynamics 365 customization and Configuration.) This time I will look at calculated fields.

In previous posts I’ve covered entities and fields. This post will build on that knowledge by looking at calculated fields.

A calculated field, as the name suggests, is simply a field which is derived from a calculation. The calculations can include conditions and make use of fields on the current entity and related parent entity. They can work with number fields but also date calculations and string manipulation are possible.

The best way to learn about calculated fields might be to look at an example.

Say I have an entity which contains someone’s salary pretax and in another field I hold the tax amount. I might want to calculate their total salary after deducting tax. Below you can see that I have started off by creating a new field called “Salary (After Tax)”. The field has been set as a currency data type and the field type has been set as calculated. Notice that as soon as the field type is changed to calculated the field requirement option set becomes readonly. This is because calculated fields are not directly entered by the user and therefore have no requirement level.

Next notice that an “Edit” button has appeared next to the field type.

Tip: Something to consider is the data type of the field to be returned. If my calculation is going to work with currency, for example, I will probably need to return the result as a currency. If you try to create a calculated field that returned a text field into a currency field then an error would be given and this would be prevented.

It might be important to know that once you click edit the field is actually created. And that the field type becomes read-only. Because the field type cannot be changed it is therefore not possible to convert simple fields to calculated. (If you wish to do this you’d need to create a new field!) However the formula behind the calculated fikedl can be changed at any point.

I can now use the edit option to define how the field should be calculated. Notice that calculated fields can have conditions but for now I have left these fields blank. I have simply entered the calculation I required. Which is salary – tax. Notice that as I enter my formula I get to pick the field / functions I require from a drop down.

After I accept my calculation not formatting changes to give a clear description of the calculation.

To demonstrate how this will work I have then added my salary, tax and this calculated field to my form.

Once I have saved and published my change I can test it. See below that I have entered a salary of £50,000 and a tax amount of £5,000. The system has then calculated the post-tax salary of £45,000. (Don’t you wish tax values were really this low???)

A couple of things to notice, firstly that the Salary (After Tax) field has a lock symbol. This denotes that it is read-only. Importantly you need to also be aware of when the calculation happens. Calculated fields are set when the record is saved. NOT when the data is entered.

NOTE:
The calculated value is also set when a form is opened or a value viewed in a list view.

This was a simply example, I hope you can already see that conditions could be added. To illustrate how this might work I altered the calculation. Firstly, I made it more complicated by adding an extra deductions field. That I added a condition, which was based on a two option yes/no field. In my example this was to decide if a £10,000 annual bonus should be applied or not. My calculated field now looked like this. I hope this demonstrates how you could start to make the logic a little more complex.

Date Fields

There are a number of functions available with date fields. Some add hours, days, weeks, months or years to date fields.

There are also a set of commands to allow you to subtract from dates.

Tip:
Also notice below NOW(). This will return the current date time. Useful if you want to add or subtract days from the current date!

Meaning, ADDDAYS(2,createdon) would return a date two days after a record was created.

You can also calculate the interval between two date in minutes, hours, days, weeks, months or years.

String Calculations

Calculations are also possible with strings.

Use CONCAT to combine two strings. And TRIMLEFT or TRIMRIGHT to return the start or end of a string.

Including Fields from the Parent

I mentioned in my opening paragraph that you can reference details in the calculated fields from parent entities. To do this in your formulae enter the name of the parent entity and then press “.”, this will present you with a list of fields from the parent entity that can be included in the calculation.

This is actually sometimes useful to show data directly from the parent record on the child record. By way of an example, I have created a text field that contains the account name and city from the parent account on my entity.

On my Dynamics 365 form you can see the result below.

Note:
Changes to the details on the account record do mean this calculated field on the child entity are updated. Meaning this information will stay in step.

Tip:
Another tip is that a calculated field can include other calculated fields!

Hopefully this post has given you the information you need to understand calculated fields in preparation for your MB2-716 certification.