Calculation Hook for reducing balance automatically
Calculation Hook for reducing balance automatically
Hello,
I have tried to use the examples on this website and have failed to complete the task. I am trying to auto update a balance in inventory from a consumable tools check out field. Listed below are my tables.
Consumable_Tools
ID
Item
Date_Issued
Employee
Order_Number
Part_Number
Operation
Qty_Issued <--- Qty to remove from Inventory
Size
Unit_of_Measure
MFG_Part_Number
Stock_QTY <----Link to inventory
Minimum_Stock
Notes
Inventory
ID
Item
Manufacturer
MFG_Part_Number
Tool_Type
Size
Base_Material
Coating
Location
Supplier
Stock_QTY <----- balance to be updated from Consumable_Tools
Minimum_Stock
Condition
Notes
Thank you in advanced for any help you can give.
Jim
I have tried to use the examples on this website and have failed to complete the task. I am trying to auto update a balance in inventory from a consumable tools check out field. Listed below are my tables.
Consumable_Tools
ID
Item
Date_Issued
Employee
Order_Number
Part_Number
Operation
Qty_Issued <--- Qty to remove from Inventory
Size
Unit_of_Measure
MFG_Part_Number
Stock_QTY <----Link to inventory
Minimum_Stock
Notes
Inventory
ID
Item
Manufacturer
MFG_Part_Number
Tool_Type
Size
Base_Material
Coating
Location
Supplier
Stock_QTY <----- balance to be updated from Consumable_Tools
Minimum_Stock
Condition
Notes
Thank you in advanced for any help you can give.
Jim
Re: Calculation Hook for reducing balance automatically
Hi,
you can do this in the hooks/Consumable_Tools.php file.
In the after_insert, after_update and bevor_delete function you need to add you calculation.
so, eg. in the after_insert function you can try
you can do this in the hooks/Consumable_Tools.php file.
In the after_insert, after_update and bevor_delete function you need to add you calculation.
so, eg. in the after_insert function you can try
Code: Select all
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
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: Calculation Hook for reducing balance automatically
Thank you for your reply, I placed this code after_insert, after_update and after_Delete.
The qty did not change no matter where i placed this code. I really thank you for the help but I am obviously making a mistake some place. please help.
Thank you!!!!!
The qty did not change no matter where i placed this code. I really thank you for the help but I am obviously making a mistake some place. please help.
Thank you!!!!!
Re: Calculation Hook for reducing balance automatically
Hi,
can you post the complete function as you have it now. And a screenshot of the Consumable_Tools Stock_QTY defintion in AppGini
can you post the complete function as you have it now. And a screenshot of the Consumable_Tools Stock_QTY defintion in AppGini
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: Calculation Hook for reducing balance automatically
this is currently my Consumable_Tools.php file
<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function Consumable_Tools_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Consumable_Tools_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Consumable_Tools_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
return TRUE;
}
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
function Consumable_Tools_before_update(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_update($data, $memberInfo, &$args){
return TRUE;
}
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
function Consumable_Tools_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_delete($selectedID, $memberInfo, &$args){
}
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
function Consumable_Tools_dv($selectedID, $memberInfo, &$html, &$args){
}
function Consumable_Tools_csv($query, $memberInfo, &$args){
return $query;
}
function Consumable_Tools_batch_actions(&$args){
return array();
}
<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function Consumable_Tools_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Consumable_Tools_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Consumable_Tools_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
return TRUE;
}
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
function Consumable_Tools_before_update(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_update($data, $memberInfo, &$args){
return TRUE;
}
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
function Consumable_Tools_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_delete($selectedID, $memberInfo, &$args){
}
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.id = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.id ={$data['Stock_QTY']}");
function Consumable_Tools_dv($selectedID, $memberInfo, &$html, &$args){
}
function Consumable_Tools_csv($query, $memberInfo, &$args){
return $query;
}
function Consumable_Tools_batch_actions(&$args){
return array();
}
Re: Calculation Hook for reducing balance automatically
This is inventory.php
?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function Inventory_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Inventory_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Inventory_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Inventory_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Inventory_after_insert($data, $memberInfo, &$args){
return TRUE;
}
function Inventory_before_update(&$data, $memberInfo, &$args){
return TRUE;
}
function Inventory_after_update($data, $memberInfo, &$args){
return TRUE;
}
function Inventory_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Inventory_after_delete($selectedID, $memberInfo, &$args){
}
function Inventory_dv($selectedID, $memberInfo, &$html, &$args){
}
function Inventory_csv($query, $memberInfo, &$args){
return $query;
}
function Inventory_batch_actions(&$args){
return array();
}
?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function Inventory_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Inventory_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Inventory_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Inventory_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Inventory_after_insert($data, $memberInfo, &$args){
return TRUE;
}
function Inventory_before_update(&$data, $memberInfo, &$args){
return TRUE;
}
function Inventory_after_update($data, $memberInfo, &$args){
return TRUE;
}
function Inventory_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Inventory_after_delete($selectedID, $memberInfo, &$args){
}
function Inventory_dv($selectedID, $memberInfo, &$html, &$args){
}
function Inventory_csv($query, $memberInfo, &$args){
return $query;
}
function Inventory_batch_actions(&$args){
return array();
}
Re: Calculation Hook for reducing balance automatically
Hi,
put the code inside the function, like this.
Also you have a different PK, so I adjusted the code. Hope that works.
put the code inside the function, like this.
Code: Select all
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
return TRUE;
}
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: Calculation Hook for reducing balance automatically
Hello pbottcher,
I have added the code as posted and when i open the browser and choose the Consumable_Tools the page is blank, I remove the code listed above and the page comes up as normal. I really appreciate your help.
I have added the code as posted and when i open the browser and choose the Consumable_Tools the page is blank, I remove the code listed above and the page comes up as normal. I really appreciate your help.
Re: Calculation Hook for reducing balance automatically
I was able to view the Consumable_Tools interface but unable to update the qty. I have attached my PHP file here.
<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function Consumable_Tools_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Consumable_Tools_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Consumable_Tools_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
return TRUE;
}
function Consumable_Tools_before_update(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_update($data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_delete($selectedID, $memberInfo, &$args){
}
function Consumable_Tools_dv($selectedID, $memberInfo, &$html, &$args){
}
function Consumable_Tools_csv($query, $memberInfo, &$args){
return $query;
}
function Consumable_Tools_batch_actions(&$args){
return array();
}
<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks
function Consumable_Tools_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Consumable_Tools_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Consumable_Tools_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT i.Stock_QTY-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
return TRUE;
}
function Consumable_Tools_before_update(&$data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_update($data, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Consumable_Tools_after_delete($selectedID, $memberInfo, &$args){
}
function Consumable_Tools_dv($selectedID, $memberInfo, &$html, &$args){
}
function Consumable_Tools_csv($query, $memberInfo, &$args){
return $query;
}
function Consumable_Tools_batch_actions(&$args){
return array();
}
Re: Calculation Hook for reducing balance automatically
Hi,
what is the link (lookup) from the Consumable_Tools to the Inventory? Is it the item?
what is the link (lookup) from the Consumable_Tools to the Inventory? Is it the item?
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: Calculation Hook for reducing balance automatically
Yes the item is my part number / stock number from bar-code.
Re: Calculation Hook for reducing balance automatically
Hi,
can you try
Are you having data in your inventory table?
can you try
Code: Select all
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
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: Calculation Hook for reducing balance automatically
I have tried this code and it does not function. I have thousands of items in inventory at this time. I have placed this code as shown below
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
return TRUE;
}
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Stock_QTY']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Stock_QTY']}");
return TRUE;
}
Re: Calculation Hook for reducing balance automatically
Thanks,
can you try this:
Also I noticed that you do not have the Item as mandatory in your Consumable Tools table. So you could create an entry without Item which would cause the queries to fail. So either you should make it mandatory, or you need to add a check if $data['Item'] has a value.
can you try this:
Code: Select all
function Consumable_Tools_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}
function Consumable_Tools_before_update(&$data, $memberInfo, &$args){
$old_qty=sqlvalue("SELECT IFNULL(Qty_Issued,0) from Consumable_Tools where Consumable_ToolID={$data['selectedID']}");
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Issued']},0) from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}
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: Calculation Hook for reducing balance automatically
I am very grateful for your help and for being patient with me. I must say this worked and there is no way I could have figured this out without you
Pbottcher. This worked very well. I have made the item required. Thank you for all your help!!!
Pbottcher. This worked very well. I have made the item required. Thank you for all your help!!!
Re: Calculation Hook for reducing balance automatically
Hello,
I need help with an additional calculation.. I have a table added called Purchase_Order that needs to update the stock_qty when adding Qty_Received. please help, I have tried modifying the script above and for some reason it does not work.
<?php
//
function Purchase_Order_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Purchase_Order_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Purchase_Order_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}
function Purchase_Order_before_update(&$data, $memberInfo, &$args){
$old_qty=sqlvalue("SELECT IFNULL(Qty_Received,0) from Purchase_Order where Purchase_OrderID={$data['selectedID']}");
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Received']},0) from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}
function Purchase_Order_after_update($data, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_after_delete($selectedID, $memberInfo, &$args){
}
function Purchase_Order_dv($selectedID, $memberInfo, &$html, &$args){
}
function Purchase_Order_csv($query, $memberInfo, &$args){
return $query;
}
function Purchase_Order_batch_actions(&$args){
return array();
}
I need help with an additional calculation.. I have a table added called Purchase_Order that needs to update the stock_qty when adding Qty_Received. please help, I have tried modifying the script above and for some reason it does not work.
<?php
//
function Purchase_Order_init(&$options, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_header($contentType, $memberInfo, &$args){
$header='';
switch($contentType){
case 'tableview':
$header='';
break;
case 'detailview':
$header='';
break;
case 'tableview+detailview':
$header='';
break;
case 'print-tableview':
$header='';
break;
case 'print-detailview':
$header='';
break;
case 'filters':
$header='';
break;
}
return $header;
}
function Purchase_Order_footer($contentType, $memberInfo, &$args){
$footer='';
switch($contentType){
case 'tableview':
$footer='';
break;
case 'detailview':
$footer='';
break;
case 'tableview+detailview':
$footer='';
break;
case 'print-tableview':
$footer='';
break;
case 'print-detailview':
$footer='';
break;
case 'filters':
$footer='';
break;
}
return $footer;
}
function Purchase_Order_before_insert(&$data, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}
function Purchase_Order_before_update(&$data, $memberInfo, &$args){
$old_qty=sqlvalue("SELECT IFNULL(Qty_Received,0) from Purchase_Order where Purchase_OrderID={$data['selectedID']}");
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Received']},0) from Inventory i where i.InventoryID = {$data['Item']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.InventoryID ={$data['Item']}");
return TRUE;
}
function Purchase_Order_after_update($data, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
return TRUE;
}
function Purchase_Order_after_delete($selectedID, $memberInfo, &$args){
}
function Purchase_Order_dv($selectedID, $memberInfo, &$html, &$args){
}
function Purchase_Order_csv($query, $memberInfo, &$args){
return $query;
}
function Purchase_Order_batch_actions(&$args){
return array();
}
Re: Calculation Hook for reducing balance automatically
Hi,
what is the relation key between the Purchase_Order and the Inventory table?
what is the relation key between the Purchase_Order and the Inventory table?
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: Calculation Hook for reducing balance automatically
Purchase order is linked to the inventory. I open inventory and when I am below minimum QTY I create a purchase order that is linked to inventory. The purchase order uses Quoted, Ordered, and Closed for status. When Closed a QTY_Received is updated to the total qty received from vendor that should Update the Inventory Stock_QTY
Re: Calculation Hook for reducing balance automatically
Hi,
thanks for the explanation, but what field in the purchase order is linked to which field in the inventory?
thanks for the explanation, but what field in the purchase order is linked to which field in the inventory?
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: Calculation Hook for reducing balance automatically
Part_Number under purchase_order is linked to MFG_Part_Number under Inventory.
I have placed a copy of here in zip format. The file is from a secure server with 3 layers of antivirus thus its clean.
thank you so much for your help!!!
I have placed a copy of here in zip format. The file is from a secure server with 3 layers of antivirus thus its clean.
thank you so much for your help!!!
- Attachments
-
- Toolcrib.zip
- (11.15 KiB) Downloaded 315 times
Re: Calculation Hook for reducing balance automatically
Hi,
try
try
Code: Select all
function Purchase_Order_after_insert($data, $memberInfo, &$args){
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY,0)-{$data['Qty_Issued']} from Inventory i where i.MFG_Part_Number= {$data['Part_Number']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.MFG_Part_Number ={$data['Part_Number']}");
return TRUE;
}
function Purchase_Order_before_update(&$data, $memberInfo, &$args){
$old_qty=sqlvalue("SELECT IFNULL(Qty_Received,0) from Purchase_Order where Purchase_OrderID={$data['selectedID']}");
$new_qty=sqlvalue("SELECT IFNULL(i.Stock_QTY+{$old_qty}-{$data['Qty_Received']},0) from Inventory i where i.MFG_Part_Number= {$data['Part_Number']}");
sqlvalue("UPDATE Inventory i set i.Stock_QTY = {$new_qty} where i.MFG_Part_Number={$data['Part_Number']}");
return TRUE;
}
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: Calculation Hook for reducing balance automatically
I have tried this and it does not change the stock qty. I tried playing with the format and alignment.
Thank you again for helping.
Thank you again for helping.
Re: Calculation Hook for reducing balance automatically
Pascal !
That last code worked perfectly! Thank you very much.
That last code worked perfectly! Thank you very much.