Auto balance from two tables

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Auto balance from two tables

Post by AEmpeno » 2018-07-20 17:03

Hello

I have two tables - ProductionList and LabelProduction.

ProductionList
+ProductionID
+LotCode
+ProductionJars

LabelProduction
+LabelID
+LotCode
+LabelJars
+UnlabeledJars

I would like to set up "UnlabeledJars" field as an auto-updating balance. It will calculate the remaining jars for a particular code.

For example:
ProductionList (table)
ProductionID: 01 (auto)
LotCode: CALKGB365H
ProductionJars: 4000

LabelProdcution (table)
LabelID: 05 (auto)
LotCode: CALKGB365H
LabelJars: 1000
UnlabeledJars: 3000 (auto-balance)

Appreciate all the help I can get. Thanks in advance!

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-20 21:08

Hi,
you could use the hooks/LabelProduction.php file and add the entries to the before_insert and before_update functions to calculate your UnlabeledJars (depending on your arithmetic).
Or you can do it via DB-Triggers
Or you can do it dynamically via Javascript in the hooks/LabelProduction-dv.js
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.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-20 21:13

Thanks, pböttcher!
I tried this code under hook before_insert and before_update and nothing is right. Is there something wrong with my code?

$ProductionID = makeSafe($data['selectedID']);
$lot_code = makeSafe($data['LotCode']);
$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = 'lot_code' ");
$production = sqlValue("SELECT ProductionJars FROM ProductionList WHERE id = 'lot_code' ");
$balance = $production - $label;
sql("UPDATE LabelProduction SET UnlabeledJars = '$balance' WHERE LotCode = 'lot_code' ", $eo);

return TRUE;

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-20 21:34

Hi,
in which hook file did you add this code? And what table are you updating?
I would guess, that you add the ProductionList first and want the calculation on changes to the LabelProdcution.
But I assume that the code is in the ProductionList.
Also, in your code I think you have some syntax errors. It should be:

Code: Select all

$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
$production = sqlValue("SELECT ProductionJars FROM ProductionList WHERE id = '".lot_code."' ");
$balance = $production - $label;
sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '."lot_code."' ", $eo);
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.

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-20 21:35

Sorry, to early submitted, the variables need to have an $ at the beginnig.

Code: Select all

$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
$production = sqlValue("SELECT ProductionJars FROM ProductionList WHERE id = '".$lot_code."' ");
$balance = $production - $label;
sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '."$lot_code."' ", $eo);
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.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-20 22:40

For now I have this code in LabelProduction table only.

I will try this code then. Thanks for your help.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-20 23:11

Hello again,

After trying it several times, Im getting this error message for both tables - ProductionList and LabelProduction

Parse error: syntax error, unexpected '$lot_code' (T_VARIABLE), expecting ',' or ')' in C:\xampp\htdocs\inventory\hooks\ProductionList.php on line 81
Parse error: syntax error, unexpected '$lot_code' (T_VARIABLE), expecting ',' or ')' in C:\xampp\htdocs\inventory\hooks\LabelProduction.php on line 80

Any thoughts? Thanks so much.

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-21 07:04

Hi,

here is the error correction.

sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '."$lot_code."' ", $eo);

should be

Code: Select all

sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '".$lot_code."' ", $eo);

you need to get hold of the syntax.
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.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-21 16:29

Thanks much. It did fix the error message.

However, when I tried entering some data and hit save record. I get a blank page - white page.

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-21 19:40

can you post what u have in your function now.
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.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-23 18:16

Please see attachment
Attachments
LabelPic1.png
LabelPic1.png (115.39 KiB) Viewed 5445 times

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-23 18:18

Here is the code in the function:

function LabelProduction_before_insert(&$data, $memberInfo, &$args){
$data['LabelCases'] = $data['LabelJars'] / $data['Pak'];
$data['Shrinkage'] = $data['Repack'] + $data['BrokenJars'] + $data['LowBrine'] + $data['NoVacuum'];
$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
$production = sqlValue("SELECT ProductionJars FROM ProductionList WHERE id = '".$lot_code."' ");
$balance = $production - $label;
sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '".$lot_code."' ", $eo);
return TRUE;
}

function LabelProduction_after_insert($data, $memberInfo, &$args){

return TRUE;
}

function LabelProduction_before_update(&$data, $memberInfo, &$args){
$data['LabelCases'] = $data['LabelJars'] / $data['Pak'];
$data['Shrinkage'] = $data['Repack'] + $data['BrokenJars'] + $data['LowBrine'] + $data['NoVacuum'];
$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
$production = sqlValue("SELECT ProductionJars FROM ProductionList WHERE id = '".$lot_code."' ");
$balance = $production - $label;
sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '".$lot_code."' ", $eo);
return TRUE;
}

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-23 18:48

Hi,
I think you are missing your value for $lot_code. This is undefined in the function and hence your SQL query will fail.
It looks like $lot_code is a lookup from another table, so you need to catch that value.
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.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-23 21:18

Thanks!

So I added these first 2 lines to the existing lines, refreshed the browser, and tried entering new data and still no changes.

$ProductionID = makeSafe($data['selectedID']);
$lot_code = makeSafe($data['LotCode']);
$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
$production = sqlValue("SELECT ProductionJars FROM ProductionList WHERE id = '".$lot_code."' ");
$balance = $production - $label;
sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '".$lot_code."' ", $eo);

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-23 21:51

Can you please post the two tables ProductionList and LabelProduction from AppGini

Also, can you check
$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
Is this really correct?
LabelJars should already be available in the data array. And LabelID should match $data['selectedID'].

The screenshot references the LabelProduction Detail View ?
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.

AEmpeno
Veteran Member
Posts: 72
Joined: 2018-01-04 18:48

Re: Auto balance from two tables

Post by AEmpeno » 2018-07-23 21:59

Yes the screenshot posted earlier is LabelProduction_view.php
Attachments
LabelPic3.png
LabelPic3.png (215.98 KiB) Viewed 5440 times

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

Re: Auto balance from two tables

Post by pbottcher » 2018-07-24 05:59

Hi,

can you replace

$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");

by

$label = $data['LabelJars '];
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.

Post Reply