2. PHPRunner General questions
  3. List page update all record with Child record values
This topic is locked

List page update all record with Child record values

 		5/15/2018 1:55:09 PM
PHPRunner General questions
U
 ustunsoz author

Hi Dears,
My aim is to auto update total field value with child table related records total sum while each time listing a table.


I am adding following code to List Page:Before Record Processed Event it is not giving any syntax error and but the list rows not displaying.
Master table is: mu_task_tasks, child table is: mu_task_support (the relation key on child is "IssueID")

global $strTableName,$dal;

$rs = CustomQuery("select sum(Qty * Price)from mu_task_support where IssueID='".$_SESSION[$strTableName."_masterkey1"]."'");

$data = db_fetch_numarray($rs);
$dal->Table('mu_task_tasks')->Value["Amount_TRL"] = $data[0];

$dal->Table('mu_task_tasks')->Param["ID"] = '".$_SESSION[$strTableName."_masterkey1"]."';

$dal->Table('mu_task_tasks')->Update();


Is there any mistake? or am i placing the event on wrong place.
Ps: I have also tried to place the code child table "edit Page After record update" but no success, but I want to run on list page
I will appreciate your support
Admin 5/15/2018

As a first step run syntax check in PHPRunner. Event code doesn't look right and should produce errors.
U
 		ustunsoz author 5/16/2018

I Have already declared that there is no syntax error, it is a pity, being oblige to prove my self with picture.
Admin 5/16/2018

Line 6 is incorrect, instead of Session variable value you just put the whole string there. It won't work. You can see that it is shown as a string in code editor, grey color instead of dark blue
U
 		ustunsoz author 5/16/2018

Thanks Sergey,
I have corrected the 6th line error, now the code looks normal in builder.
After I run the latest code the record rows are not displayed, just an empty lines listing as much as the no of actual records. When I am clicking the edit link for any row, it is always opening the same record.
What should I do
U
 		ustunsoz author 5/17/2018

While I am trying I have build following code, and it is working as function to update values in table.



function BeforeProcessRowList($data, $pageObject)

{

$mykey=$data[ID];
global $conn;

$str = "select sum(Qty * Price) from mu_task_support where IssueID= '{$mykey}'";

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

$data = db_fetch_numarray($rs);

$total=$data[0];
$strUpdate = "update mu_task_tasks set `Amount_TRL`= '{$total}' where ID ='{$mykey}'";

db_exec($strUpdate,$conn);
return true;


However, the list is not displaying the rows, I need magic touch to finalize...
Admin 5/17/2018

A couple of things:

  1. Make sure SQL Queries are generated properly. Instead of executing, print them on the page and make sure they look good.
  2. I'm not sure how this code that updates data is related to what you see on the screen. If you are trying to update something and show updated data on the screen BeforeProcessRowList will not work. This event happens when corresponding record is selected from the database already. If you update something in this event you will only see changes after page is reloaded.
U
 		ustunsoz author 5/17/2018

Yes I have printed query all works and looks good. Therefore, when I run the page, child records totals updating to master table total field.
When I disable the line "$data = db_fetch_numarray($rs);" the records displaying on the page, but of course the query not working.
If BeforeProcessRowList event is not appropriate, which event I should chose in order to update values on the list page?
I have chosen this event according to your documentation:
Admin 5/17/2018

There are a several issues with your code

  1. $data is an input parameter in this event that contains current record data. You are destroying $data array re-populating it with results of your query. This is why it is all empty.
  2. 'Calculate/display your own totals' is one thing, 'Update underlying table and expect new data to appear on the list' is another. These two have nothing in common.
    Your whole approach is incorrect. These calculations need to take place after changes made to child table. After child record is added, edited or deleted you need to re-calculate totals and update master table. You don't need to do anything at all on the master table itself.