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.
@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!
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. :-)
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.
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.
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!
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.