I'm working on a database where the client need to track quantities.
There is a layout for each product with total quantity as one of the fields.
There is an invoice area where the user creates lines for a product sale. One of the entries is quantity sold.
When a number is entered in quantity sold I want this to subtract this number from total quantity. If the user changes the quantity sold number it must reflect in total quantity.
An example: Total quantity is 100. User enters 10 items as being sold. Total quantity changes to 90. User realizes they made a mistake and changes quantity sold to 20, total quantity changes to 80. User changes quantity sold to 1 and total quantity changes to 99.. etc..
I'm thinking of a script trigger that sets items sold as a variable on entry and another that does the math on modify but that's as far as my thinking takes me..