Page 1 of 1

Adjustment Quantity

Posted: 2019-03-09 19:54
by AEmpeno
Hello,

I would like to ask for help with my code.
CODE: SELECT ALL

Code: Select all

//Adjustments Batches //
$AdjustedQty = sqlValue("SELECT QtyJars FROM adjustments1 where BatchesID='{$id}'");
$positive_codes = "'Found-New'";
$OnHandJars = sqlValue("SELECT OnHandJars from batches where BatchesID='{$id}'");
$reason_balance = sqlValue("SELECT sum(if(reason in ({$positive_codes}) $AdjustedQty + $OnHandJars)) ");
sql("UPDATE batches SET OnHandJars='{$reason_balance}' WHERE BatchesID='{$id}'",$eo);
What I'm trying to accomplish is to update my batches.onhandjars when there's an adjustment on quantity.
For example, if there are missing jars, I record them to the adjustment table and categorize (reason) it as "missing", once entered I would like to see changes on my batches table (OnHandJars). Another one is if there's found new jars, I entered them on adjustment table and categorize (reason) it as "Found-New", and once entered I would like to see changes or increase on my OnHandJars field on adjustment table. In short, if the drop-down menu (reason codes) pick one it will do a certain calculation to batches table.

So the reason codes are "Re-label", "Re-classify", "Found-New", "Missing"

Thanks, Lon