How to track running totals (e.g. inventory)?
Newbie here. I trying to accomplish something that seems like a simple task, but can't quite figure it out. I am trying to track product inventory at stores as follows:
1) I set up a form that adds a store to the database (add_store).
2) I set up a form that adds product to the database (add_product).
3) I set up a form that adds a product to a store (add_product_to_store). I used look-up fields to choose store name and product name. There is a also a numeric field to specify how many units of the product are being added to the store (quantity).
What I would like to track is the quantity of each product at each store, using a running total, so that each time a user adds product to the store, the running total for that product-store combo is updated. It seems that this should be possible via a script on the add_product_to_store form.
However, I am not sure what to use to track the running totals (variables or records) and where to declare them. I would need a running total for all store/product permutations, and the number of permutations will keep changing as new products and stores are added to the database.
If someone could provide me with a methodology or tips to accomplish this, it would be greatly appreciated. Thanks!