Auto balance from two tables
Auto balance from two tables
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!
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!
Re: Auto balance from two tables
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
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.
Re: Auto balance from two tables
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;
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;
Re: Auto balance from two tables
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:
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.
Re: Auto balance from two tables
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.
Re: Auto balance from two tables
For now I have this code in LabelProduction table only.
I will try this code then. Thanks for your help.
I will try this code then. Thanks for your help.
Re: Auto balance from two tables
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.
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.
Re: Auto balance from two tables
Hi,
here is the error correction.
sql("UPDATE LabelProduction SET UnlabeledJars = '".$balance."' WHERE LotCode = '."$lot_code."' ", $eo);
should beyou need to get hold of the syntax.
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);
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.
Re: Auto balance from two tables
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.
However, when I tried entering some data and hit save record. I get a blank page - white page.
Re: Auto balance from two tables
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.
Re: Auto balance from two tables
Please see attachment
- Attachments
-
- LabelPic1.png (115.39 KiB) Viewed 5518 times
Re: Auto balance from two tables
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;
}
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;
}
Re: Auto balance from two tables
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.
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.
Re: Auto balance from two tables
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);
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);
Re: Auto balance from two tables
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 ?
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.
Re: Auto balance from two tables
Yes the screenshot posted earlier is LabelProduction_view.php
- Attachments
-
- LabelPic3.png (215.98 KiB) Viewed 5513 times
Re: Auto balance from two tables
Hi,
can you replace
$label = sqlValue("SELECT LabelJars FROM LabelProduction WHERE LabelID = '".$lot_code."' ");
by
$label = $data['LabelJars '];
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.