Update balances
Posted: 2021-03-02 16:38
I have a fruit packing facility site with 3 tables where I want to count quantities fruit received from different orchards in the field:
1. HarvestBlock (Active orchards being harvested)
2. Intake (Here is an item "Orchards" that is linked to table 1 via a lookup field. In a hook I update quantities of a specific orchard in table 1 with the received values here )
3. Tip (This table is linked to the specific records of field 2, but I also input new values here like "undersize", "class3" and "waste". I want to update balances of an "Orchard" in table 1 with these new values.
The problem is that appgini only seem to be able to update balances of a table if the record you create is linked to that specific table for the field you want to update. This means table 3 can only update balances in table 2 because it is linked there.
My current workaround is the below where I update undersize, calss3 and waste in table 2, but I rather want them updated in table 1. I use this code in my table 3 (after insert and after update)
1. HarvestBlock (Active orchards being harvested)
2. Intake (Here is an item "Orchards" that is linked to table 1 via a lookup field. In a hook I update quantities of a specific orchard in table 1 with the received values here )
3. Tip (This table is linked to the specific records of field 2, but I also input new values here like "undersize", "class3" and "waste". I want to update balances of an "Orchard" in table 1 with these new values.
The problem is that appgini only seem to be able to update balances of a table if the record you create is linked to that specific table for the field you want to update. This means table 3 can only update balances in table 2 because it is linked there.
My current workaround is the below where I update undersize, calss3 and waste in table 2, but I rather want them updated in table 1. I use this code in my table 3 (after insert and after update)
Code: Select all
function update_balances($data){
$item1 = makeSafe($data['Orchard']);
$item2 = makeSafe($data['PUC']);
if($item1){
$class3_balance = sqlValue("select sum( Class3) from Tip where Orchard={$item1} and PUC ={$item2}");
$undersize_balance = sqlValue("select sum( Undersize) from Tip where Orchard={$item1} and PUC ={$item2}");
$waste_balance = sqlValue("select sum( Waste) from Tip where Orchard={$item1} and PUC ={$item2}");
sql("update Intake set Class3='{$class3_balance}' where id={$item1}", $eo);
sql("update Intake set Undersize='{$undersize_balance}' where id={$item1}", $eo);
sql("update Intake set Waste='{$waste_balance}' where id={$item1}", $eo);
}
}