How to monitor inventory stocks from an invoice in one file, and sales from another file
Hi, I've been using FM for several years now, and I'd like to broaden the capabilities of what it can do. I would like it to display current stock levels. I'm having a tough time trying to figure out how to do it. Here is how my system is set up.
Invoices (purchases) - each invoice that comes in, a new record is created with Invoice No, date, items, quantity, price. As each invoice often has several items. Each item has its own field. e.g. Qty 1, Qty 2, Qty 3, Item 1, Item 2, Item 2, Item 3, Price 1, Price 2, Price 3.
Sales is in another file. The items several items can be sold in one sales record, but the fields are in repetitions. e.g. only one Qty field but 5 repetitions for storage and the same for item.
I'm probably shouldn't have set up my FM this way, but I started from scratch so probably this isn't the best way to do it. But in a situation like this, how should one do it? I'm browsing through my FM books and not really getting any bright ideas.
I'm open to suggestions. Thanks