Skip to main content
Solved

Construction Projects - Amount Paid


bzelinski
Varsity II
Forum|alt.badge.img

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?

Best answer by meganfriesen37

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.

 

View original
Did this topic help you find an answer to your question?

10 replies

Janella
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • 67 replies
  • May 19, 2023

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

 


bzelinski
Varsity II
Forum|alt.badge.img
  • Author
  • Varsity II
  • 61 replies
  • May 19, 2023

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. 


iqraharrison
Captain II
Forum|alt.badge.img+8

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


Janella
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • 67 replies
  • May 19, 2023

@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! 


bzelinski
Varsity II
Forum|alt.badge.img
  • Author
  • Varsity II
  • 61 replies
  • May 19, 2023

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. :-)


iqraharrison
Captain II
Forum|alt.badge.img+8

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


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2551 replies
  • June 21, 2023

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


bzelinski
Varsity II
Forum|alt.badge.img
  • Author
  • Varsity II
  • 61 replies
  • June 22, 2023

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.


meganfriesen37
Captain II
Forum|alt.badge.img+11

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.

 


bzelinski
Varsity II
Forum|alt.badge.img
  • Author
  • Varsity II
  • 61 replies
  • June 23, 2023

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings