I have three tables that I am working with. I have a inventory detail table and a receive parts table that updates the equipment in stock table. When I receive inventory it tags the item as Customer Owned and the receive parts table tags the entry as Surplus Parts. When I deliver parts or equipment, In my movements form, I have a drop down that looks at the available tag_id numbers in the equipment list. If I pick a tag that is Customer Owned, it deletes the line item and when I pick a item that is taged Surplus it should just delete the qty entered for delivery for that tag id. The Customer Owned part works, but when I select a tag that is surplus and enter a qty, it enters a neg #(ie 10 in stock, 2 to deliver, current qty -2) The math looks right but it is surely not working right. Once I get this part right, I will put in some checks to make sure that I am not delivering more than I have in stock and on the receive parts I will check to see if a part number exists and if so just update qty and if not add new part number and qty. Here is the code that I am currently using in the movenent detail table. Any help would greatly be appreciated.
function BeforeAdd(&$values)
{
// Parameters:
// $values - Array object.
// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Update equipment in stock table ****
global $conn;
$strSQL = "SELECT * from _equipment_in_stock as ES,_movement_detail as MD where ES.Tag_ID = MD.Tag_ID && ES.Tag_ID = '".$values['Tag_ID']."' ";
db_exec($strSQL,$conn);
$result = mysql_query($strSQL);
$row = mysql_fetch_array($result);
if ($row['Type'] == 'Customer Owned')
{
// Delete from Equipment In Stock Table
global $conn;
$strSQLdelete = "delete from _equipment_in_stock where Tag_ID = '".$values['Tag_ID']."' ";
db_exec($strSQLdelete,$conn);
}
else
{
$curqty = $row['Qty'];
$newqty = $curqty - $values['Qty'] ;
// Update QTY
global $conn;
$strSQLupdate = "UPDATE _equipment_in_stock SET Qty = '".$newqty."' where Tag_ID = '".$values['Tag_ID']."'";
db_exec($strSQLupdate,$conn);
} //end else statement
return true;
// return true if you like to proceed with adding new record
// return false in other case
}