Calculated Fields in Dynamics 365

Spread the Knowledge...

In this post we will discuss about how to create and use the calculated fields in CRM 2015/2016/D365.

Microsoft has introduced new feature in CRM 2015 as Calculated fields.

This calculated fields can be used to do simple calculations itself in the field by using the required conditions.

It’s important to know some basic things about the ‘Calculated fields’.

  1. The calculations made in this field can’t be saved in Database. It worked as a virtual field.
  2. You can’t refer the calculated field to itself. However, you can refer other normal fields, calculated fields and Rollup fields to it.
  3. Calculations done in calculated fields is based on AND and OR conditions only.
  4. No workflows, Plugin will trigger on update of calculated field.
  5. As it’s called as virtual field, you cannot use its value in plugin through context.
  6. Calculated fields cannot be displayed in Outlook Offline mode.
  7. Duplicate detection rule cannot be triggered on Calculated fields.
  8. 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.
  9. Saved Queries, Charts and visualizations can have a maximum of 10 unique calculated fields
  10. Sorting is disabled on:
  11. A calculated field that contains a field of a parent record.
  12. A calculated field that contains a logical field (for example, address field).
  13. A calculated field that contains another calculated field.

Steps to create Calculated field in form.

Example 1: Calculated field with Decimal Number type.

Step 1:

Create a new field in CRM form with Data Type as Decimal Number and Field Type as Calculated.

Pic 1 - Calculated Fields in Dynamics 365

Step 2:

Click ‘Edit’ Button next to Field Type to configure the rule in calculated field.

Write the expression in the popup window.

Before writing an expression make sure that:

  1. You have selected a calculated field with correct format. Ex. If the expression is written to calculate two decimal values, then it should be stored in calculated field with same format.
  2. Check the specific predefined functions of calculated fields to complete your calculation.
Pic 2 - Calculated Fields in Dynamics 365

Step 3:

Add the new field in the form then save, close and Publish.

Step 4:

Verify the calculation.

Pic 3 - Calculated Fields in Dynamics 365

Example 2: Calculated field with Data and Time type.

Step 1:

Create a new field in CRM form with Data Type as ‘Date and Time’ and Field Type as Calculated.

Pic 4 - Calculated Fields in Dynamics 365

Step 2:

Click ‘Edit’ Button next to Field Type to configure the rule in calculated field.

Write the expression in the popup window.

Pic 5 - Calculated Fields in Dynamics 365

Step 3:

Add the new field in the form then save, close and Publish.

Step 4:

Verify the calculation.

Below are some predefined calculated field functions for ready reference.

Function SyntaxData typesDescriptionReturn type
ADDDAYSwhole number, date and timeReturns a new date and time that is equal to the given date and time, plus the specified number of days.Date and Time
ADDHOURSwhole number, date and timeReturns a new date and time that is equal to the given date and time, plus the specified number of hours.Date and Time
ADDMONTHSwhole number, date and timeReturns a new date and time that is equal to the given date and time, plus the specified number of months.Date and Time
ADDWEEKSwhole number, date and timeReturns a new date and time that is equal to the given date and time, plus the specified number of weeks.Date and Time
ADDYEARSwhole number, date and timeReturns a new date and time that is equal to the given date and time, plus the specified number of years.Date and Time
SUBTRACTDAYSwhole number, date and timeReturns a new date and time that is equal to the given date and time, minus the specified number of days.Date and Time
SUBTRACTHOURSwhole number, date and timeReturns a new date and time that is equal to the given date and time, minus the specified number of hours.Date and Time
SUBTRACTMONTHSwhole number, date and timeReturns a new date and time that is equal to the given date and time, minus the specified number of months.Date and Time
SUBTRACTWEEKSwhole number, date and timeReturns a new date and time that is equal to the given date and time, minus the specified number of weeks.Date and Time
SUBTRACTYEARSwhole number, date and timeReturns a new date and time that is equal to the given date and time, minus the specified number of years.Date and Time
CONCATsingle line of text, single line of text, … single line of textReturns a string that is the result of concatenating two or more strings.String
TRIMLEFTsingle line of text, whole numberReturns a string that contains a copy of a specified string without the first N-characters.String
TRIMRIGHTsingle line of text, whole numberReturns a string that contains a copy of a specified string without the last N-characters.String
DIFFINDAYSdate and time, date and timeReturns the difference in days between two Date and Time fields. If both dates and times fall on the same day, the difference is zero.Whole Number
DIFFINHOURSdate and time, date and timeReturns the difference in hours between two Date and Time fields.Whole Number
DIFFINMINUTESdate and time, date and timeReturns the difference in minutes between two Date and Time fields.Whole Number
DIFFINMONTHSdate and time, date and timeReturns the difference in months between two Date and Time fields. If both dates and times fall on the same month, the difference is zero.Whole Number
DIFFINWEEKSdate and time, date and timeReturns the difference in weeks between two Date and Time fields. If both dates and times fall on the same week, the difference is zero.Whole Number
DIFFINYEARSdate and time, date and timeReturns the difference in years between two Date and Time fields. If both dates and times fall on the same year, the difference is zero.Whole Number

Hope this helps!!!


Spread the Knowledge...