Page 1 of 1

Calculation Hook for reducing balance automatically

Posted: 2019-07-27 14:12
by jnewtcomp
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

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-28 21:11
by pbottcher
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

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']}");

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 13:15
by jnewtcomp
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!!!!!

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 14:36
by pbottcher
Hi,

can you post the complete function as you have it now. And a screenshot of the Consumable_Tools Stock_QTY defintion in AppGini

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 14:52
by jnewtcomp
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();
}

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 15:10
by jnewtcomp
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();
}

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 15:17
by jnewtcomp
appgini-.jpg
appgini-.jpg (41.08 KiB) Viewed 9579 times

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 16:04
by pbottcher
Hi,

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;
}
Also you have a different PK, so I adjusted the code. Hope that works.

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 16:34
by jnewtcomp
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.

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 17:11
by jnewtcomp
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();
}

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 17:44
by pbottcher
Hi,

what is the link (lookup) from the Consumable_Tools to the Inventory? Is it the item?

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 18:08
by jnewtcomp
Yes the item is my part number / stock number from bar-code.

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 21:08
by pbottcher
Hi,

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']}");
Are you having data in your inventory table?

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-29 21:27
by jnewtcomp
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;
}

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-30 12:50
by jnewtcomp
Toolcrib.zip
Zip copy of my axp
(9.33 KiB) Downloaded 382 times

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-30 19:34
by pbottcher
Thanks,

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

Re: Calculation Hook for reducing balance automatically

Posted: 2019-07-30 21:03
by jnewtcomp
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!!!

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-17 17:26
by jnewtcomp
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();
}

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-18 19:20
by pbottcher
Hi,

what is the relation key between the Purchase_Order and the Inventory table?

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-19 16:50
by jnewtcomp
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

Posted: 2019-09-19 18:19
by pbottcher
Hi,
thanks for the explanation, but what field in the purchase order is linked to which field in the inventory?

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-19 19:50
by jnewtcomp
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!!!

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-20 07:51
by pbottcher
Hi,

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

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-20 13:05
by jnewtcomp
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.

Re: Calculation Hook for reducing balance automatically

Posted: 2019-09-21 14:19
by jnewtcomp
Pascal !

That last code worked perfectly! Thank you very much.