Skip to main content

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!

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] * BARRegister.CuryOrigDocAmt]) + sum((IIf(+ARAdjust.AdjgDocType]='CRM' OR ]ARAdjust.AdjgDocType]='PMT' OR ]ARAdjust.AdjgDocType]='PPM',(pARRegister.SignBalance]*-1),1)*cARAdjust.AdjAmt])) + (sum(]ARRegister.SignBalance]*IIf(lARRegister.DocType]='CRM' OR ]ARRegister.DocType]='PMT' OR ]ARRegister.DocType]='PPM', -1, 1) * (IIf( ARAdjustPayment.AdjdDocType]='CRM',-1,1))*RARAdjustPayment.AdjAmt]))

 

Hope that helps at least a little, good luck!

Reply


Terms & ConditionsCookie settings