5 Replies Latest reply on Oct 14, 2014 8:22 PM by barenose

    Running balance for invoicing


      Hey everyone,


      I posted this over a year ago and got some helpful answers, but I'm revisiting this question.


      I have an invoice table and a payments table.

      I want a running balance for clients if they partially pay one invoice…I want the o/s balance to be added to the next invoice.


      The relationship between the two is the invoice id + the client id

      I have the calulations right to reflect the running balance.


      My problem is that the next invoice doesn't reflect the running balance.

      The script has to find all that client's invoices to see that there's an o/s balance.

      Do I have to incorporate a loop to find the o/s balance?


      I'm missing something simple here.


      Thanks in advance,



        • 1. Re: Running balance for invoicing

          That's not good accounting; you don't add any open balances as an invoice item, that would be double-billing and it will inflate your AR.

          You can mention the open balance on the invoice, but should not reflect in that invoice's total.  That may be a small distinction but it is a crucial one.


          The open balance is just one number on the customer's record that gets set whenever you post a payment, so you can pick that up easily enough for display on an invoice.

          • 2. Re: Running balance for invoicing

            Wim is correct, you are really talking about the distinction between a invoice and a statement.


            The payment due is a result of the invoice total plus the customer's statement balance. So you will likely end up with two relationships: Invoice ID_Client ID and the other is just the Client ID used to sum up all the Amount Due (+ or -) records from the Payment table.

            • 3. Re: Running balance for invoicing

              In the same line of thought with Wim and USBC


              I think that what you want is to keep track of the pending amount for each invoice. In this case you can have a relationship from invoices to payments and then have a calculation like this:


              Pending Amount = Grand Total - Sum ( Payments::Paid Amount ).


              So, an invoice can be totally pending, partially pending or paid.


              Just my 2¢ of mexican pesos.

              • 4. Re: Running balance for invoicing
                Stephen Huston

                I think USVC is on the right track, that what you need is a statement which shows the balances of al unpaid invoices.


                A statement's fields can be within the customer table or in a 1:1 relationship with the customer, with only one record necessary per customer to provide the info from all unpaid invoices.


                Ibrahim's idea about tracking each invoice's status is a good one. Then only link the unpaid one's to the statement so the calcs don't have to manage totals from everything ever invoiced in the past each time it's time to run the statement.


                You could also script the creation of statement records for customers with pending invoices at any time so that you have a full hisotry of billing statements, based on invoices, but not revising the invoices themselves.

                • 5. Re: Running balance for invoicing

                  Thanks to everyone!


                  After mulling it over while I was traveling on the bus today, the answer came to me…aall of what you said! Thanks so much for your generosity in sharing your time and wisdom with me!


                  All the best,


                  JB, The Creative One