How to show total of detail tables on master list page

I guess you have some WorkOrder table for a Client in which you insert Spares amounts and Labour amounts.
The Id in the Masterlist is I guess your WorkOrderID. The WorkOrder is for Client client 1 who has a car of the mini cooper model.
The Id in the lower part of the screen is de Id of the Spares / Labour components that are "inserted" into the WorkOrder.
Can you let me know if my guesses are right?

Yes Henny Sol. you are right

I assume your WorkOrder table has a Key field Id1 and your Spares and Labour tables have as a Key Id1 (the WorkOrder) and Id2 (the Spares / Labour component you add to the order).

A possible solution is:

1. In the Query tab go to the WorkOrder and add "totamount" (including the " !!) in the Column field with totamount as an Alias. This is not a table field so the Table column stays empty.
2. In the Fields tab go to the WorkOrder and add the totamount field to the ListPage
3. In the Designer tab go to the WorkOrder Masterlist page and add a column header (Insert Html Text "TotalAmount") and in the cell below a CodeSnippet ("WorkOrder_totamount"). Leave the snippet as it is (only change the name and press OK)
4. Go to Events
5. Enter code in the WorkOrder List page (event Before record processed):

// TotSpares
$sql = "SELECT Id1, SUM(amount) AS totspares from spares where Id1=" . $data["Id1"] . " group by Id1";
$rs = DB::Query($sql);
$result=$rs->fetchAssoc();
if($result)
{
$data["totamount"] = $result["totspares"];
}
// TotLabour
$sql = "SELECT Id1, SUM(amount) AS totlabour from labour where Id1=" . $data["Id1"] . " group by Id1";
$rs = DB::Query($sql);
$result=$rs->fetchAssoc();
if($result)
{
$data["totamount"] = $data["totamount"] + $result["totlabour"];
}

6. The CodeSnippet you created you will find as one of the last events in the Event tab. Change the code to look like this:

global $pageObject;
if ($data = $pageObject->getMasterRecord())
{
$totamount = 0;
// TotSpares
$sql = "SELECT Id1, SUM(amount) AS totspares from spares where Id1=" . $data["Id1"] . " group by Id1";
$rs = DB::Query($sql);
$result=$rs->fetchAssoc();
if($result)
{
$totamount = $result["totspares"];
}
// TotLabour
$sql = "SELECT Id1, SUM(amount) AS totlabour from labour where Id1=" . $data["Id1"] . " group by Id1";
$rs = DB::Query($sql);
$result=$rs->fetchAssoc();
if($result)
{
$totamount = $totamount + $result["totlabour"];
}
echo format_currency($totamount]);

Much appreciated. Shall give it a shot.

Hi. i am still stuck. On attempting to implement this code i keep getting this error.

Fatal error: Uncaught Error: Call to a member function fetchAssoc() on boolean in C:\projects phprunner\include\client_events.php:67 Stack trace: #0 C:\projects phprunner\classes\listpage.php(1489): eventclass_client->BeforeProcessRowList(Array, Object(ListPage_Simple)) #1 C:\projects phprunner\classes\listpage.php(1542): ListPage->beforeProccessRow() #2 C:\projects phprunner\classes\listpage.php(2310): ListPage->fillGridData() #3 C:\projects phprunner\client_list.php(154): ListPage->prepareForBuildPage() #4 {main} thrown in C:\projects phprunner\include\client_events.php on line 67

I'm puzzled about your data model. I would expect a Client table for your customers, an Order table for your workorders and Spares and Labour tables for the spares and labour you "consume" in the workorder.

A client can "have" several orders. So in your error message I would expect a Listpage of an Order table and NOT of your Client table. I assume you want to see on the Order Listpage for each workorder the total amount (sum of spares and labour for each workorder).

Maybe I misunderstood?

Thanks Honey for your assistance. Your code works great. I only had to add a single curly bracket at the end. In the actual project the culprit was on the spelling of labour.
In the project the table name is labor.
My bad had to use a dummy project for further testing hence the current error.
Again much obliged.