I am creating a series of blog posts that collectively are designed to help anyone preparing for the Microsoft Dynamics 365 Customer Engagement Core exam. (aka MB-200) In this post I will look at concepts around calculated fields.

You can see below that we have a section of the exam which covers managing entities and data. Within this section needing to know how to create and modify fields is referenced. Whilst this statement doesn’t explicitly reference calculated fields I think it should be a good guess that we don’t need to just know about simple 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. For this I am going to be using the entity maintenance options found within make.powerapps.com.

Calculated Fields Example

Say I have an entity which contains someone’s salary and in another field I hold the tax amount they should pay. I might want to calculate their total salary after deducting tax.

Firstly I created salary and tax fields. These were “just” simple currency fields. Next I created a field called “Salary (After Tax)” I wanted that to be a calculated field. So, after setting the data type to currency I used the “Add” option to add details of my calculation.

Having clicked “Add” you may see a message like the one shown below. If you do you’ll need to click save to save any changes to your entity. This happens because the field must be saved before you can edit its associated calculation.

Next a popup dialog will show allowing me to define a calculation. In my example I simply entered the calculation as salary pretax less tax.

Note:
You can see below that my schema names are shown in the calculation section. You can actually type the display name to find these fields, so entering these details is real simple! Also, after entering a valid calculation you will need to select the “tick” icon to commit your change.

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 or other data type then an error may be given and this could be prevented.

To demonstrate how this will work I have then added salary, tax and my 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 £10,000 and a tax amount of £2,000. The system has then calculated the post-tax salary of £8,000.

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 may also 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 and else actions 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. I’ve also 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 lookup to 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 really 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.

You can see the result below from my Dynamics 365 form. I now have a field on my custom entity including the account name and city. This field is populated as a by product of entering (and saving) a value in my account lookup field.

Note:
Really usefully … changes to the details on the account record do get reflected in my calculated field. Meaning the information on the parent and my child entity 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 MB 200 certification. As always I encourage you to actually create a few calculated fields as part of your revision. Enjoy.