Solved

Problem Pulling PMProjectRevenueTotal and ARInvoice to same GI

  • 21 December 2023
  • 4 replies
  • 58 views
M
Rank
Userlevel 4
Badge

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!

icon

Best answer by mnecaise 17 January 2024, 18:54

View original

4 replies

Laura02
Rank
Badge +18

Hello,

Have you tried Grouping the GI by the Project, aggregating the AR totals (Sum CuryRevisedAmount, CuryUnpaidBalance, and CuryRetainageUnreleased )?  Example:

 

Laura

 

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
M
Rank
Userlevel 4
Badge

@Laura02 Hi Laura, thanks for the suggestion. I just tried it, and my contract amount is still being multiplied based on how many times the project has been invoiced. If I remove either the PMProjectRevenueTotal or ARInvoice tables everything is correct, but it doesn’t give me all the data I need. I’m 99% sure it has to do with the tables both having the same parent/child fields, but there don’t seem to be any other fields I can choose to make the relationship work.

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! 

M
Rank
Userlevel 4
Badge

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.

 

Chris Hackett
Rank
Userlevel 7
Badge

Thank you for sharing your solution with the community @mnecaise!

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*.

Reply


Terms & ConditionsCookie settings
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