I have 2 files; "Daily Sales" and "Merch Items". "Merch Items" contains info such as price and original inventory. I need to be able to track remaining inventory. Right now I have a simple calculation which subtracts daily sales from Original Inventory, but of course that only works for the first show.

What's the best way to record the new inventory figures so that the next day sales are subtracted from the most current figure?

You can create a script that you run everyday or every week etc., that either adds new records to the inventory file or revises the existing records. I prefer to add new records so that you have a historical record of the inventory of any product on any particular date. Alternately, you can keep a single record per product and just update the quantity in that record. Basically, your calculation is going to be:

NewQuantity = MerchItems::Quantity - Sum(SelfByProductID::QtySold)

If you add new records to the MerchItems file everytime you update the inventory, it does slightly complicate the relationship that you have to create. The relationships need to be set up so that MerchItems will find only the latest record for the Product and the SelfByProductID relationship will bring only the sales records for the products that have not been subtracted from inventory yet. Doing it this way allows you to be a bit more flexible about when you run your update script. You can do on an irregular basis and it won't screw up your inventory. Plus, you will have a historical record of all transactions and inventory. To build the relationships, I would have a field in the DailySales file called InventoryUpdated. This keeps track whether the sale has been subtracted from the inventory yet. Leave it empty until the sale is subtracted from the inventory. Then, put something in the field like "yes" when you run the update script.

The key field in the relationship can then be a calculated field with this formula:

Case(isEmpty(InventoryUpdated),"",ProductID)

Then, if you do a selfjoin relationship on this field, it will only bring up the records that have not yet been accounted in the inventory.

Likewise, in the MerchItems file, your key field would be set equal to the productID field in the newest records, and your script would clear the key field in all the superceded inventory records. So, that only the newest record for each product will be found for the relationship. Note, the key field needs to be a separate field from the actual ProductID field, so that when you clear the key field, you will still have the productID field to refer to later.

Thanks for your response. I think this has turned into more of a calculation issue than a relationship issue. After a lot of thought, I realized that I am really just trying to figure out how to create a "current inventory" field from which I can subtract daily sales from as opposed to a "Invenotry" field from which I subtract "total of daily sales".