Update balances

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
wstewie
Posts: 4
Joined: 2021-03-02 09:38

Update balances

Post by wstewie » 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)

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);

    }
}

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Update balances

Post by pbottcher » 2021-03-02 18:25

Hi,

you can use a join to get both tables adressed. The matching field is your lookup field.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

wstewie
Posts: 4
Joined: 2021-03-02 09:38

Re: Update balances

Post by wstewie » 2021-03-02 20:15

https://www.dropbox.com/s/ckh4yoaeeo1je ... i.jpg?dl=0

Sorry, I don't quite understand. Still new to php. Where and how do I use a join function?
I tried a calculated field. Attached is a pick to maybe better explain myself

Post Reply