In this lesson we will learn how to make simple calculations in tables and automatically retrieve field values from linked tables using "Calculating Wizard". Besides, we will try to add a formula to our table which will calculate the sum of number-type field values in the whole subtable. This formula will be written in PHP.
To perform simple calculations with field values, our online database Basemaster employs Calculating Wizard. For example, you can make a formula which will insert the product of Amount by Price fields in Sum field. Let’s try to assign the required calculations in Order items subtable which we created during Lesson 4.
Taking values from linked fields
First of all we need to ensure that when you select a product from the price list, the relevant fields are inserted in Units of measure and Price. To do it, open the settings of Units of measure field. They have Calculating Wizard (or Calculate designer) option. The drop-down list will offer 2 fields: Number which is linked to Orders table and Name which is linked to Price table. Using these two fields you can get access to other fields of linked tables.
Since we need to insert the value from Price table into Units of measure field, select Name (Price-list). After the dot, one more list will show up. It contains fields of the linked table whose type corresponds with the field we are customizing. In our case this is Units of measure field. Select it and save the field settings.
Now, after you select the product name from Price, the unit of measure of this product will be automatically inserted in Units of measure field of Order items table.
Do the same with Price field. Now, more options are available in the drop-down list of Calculating Wizard because Price field belongs to number type. It means that we can create formulas for it, using other number-type fields from Order items table (Amount, Sum) as well as from linked tables Orders and Price-list. We need to take the value from Price, so select "Name (Price-list)" and then Price field.
Note that an extra list emerged showing arithmetical operators which you can use in further calculations. Use this function while customizing Sum field.
Calculations
Open the settings of Sum field. Select "Price" "*" "Amount” in Calculating Wizard (Calculate designer).
Click Save. Now, when you change Price or Amount fields, Sum field will be automatically recalculated in your online database.
Now, let’s check, how it all works. Select Orders table and add a new order. Then add a row in Order items subtable. Select a product from Name field, after it "Units of measure" and "Price" must be automatically filled. Enter the relevant Amount, and Sum field will show you the required value.
Calculating a sum of values from all rows of the subtable
The only thing that’s left is to prepare a formula that will calculate the total amount of the order. It should be noted, that it’s not that easy but this is very often required. This kind of calculations are written in PHP language. Using sample calculations which are included in the standard configuration of Basemaster program and supporting documents, you can try to make up formulas without having special skills.
Open the settings of Order items => Computation.
Here you will see three types of calculations that we created using Calculating Wizard. Now let’s add a new one.
Name it "Amount of Order". Set the Calc conditions “Field changed" and "Sum”
Copy the following code to the clipboard:
if ($line['Sum'])
{
$sqlQ = "SELECT SUM(`f1207`) AS sum FROM ".DATA_TABLE."95 WHERE `f1195`=".$line['Number']['raw']." and status=0 and `id`<>".$ID;
$pre = sql_query($sqlQ) or die(mysql_error());
$row = mysql_fetch_assoc($pre);
if ($row['sum']===NULL)
{
$line['Number']['Sum']=$line['Sum'];
}
else
{
$line['Number']['Sum']=$row['sum']+$line['Sum'];
}
}
Paste this code in Computation. Click Save. So the code is saved in your online database now.
Then open this type of calculation for editing. The following window will be displayed.
Now it is necessary to edit this code based on the structure of your online database.
Every table and field in Basemaster program has its own unique number. In PHP, these numbers are used to access tables and fields. They are defined in the settings of a table or field. Besides, these numbers are shown in the URL bar of your browser.
"table=110" means that Order items table has been assigned number 110 and "field=1210" means that Sum field has been assigned number 1210. Numbers of tables and fields in your online database will be different from those shown in this example.
In the formula “Amount of Order” it is necessary to edit the following string:
$sqlQ = "SELECT SUM(`f1207`) AS sum FROM ".DATA_TABLE."95 WHERE `f1195`=".$line['Номер']['raw']." and status=0 and `id`<>".$ID;
Instead of `f1207` insert the number of your Sum field of Order items table. Besides, change 95 to your number of Order items table and then do the same with `f1195` (Number field of Order items table).
Save this formula and make sure it works: add an order, then enter some products and specify their amount. Sum field must show the total amount of all order items.
But that’s not all. We need to ensure that the amount of the order is recalculated in the event one or more of its positions are deleted. To do it, make one more formula. Let’s follow the previous steps. Add Amount of order after deletion to Order items table. Set the Calc condition: Delete line.
Copy the following code:
$sqlQ = "SELECT SUM(`f1207`) AS sum FROM ".DATA_TABLE."95 WHERE `f1195`=".$line['Number']['raw']." and status=0 and `id`<>".$ID;
$pre = sql_query($sqlQ) or die(mysql_error());
$row = mysql_fetch_assoc($pre);
if ($row['sum']===NULL)
{ $line['Number']['Sum']=0;
}
else
{
$line['Number']['Sum']=$row['sum'];
}
Save it and then open for editing. Change the following string:
$sqlQ = "SELECT SUM(`f1210`) AS sum FROM ".DATA_TABLE."110 WHERE `f1150`=".$line['Number']['raw']." and status=0 and `id`<>".$ID;
by inserting the values of tables and fields that you already inserted in the formula Amount of the Order. Now save it. Let’s check how it works.
Let's consider the example. Here you can see the Sum of the 3rd order. It is equal 6 850 (USD)
Now let's remove the 1st line in Order items subtable (so we are deleting 200 "Printed book ABC" from this order). The total sum of the order will change - see the screenshot below.
So, we created the required formulas for Orders table and its subtable Order items. If you need to perform simple calculations with the fields of only one table or with fields of linked tables, use Calculating Wizard. In the event it is necessary to perform any calculations with fields of subordinate tables or more complex operations, for example, with the use of different conditions, prepare the relevant formulas using PHP. If you do not have a PHP specialist, you can address this kind of issues to the developers of Basemaster.