The calculated fields feature in Microsoft Dynamics 365 Customer Engagement (D365 CE) is a powerful tool to allow you to automatically calculate a value based on user-provided or system-driven data.
For example, let us say on an Opportunity you want to calculate the difference between the budgeted amount and the actual amount generated from the Opportunity Product lines. Calculated fields will let you do that. Not only can calculated fields do simple calculations, they also can utilize if/then conditions and more advanced formulas such as date/time. You can set a calculated field to find the difference between two date fields in Days, Months, Hours, Years, and Minutes.
Using our above example, we want to calculate the difference between the budget and actuals, but let us say we want to make that value 0 if our opportunity is lost. In our formula for the calculation, we can add an if condition that says if the opportunity is lost, set our calculation to 0.
However, there are some considerations to take into place when using a calculated field. The value in a calculated field cannot be manually overridden. The field by default will be read only, and only the calculation can be populated. The fields involved in the calculation also must have values present to calculate a result. For example, you want to add up three cost fields, if two of those costs’ fields contain a number and one does not, the calculation will not run. So, make sure all the fields are either populated by a user or the system. Calculated fields can be created on field types of single line of text, option set, two option, whole number, decimal number, currency, and Date and Time.
How to create calculated fields
- To create a new calculated field, navigate to the entity you want to create the calculation on and select “+ Add column.”
- Give your field a name and select one of the appropriate data types for a calculated field under “Data type.”
- Next in the Calculated or Rollup, select “+ Calculation.”
- Once you select Calculation, it will ask you if you want to save your field. Select Yes, and after a moment the calculated field conditions will open in a new dialog. (If you don’t see the dialog box, make sure pop-ups are enabled.)
- From this screen you can add a condition you would like to determine what action should be taken. Note: Conditions are not required, and if not used, the Action will run on this field no matter what.
- Conditions in calculated fields operate similarly to those in Advanced find and Quick find. You can select the entity and the field rules you would like to add. You can have up to 5 conditions in one calculated field and they can be grouped as either AND/OR statements.
- Select the check box once you are finished editing your condition. If there is an error in the condition, it will let you know when you try to select this box. If you would like to delete a clause at any point you can highlight it and select the X.
- Next, add your Action. Depending on the data type of the field you created it will determine what actions you can insert.
- For example, if your field is an option set, you will only be able to put in actions that set the field to one of the existing option set values. Currency fields and Whole number fields on the other hand allow you to add and subtract numbers and other functions. In our example we want to subtract the difference between the budget amount and the estimated revenue. So, we simply select one of the fields, add the minus sign and select our second field. Once completed, select the check box and the calculation has been saved.
Now I can add this field to my form and watch the calculation run!
Have any questions about adding calculated fields in D365 CE or regarding D365 CE in general? Please contact us at any time!
