The solution is not intuitive or straight-forward: you cannot easily make change a row in one table based on an arbitrary row of another table. Thereโs no way to go to a particular inventory row and tell it to update itself according to some other row. The action that tries to update the inventory row has no way of knowing what maintenance form invoked it, so the action doesnโt know where to get the new values.
Instead, you have to somehow flag the row with the data to use, then direct the row-to-be-updated to find the flagged row and use it. The maintenance form puts a sign up that says, โI have updates!โ, then tells the inventory row to update itself. The inventory row must then find the maintenance form with the sign and use the data it finds there. After the inventory has been updated, the maintenance form needs to take the sign down to avoid being counted the next time inventory updates itself.
Another approach is to make the inventory recalculate itself entirely, rather than adjusting itself. With this approach, the inventory looks for all corresponding maintenance form rows and recalculates inventory each time. Since maintenance forms would identify removals from inventory (debits), youโd also need some way to identify additions to inventory (credits), perhaps in a separate table.
Still another approach might be a ledger-style table. When a part is used, an entry is added to the ledger reflecting a debit. When new inventory is received, a entry is added with a credit. Then inventory is just a matter of summing ledger entries of a particular part type.
The ledger approach is best, I think.
LinkedIn
Twitter
.
