
1. Re: Changing two values in two tables by join table
philmodjunk Sep 3, 2016 11:41 AM (in response to LeszekŁuczkanin)1 of 1 people found this helpfulPut two number fields in your operations table. A bookkeeper would think of them as debit and credit fields. We can all them: Add and Subtract.
For a given item, the sum of "Add" minus the sum of "Subtract" for all matching items will give you the total on hand. To reduce the calculation to a single sum, add a calculation field to Operations such as cBal defined to take the difference of Add and Subtract and then you can simply sum that one field to get the quantity on hand for any given item.
An unstored calculation in items can produce this value using the Sum function or you can use a script that, each time you add, delete or edit a record in operations, it computes the new balance from these fields and updates a number field in items. The second option takes more effort to implement but produces better performance when the number of records in Operations becomes large.