Solved

Construction Projects - Amount Paid


Userlevel 4
Badge

I’m trying to incorporate the amount paid into a progress report for the projects in the construction module. I have still not been able to figure out how to build the relationships correctly between the tables and have it happen.

These are the current tables in the report:

And these are the relationships that work so far:.

 

This is what my report looks like, but the “Balance” Collumn is what I’m looking at. I want to show either the amount already paid, or the balance still owed off of current invoiced amount.

Any suggestions?

icon

Best answer by meganfriesen37 22 June 2023, 20:46

View original

10 replies

Userlevel 4
Badge

@bzelinski That is because payment is registered in another data class table - have you tried to join this table to that report?

 

Userlevel 4
Badge

Hi @Janella 

I’ve been able to join the 2 tables, as far as I can tell:

 

Problem: I haven’t been able to have any field under ARPayment show any data on the report. 

Userlevel 7
Badge +6

@bzelinski do you use pro formas? can you link the project to the proforma and then the proforma to the arinvoice?

Userlevel 4
Badge

@bzelinski agreed with Iqra it’s because AR data set is not linked to projects only to AR - You want to look at data set that is used in Report AR Open Documents by Project, Paid/Adjusted Column, I was trying to get you that table name, but I’m having issues with my report designer. Good luck! 

Userlevel 4
Badge

We can see the proformas and then the AR invoices, but we have to look in the Accounts Receivable to see what has been paid. If we look at the Balances in the projects you can see the Revenue that has come in already, so you can basically see what has been paid, but it still doesn’t make it visible on a report. :-)

Userlevel 7
Badge +6

@bzelinski the balances tab won’t show what’s been paid, it just shows what has been invoiced.

Userlevel 7
Badge

Hi @bzelinski were you able to find a solution? Thank you!

Userlevel 4
Badge

Hi @Chris Hackett. Thanks for asking. No, I didn’t actually find a solution. :-) It would be a nice suggestion for Acumatica in construction reports linking the accounts receivable with the construction.

Userlevel 7
Badge +10

I’ve been able to do something similar via a subreport with variables, assuming that you’ve only got 1 project per invoice.  The tricky bit is if your invoices have taxes on them and whether or not you want to include that amount in your report.  The invoice balances would always be “live” as of today.   You may want to put in parameter that all the invoice dates are less than or equal to the report date so that you’re not getting newer invoices included.

In the subreport - link from ARInvoice to PM Project:
 

The report only works “as of today” and then use the following fields as required

  • ARInvoice.CuryLineTotal - Original total before tax
  • ARInvoice.CuryOrigDocAmt - Original total due including any taxes
  • ARInvoice.CuryDocBal - The balance of the invoice (including any taxes) that is open

Do your calculations in the subreport and set them up as variables and then pick up those variables in the main report to do calculations with them.

 

Hope this helps.

 

Userlevel 4
Badge

Thanks, @meganfriesen37. I have worked with existing sub-reports in the system, but I haven’t done much with creating new sub-reports. I can see why your idea is the best one. I’ll take on the new challenge. Thanks again!

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