Help with Inventory DB

[sboard0599]

I am trying to buld a DB that calculates my inventory. I have 6 inventory items. I have a Codes.fp5 file that I have all of the items and the item codes and beginning inventory numbers in. I have my transaction file set up to record indivdual uses of inventory items. I have a beginning and ending inventory field and an inventory_used field in a sub-summary part of the layout set to sort by inventory item. What I would like to achieve is a sub total of each item used(which I am getting) and then have that subtotal subtracted from the inventory item. I am getting the total of all items subtracted from each item. I need to subtract the total of item A from the begnning balalnce of item A. Now I have the totak of A, b & C subtracted from A and so on. Any ideas?

[danjacoby]

The best way to do this ("he said haughtily") is to have a total of three or four files: one file for each item ("Items"), one file for each order ("Orders"), one file for when you receive the items ("Restock") and a line-item file ("LineItems") where most of the data actually resides.

There is one record in the Items file for each item.

There is one record in the Orders file for each order.

The Orders file relates to the LineItems file through some key that is unique to each order (customer name plus order date, or some such). In a portal in the Orders file, you need (at least) fields for "Item" and "Quantity".

The Restock file relates to LineItems as well. For this portal, use the same "Item" field but a separate "QuantityRestocked" field for the quantity.

The Items file also relates to LineItems. In this file, create a calc field that is the sum of the related "Quantity" field minus the sum of the related "QuantityRestocked" field.