How to Use Formula Columns in Dynamics 365 CRM

You can use Formula Columns in your Microsoft Dynamics 365 CRM solution to automate calculations and special processes.

I have used this type of column to help clients automatically:

• Calculate an expiry date based on an effective date
• Use an expiry date to determine a record’s status
• Reference columns from a related record

Previously, some of the same needs could be addressed with Calculated Fields. However, Microsoft plans to discontinue Calculated Fields, with Formula Columns being the new way to handle needs for automated calculations, etc. (Microsoft has also renamed “fields” as “columns.”)

In this article, I’ll show you the current way to meet these needs in your D365 CRM system with Formula Columns. You can instead read legacy instructions for Calculated Fields here.

Table of Contents

• How to Create a Formula Column
• Limitations of Formula Columns
• Formula Columns vs Calculated Columns

Note: There are now 5 different Dynamics 365 apps that serve various CRM needs. In some contexts, you may also see those products referred to as “Customer Engagement” or “CE” apps. Formula Columns work the same no matter which of those apps you’re using.

How to Create a Formula Column

It’s important to know that Formula Columns utilize Power Fx with a wide range of functions that can be used for all types of scenarios: simple or complex, calculations or logic. Depending on your location, you may have a Preview option for formula suggestions to leverage AI to create formulas from natural language!

When creating a Formula Column, the type of column is determined automatically based on the formula result. Once saved, the actual formula can be updated; however, the column type cannot be changed.

When configuring the formula, columns matching the text entered will display for selection. The options will list columns in the same table. Choice Columns will be prefixed with the ‘@’ character:

To reference a specific choice option, select the Choice Column, then follow that with the ‘.’ character, which will display the options available:

The same can be done to reference columns for related Lookup Columns:

Example 1. For the first example, we have a need to reference the Category associated with the Membership selected in order to create a chart of Accounts by Category. Adding a Formula Column ensures the value is always current with quick access to retrieve it, without having to reference the related Membership.

For reference, here is the same result using a Calculated Column:

Example 2. For the second example, we have a need to calculate the Membership Expiry Date to one year less a day from the Effective Date entered. The system will handle the error if the Account does not have a Membership Date entered and display a blank.

Formula Columns are more flexible working with Dates – in this case, I have set the Membership Date and Expiry Date columns to be date only and time-zone independent so that all system users see the same date, no matter which time zone they are in.

For reference, this is the same result using a Calculated Column:

Note: Calculations will return an error if the date type and time zone settings do not match that of the column referenced in the calculation.

Example 3. For the third example, we have a need to determine the Membership Status based on the Expiry Date:

• If the Expiry Date is in the past, then the status should be “Expired.”
• If the date is within 30 days, then “Expiring Soon”
• If the date is more than 30 days in the future, then “Active.”

For this example we are setting the result to an existing Global Choice:

Although it’s possible to create the same result using a Calculated Column, it would require the creation of another column for today’s date for proper evaluation. I would not recommend that approach.

Let’s look at the results of the columns created. The Formula Columns are read-only:

Limitations of Formula Columns

Formula Columns are still evolving, and Microsoft updates capabilities frequently. That said, there are some current limitations for you to be aware of as of the time of writing.

One current limitation is that Formula Columns cannot result in a currency type or directly reference Currency Columns. However, we can reference Currency Columns for the same table by using the Decimal function.

Other limitations of Formula Columns include:

• Not all Power Fx functions are available to use within Dataverse solutions.
• Not all Formula columns can be used for sorting.
• The maximum number of chained Formula columns is 10.
• Formula Columns are read-only and cannot have cyclic chains or trigger workflows/plugins.
• Formulas can reference non-currency columns from related records, but are not currently able to aggregate child records to replace Rollup Columns – we are anxiously awaiting this functionality to be added!

Formula Columns vs Calculated Columns

The most important distinction between Formula Columns and Calculated Columns is that Formula Columns utilize Power Fx rather than a limited list of functions and thus support more complex logic.

However, unlike Formula Columns, Calculated Columns can be created with a result type of currency and reference both same and related table Currency Columns. So for the time being, you would continue to use Calculated Columns for any Currency Columns you require.

The maximum number of chained Formula Columns is 10 (twice as many as the maximum number of chained Calculated Columns, which is 5).

And once again, it’s important to bear in mind that Calculated Columns are expected to be discontinued by Microsoft. It is therefore better to build with Formula Columns whenever possible.

---

 

At Encore, we implement, advise, and support our customers as a Dynamics 365 CRM Partner. You may be interested in our articles about other special kinds of columns: Multi-Select Option Sets (aka Choices) and Rollup Fields.