Introduction to calculation columns

  • 6 minutes

A calculation column lets you define a calculation formula that is run by Microsoft Dataverse regardless of the form that is used to edit or add data. Calculations allow you to improve data integrity and simplify form development. Unlike asynchronous rollups that calculate based on a scheduled job, calculations run in real time when the row is saved.

Calculated columns are powerful, and have the following key considerations:

  • Calculated columns use the columns from the current table or related parent tables from a many-to-one relationship.

  • Calculated columns are read-only.

  • The expression support is available on the current table and the related parent table columns 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

    TRIMRIGHT

  • You can define branching and multiple conditions. The logical operations that are available include AND and OR operators.

  • The following column types support calculations:

    • Text

    • Option Set

    • Two Options

    • Whole Number

    • Decimal Number

    • Currency

    • Date Time

  • The visual editing capabilities include intellisense when you define the calculation in the Action portion of the column.

  • You can configure calculated columns to use custom controls.

Calculated column limitations

  • If the calculated column depends on another value, it will not be recalculated until the row is retrieved.

  • You cannot use values in calculated columns that reference a related table, another calculated column, or a logical value in the same table to sort data that is returned by a query. Though your query can specify that the results should be ordered by using a calculated column, the sort direction will be ignored and will not throw an error. If the calculated column references only simple values in the same row, sorting works normally.

  • Only columns from an immediate parent table can be used in a calculated column.

  • Calculated columns can reference other calculated columns in their formula, but they cannot reference themselves.