*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.*