Calculations

Important

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

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

With the Custom Tiles & 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.

Success

Custom calculated fields are always computed in the background. The values of custom calculated fields will only be available on the page after all calculations have finished.

Note that there is a time-out of 30 seconds, so even though the calculations are performed in the background, please make sure to optimize your calculated field definitions.

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. Even though calculated fields are computed in the background, poorly optimized field definitions could be detrimental for the user experience (e.g., users getting annoyed that custom fields take a long time to load).

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.

Filter Set¶

In the filter sets you create a filter on which the value of your custom tile is being determined. The value in the Code field is being determined automatically but you can edit it if you want to. Give your filter set a recognizable Description. The fields Filter Table ID and Filter Table Caption are filled in based on your custom tile setup. In the Key ID field choose the right Key. The Key Name field shows the caption of your choice in the Key ID field. Key Order defines if the sorting is ascending or descending. In filters you can set up your own filters based on the fields in the filter table. You can use multiple filter options in the filter string field:

Filter Token	Description
`TODAY` or `today` or `T` or `t`	Returns the date of today as a date value
`YEAR` or `year` or `Y` or `y`	Returns a filter for the current year value
`WORKDATE` or `workdate` or `W` or `w`	Returns the workdate as a date value
`YESTERDAY` or `yesterday`	Returns the date of yesterday as a date value
`TOMORROW` or `tomorrow`	Returns the date of tomorrow as a date value
`WEEK` or `week`	Returns the first and last day of the current week as a date filter value (eg. 6-10-2025..12-10-2025)
`MONTH` or `month`	Returns the current month as a date value (eg. 1-1-2025..31-1-2025)
`QUARTER` or `quarter`	Returns the current quarter as a date filter value (eg. 1-1-2025..31-3-2025)
`CW` or `cw`	Returns the last day of the current week as a date value
`CM` or `cm`	Returns the last day of the current month as a date value
`CY` or `cy`	Returns the last day of the current year as a date value
`ME` or `me`	Returns the user who runs the page as a value
`USER` or `user`	Returns the user who runs the page as a value
`USERSECURITYID`	Returns the GUID of the user who runs the page as a value
`COMPANY` or `company`	Returns the value of the Company Name from the Companies table as a value

Additionally, the app supports most of the default Business Central filter options as described in the standard documentation (see the Sort, search and filter documentation on Microsoft Learn for some examples)

Enum/Option fields are based on enum filtering, when you fill in for example Order in the Document Type field in table Sales Line the system automatically recognizes that this is option 1 which is the ordinal/numeric value for Order.

Important

Filtering options make it possible to create a dynamic date filter For example if you want to see the due quotes from the current week you can use the following filter: cw-1w+1d..cw. When the current date is October 10, 2025 the filter returns the following date value: 6-10-2025..12-10-2025.

Important

Date filters must always be in English as mentioned above. This prevents issues when users work in another language.

Display Type¶

The custom calculated fields can also be shown in the Custom Tiles factbox on the card and list pages. You can change the appearance of a calculated field by changing the Display Type setting in the calculated field setup.

The following Display Type options are available for displaying custom calculated fields:

Display Type	Description
Page Field	The custom field is shown on the card page as a field.
FactBox Field	The custom field is shown in the Custom Tiles factbox as a factbox field.
Tile	The custom field is shown in the Custom Tiles factbox as a tile/cue. Only supported for Decimal and Integer fields.
Page Field and Tile	The custom field is shown in the Custom Tiles factbox on the card and/or list page as a tile and on the card page as a field. Only supported for Decimal and Integer fields.

Drill-Down¶

You can use the settings in the Drill-Down group of the Field Setup page to configure what happens when users click on the field or tile.

Open Card or List Page (Default)¶

This Drill-Down Behaviour opens either the card page or the list page depending on the number of records resulting from the filters:

If the filters result in a single record, then the card page will be opened for that record when clicking on the field/tile.
If the filters result in zero or multiple records, then the list page will be opened (with the filters from the filter set applied).

If you do not specify a card/list page explicitly, then the default card/list page for the filter table will be used. If you wish to customize which card and list page will be used, then you can specify a specific card and/or list page to use.

Open List Page¶

This Drill-Down Behaviour opens the list page, with the filters from the filter set applied to it.

If you do not specify a list page explicitly, then the default list page for the filter table will be used. If you wish to customize which list page will be used, then you can specify a specific list page to use.

Possibility to add new options

The Drill-Down Behaviour options can be extended by a developer (by extending the WSB_CFDrillDownBehaviour enum with a new option). For example, you could add a new Hyperlink option which will open a specified URL when clicking on the field/tile.

If you have suggestions/ideas for new drill-down behaviours that you think should be supported by default, then please reach out to the Apportunix support team to share these ideas with us!

Conditional Styles¶

For Decimal and Integer fields it is possible to configure the style/indicator color that is applied to the field/tile using the Conditional Styles tab on the Field Setup page.

The conditional styles are processed based on their Entry No. The style of the first record for which the value falls in the specified filter is applied to the field/tile.

For example, if you have a field/tile to display the number of Due Sales Quotes, it is possible to define a Style (colour) when the count reaches a specific count.

Filtering is possible based on the standard Business Central filtering syntax (e.g. 1100..2100 for numbers 1100 through 2100, ..2500 for number up to and including 2500, <> for not equal to, > for "greater than", < for "less than", etc.). For more information about the Business Central filtering syntax, please see the Sort, search, and filter data in lists, reports, or XMLports on Microsoft Learn.

Tip

For fields displayed as tiles, you can click on the Preview Tile on the right to change the sample value to a different value. This way you can test which style will be applied for the sample value that you entered.

The tile in this page part looks a bit different than the tile in your role center. This is something that is 'by design' in Microsoft Dynamics 365 Business Central.

Decimal Format¶

For Decimal fields you can customize the formatting, with the following options available:

Format Type	Description
Default	The default formatting for decimal fields in Business Central.
Amount with Currency Symbol	The decimal field is formatted with a currency symbol. The currency symbol that is used is that of the Local Currency defined in the General Ledger Setup. The decimal places/points are taken from the Amount Decimal Places (LCY) setting in the General Ledger Setup. It is possible to override the Currency and/or Decimal Places that will be used for the formatting of the field.
Unit Amount with Currency Symbol	The decimal field is formatted with a currency symbol. The currency symbol that is used is that of the Local Currency defined in the General Ledger Setup. The decimal places/points are taken from the Unit Amount Decimal Places (LCY) setting in the General Ledger Setup. It is possible to override the Currency and/or Decimal Places that will be used for the formatting of the field.
Percentage	The decimal field is interpreted as a percentage. A value between 0 and 1 is expected. For example, the value "0.42" will be displayed as "42%".
Custom	Use a custom Expression to define the formatting for the decimal field. For more information on the syntax for these expressions, please consult the Formatting decimal values documentation on Microsoft Learn.

Negate Value¶

For Boolean, Decimal and Integer (Number) fields you can negate/invert the calculation result value by enabling the Negate Value setting on the Field Setup page.

For a Boolean field, false will become true and vice versa.

The setting is available when the Calculation Type = Exists.
For a Decimal or Integer (Number) field a positive value will become a negative value and vice versa.

The setting is available when the Calculation Type = Sum.

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:

Open the Custom Fields Definitions page.
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.
With the new custom field selected, invoke the Calculations action to open the Table-Specific Field Setup page for the custom field.
Change the Field Class from Normal to Calculated
Set Calculation Type to First, as we want the calculated field to use the first record within our filters.
Set the table to use to table 36 "Sales Header", as this table contains the headers of (unposted) sales documents, including sales orders.
Set the field to use to field 3 "No.", as we want the calculated field to retrieve the "No." of the first Sales Order.
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.
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.
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 as page fields.

However, you can make calculated fields visible on the Custom Tiles factbox as FactBox Field or Tile for the selected record on the list page. For this, change the Display Type of the calculated field to FactBox Field, Tile or Page Field and Tile.

For example, you can define custom calculated fields for the Sales Line entity and view the calculation results in the Custom Tiles factbox on the Sales Lines list page:

"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

"Tile Due Sales Quotes on Customer Card"¶

In this example we are going to look at adding a Due Sales Quotes tile for entity table = Customer. The tile must show a count of Sales Quotes that are past due for quotes where the selected customer is the Sell-to Customer. When clicking on the tile, the drill down must always go to the Sales Quote List Page with an overview of the filtered values:

Set up a custom calculated field:
- Entity = Customer
- Type = Integer (Number)
- Field Name = Due Sales Quotes
Click on Calculations
Set Field Class = Calculated
Set Display Type = Tile
Set Calculation Type = Count
Table ID is 36 (Sales Header)
Create the following Filter Set:
- Code is suggested automatically, but the recommendation is to choose a descriptive code, e.g., CUSTOMER_DUESALESQUOTES
- Give the filter set a recognizable Description, e.g., "Customer Due Sales Quotes"
- In this case we choose under Index the Key ID 1 which is Document Type,No.
- Select the right Filters, in this case the following:
  - Field ID 1 Document Type is the Constant Value = 0 (Quote)
  - Field ID 2 Sell-to Customer No. must be linked to Entity Field ID = 1 (Customer No.)
  - Field ID 152 Quote Valid To Date with Filter String = ..TODAY
Under Drill-Down you must specify the right Behaviour, in this case we always want to open the "Sales Quote List" page, so the Behaviour in this case must be Open List Page, as the List Page you can select ID = 9300 "(Sales Quotes").
Now the Customers list page and the Customer Card page have a new tile called Due Sales Quotes visible in the Custom Tiles factbox.

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: October 20, 2025