Skip to main content
Solved

Invoice balance on a specific date

  • 9 July 2024
  • 1 reply
  • 42 views

Hello,

I'm seeking a method to determine the remaining balance of an invoice as of a specific date in a generic inquiry. Consider the following scenario:

  • Initial Invoice Amount (01/01/2024): $1,000.00
  • Payment Made (02/01/2024): $400.00
  • Payment Made (04/01/2024): $200.00
  • Payment Made (05/01/2024): $300.00
  • Current Balance: $100.00

My goal is to find the balance of the invoice on a past date, such as $400.00 on (04/25/2024).

While I can join the ARInvoice with the ARAdjust table and filter by the ARAdjust entries made before my specified date (04/25/2024) to calculate the total amount paid, this does not provide me with the balance. Unfortunately, the ARAdjust table lacks a field that reflects the document balance after each transaction.

 

Thank you for your help!

1 reply

craig2
Rank
Userlevel 5
Badge +1

Hey @miguel80 ,

Hope all is well.  I was able to accomplish something similar in the AR Aging report, with a combination of the ARRegister table, an “As Of” Date Parameter, the filter below, and a very busy Variable.  Hopefully this at least gives you some ideas to meet your needs.

 

 

 

=([ARRegister.SignBalance] * [ARRegister.CuryOrigDocAmt]) + sum((IIf([ARAdjust.AdjgDocType]='CRM' OR [ARAdjust.AdjgDocType]='PMT' OR [ARAdjust.AdjgDocType]='PPM',([ARRegister.SignBalance]*-1),1)*[ARAdjust.AdjAmt])) + (sum([ARRegister.SignBalance]*IIf([ARRegister.DocType]='CRM' OR [ARRegister.DocType]='PMT' OR [ARRegister.DocType]='PPM', -1, 1) * (IIf([ARAdjustPayment.AdjdDocType]='CRM',-1,1))*[ARAdjustPayment.AdjAmt]))

 

Hope that helps at least a little, good luck!

Reply


Terms & ConditionsCookie settings