AppGini Community Forums

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!

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

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;

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:

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

For now I have this code in LabelProduction table only.

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

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.

Hi,

here is the error correction.

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

should be you need to get hold of the syntax.

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.

can you post what u have in your function now.

Please see attachment

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

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.

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

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 ?

Yes the screenshot posted earlier is LabelProduction_view.php

Hi,

can you replace

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

by

$label = $data['LabelJars '];