Let's insert the calculations
In this chapter, we will add to our order some calculations that should be performed automatically. The goal is to calculate the price based on the quantity of each item that will be placed and the total price of the order.
First, we will have to create the fields that will contain the calculated values within the various tables. An important rule to remember is that every calculation we will perform must have a field where to enter the result, this will be used to control every single step calculated by the program.
Create a field of type Currency and with name “Price” in the table Items.
Create a field of type Decimal number and with name “Qty” inside the orders_rows table. It will be the field that contains the quantity of the item to be ordered.
Create a field of type Currency and with name “Price” inside the orders_rows table.
Create a currency type field with the name “Total price” within the orders_rows table.
You should be in a situation like this when the fields are completed:
Change the code field of the orders_rows table so that when we choose an item, the price is also filled in. We then double-click on the field and set the macthes by adding the price.
Once you have set the matches, click OK and save the changes made in the field.
After creating the fields, we can enter our counts. Since the calculations are linked to the tables we will have to modify orders_rows and then Orders.
Go to the Tables page, double-click on the orders_rows and click on Settings in the Calculation Expressions parameter.
We add a new row and, in the left column, we select the Total price field, the one that will contain the result, while in the right column we write [price] * [qty]. It is important to write exactly this way because the square brackets inform the program that those are fields and therefore it will have to retrieve their current value before performing the operation.
Click OK, save, and try to make a new order now.
You can see that there are the new fields that we’ve created within orders_rows.
Add a line and enter values into Qty and Price to see the total price automatically calculated. You can also write a value inside Total Price as the field is editable, but it will be changed automatically as soon as one of the fields called by the expression is changed.
Remember that expressions are executed in the order in which they were entered and are all recalculated as soon as a field they call is changed.
Now we insert the field Total price on the order following another way: by inserting the field directly from the scheme editor.
Open the default scheme of the Orders table, open the Actions menu, select Create field and finally select the item Bottom right Detail.
After that, create the field type Currency and name “Total price”. Save the field, save the pattern and close it.
Now let's go and insert the calculation into the Orders table. As you can see in the right column, you can find the fields of both orders and orders_rows because you can perform calculations by retrieving values from additional tables.
Add the row and select the Total price field on the right column, while clicking on the Total price of orders_rows within the table on the right.
The expression has a particular format – it tells the program to recover the sum of the Total Price field. It’s not possible to recover a value of a specific row from an additional table, but, in this case, only the sum of the column.
If you need to retrieve the value of a specific row, you will need to enter a LUA script that will perform the calculation.
As always, click OK, save and let’s try our order.
From now on, by adding more rows and entering the values in the Quantity and Price columns, we will see the total price of the order recalculated from time to time.