I'm trying to figure out how to show a deduction of the amount owed to a company when a client places multiple deposits on a subtotal from in invoice.
For example, if the client receives an invoice for $500 and places a $100 deposit, my balance field should show $400. THEN if they place a second deposit of $50 the balance field should show $350, and so on and so forth.
Currently I have 2 tables. An Invoice table which contains the "Subtotal" field and a Deposit table which has the fields "Deposit Amount" and "Balance" The Invoice table and Deposit Table are related to one another by an invoice serial number.
Currently the "Balance" field in the Deposit Table is a calculation which subtracts the "Deposit Amount" from the "Subtotal". This works great if there is only one deposit made. However, if you make a second deposit Balance result doesn't make sense.
Basically I need to figure out a way for the first deposit to subtract the "Deposit Amount" from the "Subtotal" and then store that new Balance as a variable. When a 2nd deposit is made the "Deposit Amount" needs to be subtracted from the new Balance. I'm not sure how to write that. Would that all be written in a calculation field or would it be some sort of a script.