1 Reply Latest reply on Sep 3, 2016 11:41 AM by philmodjunk

    Changing two values in two tables by join table




      I have one CHILD table (operations) to check out my incomes/outcomes. It works pretty well.

      If I give a loan it is outcome, and then when it is given back, I write it as an income. Same with donations.


      But I have another category - ITEM. Here is the story:


      I get 10 bears and a 100 $ - it makes 10$ each. I also get 5 birds and 20 $ - it makes 4$ each. I have to give it away.

      I gave out 1 bear and 1 bird to my brother, 2 bears to my mother, and 1 bird to my kid.

      I want to have an updated record to know how many is left to giveaway and I need to know how much money is still to giveaway. So I need to update ITEM table and also update my OPERATIONS. I would like to do it with my OPERATIONS table which i use to note all my incomes/outcomes.


      How can I do that? Is it possible?

        • 1. Re: Changing two values in two tables by join table

          Put 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.

          1 of 1 people found this helpful