2. PHPRunner General questions
  3. Subtract the sum of child table from master
This topic is locked

Subtract the sum of child table from master

 		11/28/2008 7:43:54 AM
PHPRunner General questions
vin7102 author

Hello All,
I'm creating an inventory app and need help with an operation
I have a master table "New Inventory" with one field called "Initial Qty" and one called "Current Balance"

And its Child table named "Pull Inventory" with a field called "Amount Used".
I need help figuring out how to sum all the values in "Amount used" in the child table and subtract that value from "Initial Qty" in the master table.

I would like that value to be presented at the bottom of the child table as well as in the master table in the "Current Balance" field.

and It would be great if those values were shown in the printer-friendly version.
Thanks In Advance,
Vince
J
 		Jane 11/28/2008

Hi,
use After record added/updated events on the Eventstab to update Current Balance field in the master table.

Here is just a sample:

global $conn,$strTableName;

$rs = CustomQuery("select sum(`Amount Used`) from `Pull Inventory` where DetailKey=".$_SESSION[$strTableName."_masterkey1"]);

$data = db_fetch_numarray($rs);
$strUpdate = "Update `New Inventory` set `Current Balance`=`Initial Qty`-".$data[0]." where MasterKey = ".$_SESSION[$strTableName."_masterkey1"];

db_exec($strUpdate,$conn);



where DetailKey and MasterKey are your actual field names.
vin7102 author 11/28/2008

Hi Jane,
Thanks for the very quick response but Ive plugged the your event in the After record added event in the detail "pull Inventory"-

"Add Page" event list but I keep getting an sql error
"select sum(`Amount Used`) from `Pull Inventory` where DetailKey=Dist 3"



I guess I'm confused about what you said:

"where DetailKey and MasterKey are your actual field names"

Can you explain this a little bit more?
Sorry for the confusion but I'm really new to this!
Thanks again Jane,
Vince
C
 		chaintm 11/29/2008

Where $rs line has DetailKey should be the actual database file name, IE if your working within a schema named sales, that field would be called sales, as you are using the DetialKey as the "lookup" for that name.
The $Strupdate line has "where MasterKey" is the referance of the key data, you can look at your visual edit pages and see on top usually it shows blablabla KEYFIELD or blablabla,, whatever your main key is for that page. So for instances, my order are based off the Orde_num key... so mine would look like this...
global $conn,$strTableName;

$rs = CustomQuery("select sum(`Delivery_cost`) from `order_sales` where Order_num=".$_SESSION[$strTableName."_masterkey1"]);

$data = db_fetch_numarray($rs);
$strUpdate = "Update `Delivery_cost` from `order_sales` where Order_num= ".$_SESSION[$strTableName."_masterkey1"];

db_exec($strUpdate,$conn);