Calculations

Important

To use this functionality in a Business Central Production environment a subscription for the Custom Fields - Calculated Values product is required.

You can test out this feature for free in a Business Central online Sandbox environment.

With the Custom Fields - Calculated Values product it is possible to define custom fields that can perform a custom-defined calculation (e.g., a count, sum, average, etc.) and show the output of those calculations on your pages in Business Central.

Calculated Field Setup

On the Custom Fields Definitions page you can set up calculations for a custom field by either invoking the Calculations action, or by drilling down on the Calculations field.

This opens the Table-Specific Field Setup where one can configure table-specific preferences for a custom field. The Customer entity only has a single table where custom fields are added (i.e., table "Customer"), whereas the Sales Header entity has multiple tables where custom fields are added (i.e., "Sales Header", "Sales Invoice Header", "Sales Shipment Header", "").

To change a custom field to a calculated field, one can change the Field Class from Normal to Calculated on the Table-Specific Field Setup page. When the Field Class has been changed one can configure the custom field as a calculated field to their preferences.

This includes setting the desired Calculation Type, where the currently supported values are:

Calculation Type	Description	Supported for Data Types
Sum	Calculate a sum over a certain field value.	Decimal, Integer
Count	Calculate the number of records that meet the filters / link between entity and other table.	Integer
Exists	Calculate whether there are any records that meet the filters / link between entity and other table.	Boolean
Average	Calculate an average of a certain field value.	Decimal
First	Retrieve the value of the first record that meets the filters / link between entity and other table. Can also be used for lookups.	All
Last	Retrieve the value of the last record that meets the filters / link between entity and other table.	All
Min	Calculate the minimum of a certain field value within the record filters.	Date, Decimal, Integer
Max	Calculate the maximum of a certain field value within the record filters.	Date, Decimal, Integer

The calculation is configured by choosing a Table to aggregate records from, a Value Field to get the value from (if applicable for the calculation type), and setting up a link between the entity table record and filter table by configuring a (reusable) Filter Set.

Caution

Please make sure to take performance into consideration while configuring calculated custom fields. Note that subpar configurations could be detrimental to the performance of loading records and pages in Business Central. Therefore it's important to set up calculated custom field definitions responsibly and with proper thought and care. Apportunix cannot be held accountable for performance degradations in Business Central directly or indirectly caused by custom calculated fields.

Please note that you can use the Simulate Calculation(s) action (on the Custom Fields Definitions page and Table-Specific Field Setup page) to get an indication/estimate of the time needed to calculate the output of calculated custom fields. This can be a helpful tool to help prevent and/or assess potential performance degredations from calculated custom fields.

Examples

"First Sales Order No."

In this example we are going to look at adding a First Sales Order No. field for entity table = Customer, which looks up the first sales order for the customer. To achieve this, we can follow these steps:

1. Open the Custom Fields Definitions page.

2. Set up a custom field for:

   • Entity = Customer,
   • Type = Code, and
   • Field Name = First Sales Order No.

   Please note that you may need to close your current Business Central session and open a new one before the custom field will show up with the appropriate caption/translations.

3. With the new custom field selected, invoke the Calculations action to open the Table-Specific Field Setup page for the custom field.

4. Change the Field Class from Normal to Calculated
5. Set Calculation Type to First, as we want the calculated field to use the first record within our filters.
6. Set the table to use to table 36 "Sales Header", as this table contains the headers of (unposted) sales documents, including sales orders.

7. Set the field to use to field 3 "No.", as we want the calculated field to retrieve the "No." of the first Sales Order.

   

8. Create a new Filter Set to set up a link between the Customer table records and Sales Header table records:

   

   1. Provide a Code and Description for the Filter Set, e.g.:

      • Code = CUSTOMER_SALESORDERS
      • Description = "Sales Orders for Sell-to Customer"

   2. Set up a filter to limit the results to Sales Orders only:

      • Filter Field = 1 "Document Type"
      • Constant Value = 1 (Order)

      Tip: Use the Assist-Edit () button of the Constant Value field to get the ordinal/number value of Document Type = Order for the filter.

      

   3. Set up a filter to limit the results to Sales Orders for a single customer:

      • Filter Field = 2 "Sell-to Customer No."

      • Entity Field = 1 "No."

        

   4. On the Index tab use the Assist-Edit () button of the Key ID field to select a key/index of the Sales Header table to use for this filter set.

      

      As we are filtering on the Document Type and Sell-to Customer No. field, key 3 on "Document Type","Sell-to Customer No." is the most appropriate in this case.

9. On the Table-Specific Field Setup page invoke the Simulate Calculation action to confirm the output is as expected.

   

   1. Use the Assist-Edit () button of the Selected Record(s) field to select a Customer record to test the calculation.

      

   2. Select one of the Customer records and choose OK.

      

   3. Below the Calculation Sample Output label you will now find the calculated output for the custom field.

      

      Additionally, below the Calculation Time label you will find a measurement of the time it took to calculate the output. Please note that the calculation time is dependent on multiple factors but is important to keep in mind as you should avoid that your calculated fields decrease performance of Business Central and lead to users waiting a long time for pages to load.

   4. If you wish you can repeat the simulation with other records to check if the outputs are as expected. After you are done, choose OK to close the page.

10. Finally, navigate to the Customer Card of one of the customers in your Business Central environment and find the calculated field(s) that we have added:

    

    Note that you can also drill-down on each of the calculated fields to open a page to view the calculation's related record(s).

    

Note

Custom calculated fields are not visible on list pages. This is a deliberate decision to protect users from subpar custom field definitions that could be detrimental to the performance of loading list pages.

"Qty. Shipped Not Invoiced"

In this example we are going to look at adding a Qty. Shipped Not Invoiced field for entity table = Customer, which "sums the quantity of Qty. Shipped Not Invoiced from the Sales Order lines for the customer". To achieve this, we follow the steps as described in First Sales Order No with the following additions.

• Set up a custom field for:

  • Entity = Customer,
  • Type = Decimal, and
  • Field Name = Qty. Shipped Not Invoiced

• Use the following Table-Specific Field Setup

  

• Use the following Filter Set

  

"Delayed Sales Order lines"

In this example we are going to look at adding a Has Delayed Sales Order Lines field for entity table = Customer, which "sets a boolean to TRUE if the Customer has one or more Sales Order lines which are delayed". To achieve this, we follow the steps as described in First Sales Order No with the following additions.

• Set up a custom field for:

  • Entity = Customer,
  • Type = Boolean, and
  • Field Name = Delayed Sales Order lines

• Use the following Filter Set

  

Simulate Calculations

On the Custom Fields Definitions page, you can use the Simulate Calculations action to simulate calculations for all the calculated fields of a selected table record.

This is a useful tool to directly view the calculation outputs for all of your calculated custom fields:

---

Last update: March 14, 2025