2. PHPRunner General questions
  3. calculations from two tables using a table view
This topic is locked

calculations from two tables using a table view

 		3/10/2007 12:45:32 PM
PHPRunner General questions
V
 vytb author

Hello,
I'm trying to make calculation from the source fields of two different tables of the same record.
If I create a view of the second table with joint of the first table, I still cannot calculate on the add or edit pages because this means inserting into two pages at once and calculation is not done. Of course I could join physically these two tables and then split but for some reason it does not function (data from the first view of the same table is not counted in the second view).
What would be a solution?
J
 		Jane 3/12/2007

Vitas,
you can make your calculations in the Before record added or Before record update events on the Events tab.
V
 		vytb author 3/12/2007

Vitas,

you can make your calculations in the Before record added or Before record update events on the Events tab.


I understand... However, the result of a calculation in the second view does not count the data of the first view if I write the fields without table names. If I'll add table names, I am afraid that record selection will be lost. Won't it?
J
 		Jane 3/12/2007

Vitas,
it's difficult to tell you what's happening without seeing actual data.

Post your table structure and your calculations and I'll try to help you.
V
 		vytb author 3/12/2007

Vitas,

it's difficult to tell you what's happening without seeing actual data.

Post your table structure and your calculations and I'll try to help you.


Thank you a lot,
Let's see:
table a (not selected) field1, field2, field3, field4, field5

table a1 (1st view of table a) (Mastertable) field1, field4

table a2 (2nd view of table a) (child) field2, field3, field5
event on the add/edit pages of table a2:

$values['field3'] = $values['field1'] + $values['field2'];
It works fine only if I put all 3 fields on the same view but I don't want to. If I don't include field1 in the table a2 or if I include field1 as read-only in the table a2, it is not counted.
J
 		Jane 3/14/2007

Vitas,
here is a sample code:

function BeforeAdd(&$values)

{

//select value of field1 from master table

global $conn,$strTableName;

$str = "select field1 from MasterTable where ForeignKey=".$_SESSION[$strTableName."_masterkey1"];

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

//calculate field3

$values["field3"] = $data["field1"]+$values["field2"];

return true;

}
V
 		vytb author 3/15/2007

Vitas,

here is a sample code:


Thank you a lot Jane for your marvelous help as well as your colleagues,
I understand that I should treat the not updatable part of the view as a different table in order to get data from it. In that case, splitting the table and using tables and not views would be more logical. What code should I use in this case for the part "//select value of field1 from master table" and getting data not from 1 but from the several fields?
for example,

table (mastertable) (field_a, etc.)

table1 (child on 'field_a') (field1, field2)

table2 (child on 'field_a') (field3)
How to get the values of field1 and field2 in order to use them in calculations on the table2?
Thank you in advance.
J
 		Jane 3/16/2007

Vitas,
try to use following event code for th table2:

function BeforeAdd(&$values)

{

//select value of field1,field2 from master table

global $conn,$strTableName;

$str = "select field1,field2 from table1 where field_a=".$_SESSION[$strTableName."_masterkey1"];

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

//calculate field3

$values["field3"] = $data["field1"]+$data["field2"];

return true;

}