Calculation expressions

Calculation expressions tell the program which calculations are to be performed on the table. The calculations are always carried out sequentially, so it is important to correctly define their ordering.

The expressions are executed every time one of the fields shown inside them is modified. The expressions, however, only act within the current detail and any of its additional tables.

To manage the expressions click on Set and display the designated screen.

In the right-hand column you will see the field where the result of the operation will be saved. The actual expression of the calculation will be created in the central column. The left-hand column contains all the fields of the table and additional tables that can be used within operations.

The fields are always enclosed in square brackets [].

If the field is enclosed in braces {} it means that the requested field is not located inside the table, but is a field belonging to the parent table (therefore not an additional table).

Examples of calculation expression

[total_price] = [total_sale] - [total_discount]

The operation sets the [total_price] field by subtracting [total_discount] from [total_sale].

[production_time] = ([qty_requested] * [work_hours]) / [number_machines]

The expression shows how to set up a calculation with round brackets.

[total_price] = [total_sales] - ([total_sales] / 100) * [percentage_discount]

The expression sets the [total_price] by calculating a percentage discount on the total sale.

[total_price] = sum([invoices_rows.total_price])

The value is retrieved from the sum of the [total_price] column shown in the invoices_rows table. The SUM function can be used only from the detail to the additional table and not vice versa. The reason is that the detail cannot access a single value on a specific row contained in the additional table (invoices_rows). It can therefore only request a single value, in this case the sum of the column.

[total_price_gross] = sum ([invoices_rows.total_price])

[total_price_ net] = [total_ price_gross] - ([total_price_ gross] / 100) * [percentage_ discount]

First we set a partial value in order to be able to view the gross on [total_price_gross]. This value was then used to calculate the net value.

[total_gross_row] = [price_single_row] * [Quantity]

[total_net_row] = [total_gross_row] - ([total_gross_row] / 100) * {customer_discount}

In this case the expressions are inside the row of the additional table. The {discount_customer} field written between braces informs the program that the value is not present on the row but must be retrieved from the parent detail (if any). Therefore, with each row added to the invoice, the program will recover the discount set on the head data. By changing the customer discount on the invoice, all the lines present will be recalculated with the new value.

[total_VAT] = if([VATexemption]=1?0:sum([invoices_rows.total_VAT]))

A conditional is used in the operation to be able to set a value based on a choice. The operation, prior to setting [total_VAT], checks the value of the field [VATexemption]. If the value of the field is 1, then [total_VAT] is set to 0, otherwise it is set to the sum of the [total_VAT] column in the invoices_rows table.

[total_days] = days([end_date] : [start_date])

[total_hours] = hours ([end_date] : [start_date])

These types of expressions are used to extract the hours or the number of days starting from two dates.