We have created invoices for a customer and the customer has paid all but the retainage on 2 of the invoices. How can I get the report to pull in the Inital amount, the Paid to date amount and the Unreleased retainage? My report currently only has the initial invoice amount.
Solved
How can you bring in the amount a customer has paid against an open invoice?
Best answer by Steve Milner
@aaronslim, your spreadsheet shows 0.00 in Original Retainage and Unreleased Retainage on both invoices, and Total Amount equals Amount. So Acumatica has no retainage recorded on those two. Whatever the customer held back is part of the regular Balance.
That export is the Invoices and Memos list, and it has a Balance column that's hidden by default. Open the gear at the top left of the grid (Column Configuration) and check Balance. Paid to date is Amount minus Balance. It covers everything applied and released against the invoice, so an applied credit memo counts along with customer payments.
If you'd rather see paid to date as its own column than subtract in Excel, build a generic inquiry on ARInvoice and add a result column with this formula:
=[ARInvoice.CuryOrigDocAmt]-[ARInvoice.CuryDocBal]
You'll see a Total Paid field on ARInvoice too. I'd leave it out. It counts applications that haven't been released yet, so it can run ahead of what's actually been paid.
On invoices that do carry retainage, Amount excludes it, Total Amount adds it back, and Unreleased Retainage holds it until you release it. Once released, the retainage bills on its own invoice with Retainage Document checked and Orig. Ref. Nbr. pointing back to the original. Payments against it land on that invoice.
+6@aaronslim, your spreadsheet shows 0.00 in Original Retainage and Unreleased Retainage on both invoices, and Total Amount equals Amount. So Acumatica has no retainage recorded on those two. Whatever the customer held back is part of the regular Balance.
That export is the Invoices and Memos list, and it has a Balance column that's hidden by default. Open the gear at the top left of the grid (Column Configuration) and check Balance. Paid to date is Amount minus Balance. It covers everything applied and released against the invoice, so an applied credit memo counts along with customer payments.
If you'd rather see paid to date as its own column than subtract in Excel, build a generic inquiry on ARInvoice and add a result column with this formula:
=[ARInvoice.CuryOrigDocAmt]-[ARInvoice.CuryDocBal]
You'll see a Total Paid field on ARInvoice too. I'd leave it out. It counts applications that haven't been released yet, so it can run ahead of what's actually been paid.
On invoices that do carry retainage, Amount excludes it, Total Amount adds it back, and Unreleased Retainage holds it until you release it. Once released, the retainage bills on its own invoice with Retainage Document checked and Orig. Ref. Nbr. pointing back to the original. Payments against it land on that invoice.
Translate
Thank you
I’m trying to follow your last paragraph regarding retainage. So in my updated query you can see the balance remaining of $12,242.71. Why would this not be in “Retainage” column as well? Does this have something to do with how the data is input originally?
Translate
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.