2 Replies Latest reply on May 10, 2014 2:11 PM by philmodjunk







           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.

           Thank you!


        • 1. Re: Deposits

               I should also mention that there should be able to be an unlimited number of deposits. Currently I am displaying the Deposit table as a portal on the Invoice layout.

          • 2. Re: Deposits

                 I suggest that you set up a table of payments linked to your invoice table. The invoice total minus the total payments will be your balance due. Since it is a table of records, this can support an unlimited number of payments and a deposit is just one type of payment that you might log in such a table.