Working with Calculated Fields in Dynamics 365 Customer Engagement

Calculated fields are the fields for which the value can be automatically calculated using some conditions and formulae in design time. The value will be automatically assigned to the field once the conditions are satisfied.

For example, if I want to have a functionality to store the next birthday automatically of a specific contact after the birthdate field value is supplied then without writing any custom code I can use the feature Calculated field to auto calculate the next birthday and store the value.

Let’s see how we can build this taking the above scenario as an example. Follow the steps below to complete the task. After the steps I will discuss more concepts, limitations and facts on calculated fields.

If you want to watch video then click the below video otherwise scroll down to read more.

Step-1: Choose your solution

Go to Advanced Settings and Open Solutions for which I want to do the customization.

Step-2: Add Contact entity to it without other assets.

Now we have to add the entity to it & click ok then click finish.

 Step -3: Add a new field named “Next DOB” whose Data Type is Date and Time. Also select the Field Type Calculated. Format as Date Only. Behavior as User Local.

Once it’s saved. Click on Edit button besides Field Type.

Step-4: Now define the condition and Actions

Here we want to only calculate if there is a value in Birthdate field so we have to add a condition like below.

Once the condition added click on the tick mark. We can also add multiple conditions using Grouping such as AND/OR. But for now we do not require any new condition.

In Action we have to select AddYears formula and pass 1 as first parameter and the Birthday field as second parameter. Remember that the Date fields Behavior must be same for Birthday field and Next Birthday Field.

After the condition and Action is built now save it and publish the solution. We can also add the Else condition if you want but for now we will not add any condition in Else. Now place the new field on the form and publish he solution.

Now Publish all Customization.

Now we are ready to test. Open a Contact record and go to Details TAB. Provide the values for Birthday field then the Next Birthday is automatically populated.

NOTE: If you open an existing record the Next Birthday field is populated with correct value.

For new records the field will populated once you save the record. That’s it.

Now we will see some facts about calculated fields.

The calculated field capabilities:

• The calculated fields comprise of calculations that use the fields from the current entity or related parent entities.
• The expression support is available on the current entity and the related parent entity fields in the Condition sections and the Action sections.
• The built-in functions include:

ADDHOURS, ADDDAYS, ADDWEEKS, ADDMONTHS, ADDYEARS, SUBTRACTHOURS, SUBTRACTDAYS, SUBTRACTWEEKS, SUBTRACTMONTHS, SUBTRACTYEARS, DIFFINDAYS, DIFFINHOURS, DIFFINMINUTES, DIFFINMONTHS, DIFFINWEEKS, DIFFINYEARS, CONCAT, TRIMLEFT, and TRIMRIGHT.

• A rich conditional support provides branching and multiple conditions. The logical operations include AND & OR operators.
• The visual editing capabilities include modern user interface and intellisense in the ACTION section.
• A seamless integration of the calculated fields with the forms, views, charts, and reports is available in real time.
• If you updated your online organization to December 2016 Update for Dynamics 365 (online), you can configure calculated fields to use custom controls.

Scenarios of the calculated fields

• Weighted Revenue: Estimated revenue multiplied by probability
• Net Worth: Assets subtracted by the liabilities for a given account
• Cost of Labor: Base rate up to 40 hours, plus additional overtime
• Contact Number: Phone number for an opportunity based on account or contact
• Lead Score: Single field that provides insights to the quality of a given lead
• Follow Up By: Follow up on an activity by a specified number of days based on priority

Calculated fields considerations

You should be aware of certain conditions and limitations when working with calculated fields:

• Saved queries, charts, and visualizations can have a maximum of 10 unique calculated fields.
• The calculated field values are not displayed in the Customer Engagement Outlook Offline mode in the tile views or on entity main forms.
• A maximum number of chained calculated fields is 5.
• A calculated field can’t refer to itself or have cyclic chains.
• If you change one of the condition operators in a multiple condition clause, all of the condition operators will update to that condition. For example, in the clause IF (x > 50) OR (y ==10) OR (z < 5), if you change the OR operator to the AND operator, then all OR operators in the clause will become AND operators.
• You can access parental fields via the Lookup field to the parent entity, such as <LookupFieldName>.<FieldName>. This is not possible with multi-entity Lookup fields like Customer which can be Account or Contact. However, some entities have individual Lookup fields for a specific entity, such as ParentAccountid.<FieldName> or ParentContactid.<FieldName>.
• Sorting is disabled on:
  • A calculated field that contains a field of a parent record.
  • A calculated field that contains a logical field (for example, address field).
  • A calculated field that contains another calculated field.
• Calculated fields can span two entities only.
  • A calculated field can contain a field from another entity (spanning two entities – current entity and parent record).
  • A calculated field can’t contain a calculated field from another entity that also contains another field from a different entity (spanning three entities):
    (Current Entity)Calculated Field <- (Parent Record) Calculated Field 1 <- (Parent Record) Calculated Field 2.
• You can’t trigger workflows or plug-ins on calculated fields.
• You can’t change an existing simple field to a calculated field. If your current application is using JavaScript or plug-ins to calculate a field, you would not be able to use the calculated fields feature without creating a new field.
• Duplicate detection rules are not triggered on calculated fields.
• A rollup 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.