How have you set up your tables and fields? Relationships?
You may want to have one table where you log each change in inventory and a second table where you have one product for each item on your stock list. They can be related by an Item ID field. A calculation field in the Produtcts table can use Sum to compute the current inventory or you can define a number field in the products table that is updated to show the quantity on hand each time you add a new record to the Log table to record a change in inventory.
A log table is useful to show how your inventory levels change over time and to audit for and correct mistakes. Logging items into inventory is as simple as creating a new record in the Log table and filling in appropriate fields. If you deal with invoices and purchase orders, they can be set up to use this same log table as a lineItems table.
You would have this relationship:
InventoryLog::ProductID = Products::ProductID
Inventory Log would have tables such as these:
Date, ProductID, QtyIn, QtyOut, cBal (calculation defined as QtyIn - QtyOut), sBalance ( Summary field, total of cBal, running total that restarts when sorted on ProductID).
Big help. What you have said is exactly the direction I want to head in.
I have set up the new Stock_Log table and set the fields as you've said. I have it linked to my Stock table using the Stock_ID key.
The questions now is how are changes in the Stock_Log going to affect the Item Qty in the Stock table? Do I have to link them or set up a new realationship join?
If you define the Item Qty field in the stock table as a calculation: Sum ( Stock_Log::cBal ), it will automatically compute the current inventory for each item.
With very large sets of records in the log table, a list layout that uses this calculation field to display inventory levels for all your products, may update very slowly. To speed things up, you may want to use a number field and use a script that automatically updates the inventory level each time you add a record. The produces a total for each item that lists rapidly in the stock table, but you have to control your scripts with care to avoid introducing errors in this level.
You can use the OnRecordCommit layout script trigger on you Stock_Log layout to run a script like this:
Set Field [StockTable::Item Qty ; StockTable::cItem Qty]
cItem Qty would be a calculation field defined in StockTable to use the above sum function to compute the inventory total for the item just updated in the Stock_Log table.