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!
@meganfriesen37 seeing your answer here and you mentioned that it’d be tricky if taxes need to be displayed on the report.
We have a request right now to provide project totals on the AR invoice that is delivered - contract/revenue amount, balance remaining, paid-to-date, etc. Everything is calculating… except tax is causing discrepancies. It only calculates tax at time of invoice, so it’s calculating just the taxable amount on the specific invoice. We’re struggling to figure out how to display the total “estimated tax” on the project.
I am regrouping with dev tomorrow, but curious if you have any insights to share…
@sean21 - Luckily for me, I’m in Canada so our Sales taxes tend to be way easier, as we only have federal and provincial taxes. I’ve done some basic hard coded calculations before in some of the invoicing templates with something to the effect of the following (I don’t remember the specifics off the top of my head)
If (Revenue Budget Tax Category = Taxable and Revenue Tax Zone = AB, 0.05, IF (Revenue Budget Tax Category = Taxable and Revenue Tax Zone = BC, 0.12, etc for other required provinces, if anything else then 0) * Revenue Budget Revised Amount
It’s not pretty and it counts on the fact that our sales taxes don’t change often. I don’t have much assistance on the US side where you have way more complex sales taxes.
@sean21 - Luckily for me, I’m in Canada so our Sales taxes tend to be way easier, as we only have federal and provincial taxes. I’ve done some basic hard coded calculations before in some of the invoicing templates with something to the effect of the following (I don’t remember the specifics off the top of my head)
If (Revenue Budget Tax Category = Taxable and Revenue Tax Zone = AB, 0.05, IF (Revenue Budget Tax Category = Taxable and Revenue Tax Zone = BC, 0.12, etc for other required provinces, if anything else then 0) * Revenue Budget Revised Amount
It’s not pretty and it counts on the fact that our sales taxes don’t change often. I don’t have much assistance on the US side where you have way more complex sales taxes.
Thanks for that insight. This is basically what I was thinking TBH. Based on the Revenue Tax Zone and perhaps an attribute, but hard coding makes sense too. Fortunately this client is in CAN so it should be somewhat straightforward, similar to how you presented it!
