I’m working on a GI that information is needed from both the PMProjectRevenueTotal and ARInvoice tables. Right now, I have both of these as child tables to the parent table of PMProject. However, both are coming up with the parent/child fields as contractid equals projectid, so my results are skewed. The projects themselves are only pulling in once, but the column values are multiplying based on how many times the project has been invoiced. Are there other tables/relations that will allow data from CuryRevisedAmount, CuryUnpaidBalance, and CuryRetainageUnreleased that won’t result in the inaccurate values? Thanks!
Answer
Problem Pulling PMProjectRevenueTotal and ARInvoice to same GI
Best answer by mnecaise
After some testing and viewing some other reports, I was able to come up with a solution. I ended up clearing out all the tables and relations to start over. From the Tables tab, I started with ARInvoice and added PMProject as a related table. Then from PMProject, I added PMProjectRevenueTotal as a related table. So far all my amounts seem to be pulling in the correct numbers. Doing it this way is only showing me projects that have invoices attached to them, verses all projects. However, this works for what I'm needing the GI for.
+19Hello,
Have you tried Grouping the GI by the Project, aggregating the AR totals (Sum CuryRevisedAmount, CuryUnpaidBalance, and CuryRetainageUnreleased )? Example:
Laura
~ Laura Barber, WM Synergy
I’m wondering if there might be another report already in Acumatica that would show the information we’re needing to see. We need an overview of project contract amount, total amount billed to date, including any unreleased retainage, and total amount paid to date. I looked at the AR Open Documents by Project report, but it doesn’t give all the information I’m looking for. My guess is if I add the additional tables needed, I’ll run into the same issue I’m having with the GI.
Any thoughts? Thanks!
After some testing and viewing some other reports, I was able to come up with a solution. I ended up clearing out all the tables and relations to start over. From the Tables tab, I started with ARInvoice and added PMProject as a related table. Then from PMProject, I added PMProjectRevenueTotal as a related table. So far all my amounts seem to be pulling in the correct numbers. Doing it this way is only showing me projects that have invoices attached to them, verses all projects. However, this works for what I'm needing the GI for.
Thank you for sharing your solution with the community
Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.