Limiting Which Records Are Summed

[Patron Saint of Chimichangas]

Hello, all! Newbie here again, beginning to love these forums. :

In my Orders table, I've got a portal to the Line Items table which shows the following fields:

ItemID (drop down menu, text field)

ItemCost (looked-up)

Quantity (number field)

TotalItemCost ("ItemCost" * "Quantity")

Back on the Orders table, there's a GrandTotal field that sums all the TotalItemCost fields that for that particular order number, and everything works fine.

So here's my question. I have an Inventory table which contains all items, our cost for each, and the quantity we have in stock. Somehow I want to automatically subtract from that stock when an order is placed on the orders table. How can I do this? I've tried a few different things, but my fiddling isn't working.

I'm supposing this must be a fairly common process, right? If anyone can help I would be most grateful. Thanks!

[Ender]

The basic idea for this is quite simple; you simple have a calc in the Product table add up all the related Quantities, and deduct that from the Quantity purchased or manufactured.

But when you start looking at the issues that must be accounted for in a typical inventory cycle, this simple idea gets harder to implement. There have been many threads about inventory systems in the past, but here's a recent one that's worth checking out:

http://www.fmforums.com/forum/showtopic.php?tid/171928/post/185028