2. PHPRunner General questions
  3. Totals of multiple details records
This topic is locked

Totals of multiple details records

 		5/30/2011 7:02:10 PM
PHPRunner General questions
L
 lordkain2 author

Hello, I have a master-detail relationship (1 Master - 5 Details , all tables have one common field "SaldoActual") and I want to sum this field ("SaldoActual") of all details tables and putting in the master table. Any Ideas?
Thanks
L
 		Lena 6/1/2011

Hi,

please find the sample how calculate value for master tables field when user adds or edits the records in the details tables:

use this code in the Add page: After record added event:



global $conn, $dal;

$tblMaster = $dal->Table("MasterTable");

$rsMaster = $tblMaster->Query("Masterkey=".$values["DetailMasterkey"],"");

$dataMaster = db_fetch_array($rsMaster);

$Sum = $dataMaster["SaldoActual"]; // get SaldoActual value from master table
$Sum = $Sum + $values["SaldoActual"]; //calculate new value
$strSQLUpdate = "update MasterTable set SaldoActual=".$Sum." where Masterkey=".$values["DetailMasterkey"]; // save new value to master table

db_exec($strSQLInsert,$conn);



where MasterTable is the actual name of master table, Masterkey - actual field name from the master table and DetailMasterkey - the actual field name from detail table.
use this code in the Edit page:After record updated event:



global $conn, $dal;

$tblMaster = $dal->Table("MasterTable");

$rsMaster = $tblMaster->Query("Masterkey=".$values["DetailMasterkey"],"");

$dataMaster = db_fetch_array($rsMaster);

$Sum = $dataMaster["SaldoActual"]; // get SaldoActual value from master table
$Sum = $Sum - $oldvalues["SaldoActual"] + $values["SaldoActual"]; //calculate new value
$strSQLUpdate = "update MasterTable set SaldoActual=".$Sum." where Masterkey=".$values["DetailMasterkey"]; // save new value to master table

db_exec($strSQLInsert,$conn)



where MasterTable is the actual name of master table, Masterkey - actual field name from the master table and DetailMasterkey - the actual field name from detail table.
john_m_craig 10/26/2011

Hi Lena,
I'm new to PHP Runner and your reply to this post helped me enormously so thanks.
It may be of use to the next person like me who finds this useful to also point out that you need to update the master record when you delete a detail record. I used your code for the change (detail) record but put it in the List Page, After Record Delete section and it (also) worked a treat. Or maybe just leave it as a learning exercise, like it was for me.
Regards, .../John