Solved

How to generate Date(Period) Specific - Paid to Date

  • 10 February 2022
  • 4 replies
  • 347 views

Userlevel 4
Badge

Hi, 

Does anyone have a generic inquiry I can use to calculate Paid to Date (with ability to view a range of time)?

I have been attempting to calculate Paid to Date for our Billings Cost Recap.  I can use the Invoices and memos to calculate a “real time” paid to date (doc amount - Balance = Amount Paid to Invoice). To get this in a date specific manor, I am attempting to use ARRegister & ARAdjust to be able to link the payment amounts to specific projects BY PERIOD.  I have not been successful and would appreciate guidance to producing a paid to date that can be used for historical periods. The amounts must be Project specific.

 

 

*I am surprised that Paid To Date is NOT an “out of box” report for the Construction edition. *

 

Please reach out with any questions if you need more information to advise.

icon

Best answer by ChrisWeidner 25 April 2022, 22:15

View original

4 replies

Userlevel 7
Badge

Thank you for sharing your solution @ChrisWeidner !

Userlevel 4
Badge

@Chris Hackett , I created a work around. I have a Billings/Cost Recap (B2D & C2D, by project (as of date)). Then I manually pull Paid to Date (with As of Date) via pivot table into Excel. Once both reports are in excel, I use a vlookup to combine into a single report (for over/under purposes). The Over/Unders need to be as of date (EOM), because this is ultimately what we use to tie out our job costs to our general ledger. For Paid to date I use a formula, then create a pivot table to combine Paid to Date by project by FinPeriod. Long work around, but it still is quicker than what was previously being done. 

 

(This formula may not work for all use cases. We only use AR for Invoices & AR credit memos)

=IIF([ARAdjust.AdjdDocType]='INV',[ARAdjust.CuryAdjdAmt],[ARAdjust.CuryAdjdAmt]*-1)

Userlevel 7
Badge

Hi @ChrisWeidner were you ever able to resolve your issue? Thank you!

Userlevel 3
Badge

Hi Chris,

I have the same problem as you. I am trying to create a report that would show all of the invoice for a project follow by the next column showing the amount paid but I am also finding it difficult. I have managed to almost get it but what is throwing it off are some AR reversal invoices. Below are the tables I used to make the relationship along with the generic inquiry. Hope someone can help as Acumatica should have an easy way to track paid to date for projects.

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved