Create and use calculated fields

  • 7 minutes

Calculated fields are fields where the value is calculated instead of stored in the database. Calculated fields can be implemented by using a special field property, the FieldClass property. Because it's a field property, you must define the FieldClass property for each field in the table.

The FieldClass property can be one of three values:

  • Normal

  • FlowField

  • FlowFilter

By default, every field that you create has a FieldClass value of Normal. A Normal field contains data that is stored in the database. Most fields in the Business Central database are normal fields.

With FlowFields, your data isn't stored in a table, but it is calculated. When you need to calculate something, a calculation formula is used. When you create a field of type FlowField, you must also provide a formula in the CalcFormula property. The calculation formula is stored in the database.

You can have different types of calculation formulas:

  • Sum - The sum of a specified set in a column in a table (data type: decimal)

  • Lookup - Looks up a value in a column in another table (data type: any)

  • Count - The number of records in a specified set in a table (data type: integer)

  • Exist - Indicates whether any records exist in a specified set in a table (data type: Boolean)

  • Average - The average value of a specified set in a column in a table (data type: decimal)

  • Min - The minimum value in a column in a specified set in a table (data type: any)

  • Max - The maximum value in a column in a specified set in a table (data type: any)

The third property value is FlowFilter, which is designed to be used in the calculation formula of a FlowField. It holds a temporary value, which is used for filtering in the calculation formula. Instead of a developer predefining a fixed filter, a FlowFilter allows the end user to provide a value that is then used in the calculation.

Calculated fields example

Watch the following video to see some examples of calculated fields.