Here I will continue my series of posts connected with the MB2-712 Certification. (Microsoft Dynamics CRM2016 Customization and Configuration) In this post 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 as you’d expect but also date calculations and string manipulation is possible.
The best way to learn about calculated fields might be to dive straight in and 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 (Post Tax)”. The field has been set as a currency data type and the field type has been set as calculated. At this point I have to save the field.
Saving the new field will do a couple of things. Firstly, notice that the field requirement field is now read only. Calculated fields are not directly entered by the user so the requirement level is not important.
Next notice that an edit button has appeared next to the field type.
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.
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.
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 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 (Post 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.
Below you can see the result I obtained after publishing my change. I awarding myself a £10k bonus. J
The process I followed here does also demonstrate that the formulas used in calculated fields can be changes.
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.
Tip:
Another tip is that a calculated field can include other calculated fields!
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.
Hopefully this post has given you the information you need to understand calculated fields in preparation for your MB2-712 certification.
Next time I will look at rollup fields. J
NEIL PARKHURST 
Leave a comment