Trying to build inventory system

[fileman]

I'm trying to build an inventory system. I've got two databases, one has the parts and quantities in stock, the other uses the parts in variable quantities. I can pull parts with a relationship but can't figure how to subtract the quantity and have it successively deduct from the total. I've tried scripts but can't figure out how to execute the script when the qty is input, I've tried calculations and lookups etc. I'm just lost. Any ideas would be greatly appreciated. Thanks.

[ZuperZZ]

Have you tried to get the quantity with a relationship? it should be easy.

In the first file (where you keep the stock)define:

- a relationship Prod_Id::Prod_ID

- a calc field: qty_sold=sum(Prod_Id::Prod_ID qty)

ZZ

[ryan93]

I know and use both Access and Filemaker Pro. I have successfully developed an inventory system in both databases. The way I did it was by creating a table of inventory transactions. Then linking purchase orders and products to the inventory transactions table. In the Inv Trans table create two fields, Purchase Order ID and Product ID. In your products table have a Product ID field and relate it to the Inv Transactions. In the Purchase Order table create an autonumber field called Purchase Order ID and relate it to the Inv Transaction table. The key is that you also need an autonumber field in the Inventory transaction table called Transaction ID. And remember to use portals. If you have any questions email me at [email protected]

Hope this wasn't too confusing.