Sorry...Another Inventory Question

1. Inventory_Stock (Amount of Stock on Hand) - Fields: Inv_ID, Item , Qnty
   
2. Inventory_Sales (Product Sold) - Fields: InvDet_ID, Date, Merchant, Address, Qnty_Sold, Item, Shipping_Cost
   
3. Inventory_Additions (RE-stocking) - Fields: InvAdd_ID, Item, Amnt-Added
   I know i need to use Events, the before added, or after added, but not sure what to use...can anyone help? Need to know the proper amount of stock at any time.
   Can i use the Inventory Additions, to also make adjustments when we do a check and find the numbers are off as well ( add and /or remove stock shortages or pluses)
   Thanks,
   
   Alan
   

http://www.asprunner.com/forums/topic/24120-a-very-simple-car-service-system-with-phprunner-9/

Alan,
you are not really telling what exactly you need to do. Try to make your question or questions more specific so we can suggest some code.

Let me try to explain better:

1. Inventory_Stock (Type of products) - Fields: Inv_ID, Item , Qnty, Comments
   
2. Inventory_Sales (Customer Invoices) - Fields: InvDet_ID, Date, Merchant, Address, Qnty_Sold, Item, Shipping_Cost, Comments
   
3. Inventory_Additions (Re-stocking) - Fields: InvAdd_ID, Item, Amnt_Added
   Similar to the sample you suggest i look at, I will add the list of products in the first table. When we add an order in table 2, I need to have the Quantity_Sold for any specific Item, subtracted for the listing in table 1. If we add stock in Table 3, I want the qnty in table 1 increased by the Amnt_Added for each Item added.
   Unfortunately the sample you sent me to look at, is not updated the Parts Table....

1. Inventory_Stock (Type of products) - Fields: Inv_ID, Item , Qnty, Comments
   
2. Inventory_Sales (Customer Invoices) - Fields: InvDet_ID, Date, Merchant, Address, Qnty_Sold, Item, Shipping_Cost, Comments
   
3. Inventory_Additions (Re-stocking) - Fields: InvAdd_ID, Item, Amnt_Added
   Similar to the sample you suggest i look at, I will add the list of products in the first table. When we add an order in table 2, I need to have the Quantity_Sold for any specific Item, subtracted for the listing in table 1. If we add stock in Table 3, I want the qnty in table 1 increased by the Amnt_Added for each Item added.
   Unfortunately the sample you sent me to look at, is not updated the Parts Table....
   

Ahh your suggested sample was great...i got the basic mathmatics working. When I sell stock it is removed from the stock count. When I add more stock it adds to the stock count.

I just adjusted the event that was shown.

I used:

global $conn;

$strSQLUpdate = "UPDATE inventory_stock SET Qnty = Qnty - '".$values["Qnty"]."' WHERE Item ='".$values["Item"]."'";

db_exec($strSQLUpdate,$conn);
I just changed the - to a plus for the two different needs.
Now the hard part. If I allow editing of the Inventory_Sales, I will need to allow for the adjustment. So I need to check what the Qnty was before the edit, and then adjust depending on whether I added more or took less...any suggestions on how i can do that? My thinking would be to add back any Qnty removed when i open the edit, and then subtract the Qnty after the save....

Suggestions...?

You've taught me well....i figured it out!

I use events and two fields new inventory received field ( fldrecinv ) and actual inventory ( fldinv ) then I use events to facilitate the calculation. I have in edit mode only as anything in add should be new inventory. adjustments to inventory with the ( fldinv ) field. I use a calculation like this
before record updated event .
$values["fldinv"]= $values["fldinv"] + $values["fldrecinv"];
I use fldinv in edit mode is only used for adjustments . where as fldrecinv is to update existing inventory that has arrived. The calculation will just add new inventory to fldinv .

2. If I make an adjustment I have an event to make sure that fldrecinv in edit mode is 0 meaning I am just updating inventory and not receiving anything new. Do not add the field fldinv to add page just edit page .

3. You can use fldinv fldcost as aliasfield to make your calculation in add mode. Use the calculation in your table query . something like fldinv fldcost AS fldtotal,
I use a tab for adjustments with the field fldinv and another tab for recieving new items ( fldrecinv ) in edit page . Where in add page I just use fldinv and fldcost and the query does the work and fldtotal shows total inventory cost.
something like this
in before process record event in edit mode in event editor . This is to ensure that the last value of new inventory is also not calculated with your adjustment field from the last value you entered when you originally received new inventory.
if ($values["fldrecinv"] > 0) {

($values["fldrecinv"] = 0);

}
else
{

}
Wish I can supply screenshot . but I don't see here where you can upload image here without providing url . I cannot provide url for security purposes. hope this helps.