1 Reply Latest reply on Feb 4, 2014 1:03 PM by KylePutzier

    Current customer balance on invoice


      Hi guys/gals. I have an invoicing database. everything seems to run smoothly until now. the client (my wife) would like when she accepts payments from customers, that there be a visible Current Balance to show on the current invoice giving the customer a chance to pay both the invoice and current balance. I currently have calculated field that uses the getsummary function to add all balances due in the accountbalances table. the calculation seems to work but now show up on the invoice. It did once after I entered something in the payment field. I am going to script some buttons that will allow her to select full payment, pay account off etc. I don't think I will have a problem with that. Just getting the current balance to show on the invoice.


      I understand that using a getsummary field in browse mode would slow the database down after many records, but are anyother possible solutions. I thought about a portal on the invoice screen showing all invoices for that particular customer. then the end user could see at a glance which invoices are outstanding and a balance.



        • 1. Re: Current customer balance on invoice


          I do a similar thing on our invoices. I've added an option to include a statement on the invoice. It looks like this:

          Screen Shot 2014-02-04 at 12.37.05 PM.png

          I have a table that contains a record for each open receivable. It is updated from our accounting software via an import.

          That table contains a calc that assembles some data to be used for the statement.


          Statement =


          Left(Invoice No. & "                                      ";10)


          Left(Client PO & "                                                              ";18)


          Left(Invoice Description & "                                                                                        ";25)


          Right("                                                    " & GetAsText(Inv Date) ;14)


          Right("                                                     " & GetAsText(Due Date)  ;14)


          Right( "                                                       " & AddDollarFormat ( Outstanding );15)



          When the option to include a current statement is chosen, this calc builds the statement to be added to the invoice memo area.



          Memo = "For your convenience, here is a current statement. (as of " & Invoices | CurrentReceivables::as of&  ")¶¶" &

          TextStyleAdd (

          Left("Inv." & "                                      ";10)


          Left("Your PO" & "                                                          ";18)


          Left("Invoice Description                                                                                         ";25)


          Right("                                              " & "Inv. Date" ;14)


          Right("                                               " & "Due Date" ;14)


          Right( "                                                       " & "Outstanding";15)

          ; Underline )

          & "¶" &

          TextFormatRemove (List(Invoices | CurrentReceivables::Statement)) &

          "¶¶Thank you very much for your business."


          TextFont ( Memo;"Monaco" )



          I realize that this example may be confusing as I haven't explained it very well.

          Basically it's just a calc that creates a header, then list the related records from a receivables table and adds a footer. I use a monospaced font to keep everything all lined up. No portals needed. It is just like you typed it into the memo section manually.