How to track running totals (e.g. inventory)?
Newbie here. I'm trying to do something I think should be relatively easy but haven't been able to figure out. I'd like to track product inventory at stores. To that end, I have:
1) created a form to add stores to the database
2) created a form to add products to the database
3) created a form to add a product to a store; this one utilizes look-up fields from the add store and add product forms. There is also a quantity field to input the quantity of the particular product being added to the store.
What I would like to do is track a running total for each store-product combo, such that the quantity in the 'add product to store' form is added to the running total when the form is submitted. I would think this could be done with a script in the 'add product to store' form. However, I am not sure whether a record or variable is appropriate for each of the running totals, and it is not clear where/how to declare them. There are many permutations of store-product running totals, and new ones will constantly be added as more stores and products are added to the database.
If someone could suggest an appropriate strategy/method for implementing this it would be greatly appreciated. Thanks!