I created a generic inquiry it was working correctly until I tried to add the payments received column. Now the contract amounts/billing amounts and payment amounts are wrong on ones with multiple invoices. I know the problem is creating duplicate lines for projects with multiple invoices. I tried the grouping function and getting rid of the sum function but that didn’t work so I think it how I joined the tables. I can’t figure out how to fix it. I attached the xml file.
Solved
Some values are duplicating on Generic Inquiry
Best answer by plambert
There are a couple of ways to keep your unrelated multi-record joins separate. One option if you’re on 24r1 or later is to make a GI that does the summation of the ARInvoice and join on that instead of directly - effectively a subquery.
Another is a technique of doing a fixed full join to separate the unrelated tables so they can join in and group together without interfering with one another. It’s frequently called the ‘Wayatt.ERP’ technique and I go into it in detail in another post. Check it out and adapt it to isolating the ARInvoice and PMProject DACs.
+1To fix these totals, you just need to add these numbers that should not be summed up into the GI grouping. All amount fields that are duplicating need to be added to the grouping.
1 person likes this
I tried adding the fields to the Grouping tab but now it’s giving me multiple lines for each project. I’m not sure I am doing that correctly. When I add them to the grouping tab it creates multiple project lines for each job
+1You might need to remove the project ID from the grouping... The grouping tab should only include the fields you want to report grouped by as well as numeric fields that you don’t want to aggregate. Any extra fields will only cause issues.
1 person likes this
Even removing that projectID but keeping the PMProject.ContractID it’s not grouping the project # together. It’s listing the same project# multiple times based on the number of invoices on the project. The issue seems to be with me joining the PMProject table to the ARInvoice table. With those tables joined it’s recking havoc on my report no matter how I try and group things. I think I need to go back to the drawing board.
I have the issue narrowed down but I still can’t find a way to make it work. I do want it to group by Project# which it is doing. The total payment on the project is pulling correctly.
It’s pulling in the # of lines for each project based on the number of invoices. So if the project has 2 invoices then it’s doubling the amounts of each column. If there are 8 invoices on the project then the amount in each column is 8 times the correct amount.
I tried adding grouping based on the AR invoice# and ProjectID. The numbers in each column on the individual lines are correct total for the project but I get 2 lines for projects with 2 invoices and 8 lines for projects with 8 invoices and so on.
The question is how do I get it only pull one line per project regardless of how many invoices there are?
+1OK, in this case you want ARInvoice.curyDocBal to be summed and not grouped. You should remove this field and all other ARInvoice fields from the grouping.
1 person likes this
I am actually trying to get the amount paid and not balance due so I had pulled AR.PaymentTotal which does pull in correctly. I had removed all fields from the grouping except for PMProject.ContractID. It’s still multiplying the balances in every column except for the ARPaymentTotal by the number of invoices.
Even if I make the row inactive ARPaymentTotal as long as the table join to ARInvoice is active it is causing the issue of multiplying by the number of invoices.
I have the PMProject left joined to the ARInvoice table with ContractID and ProjectID as the conditions of the join. I think that is what ultimately is causing the issue but I can’t find another related field on the Project table that would get me to the total paid. No matter what I still need the AR table.
There are a couple of ways to keep your unrelated multi-record joins separate. One option if you’re on 24r1 or later is to make a GI that does the summation of the ARInvoice and join on that instead of directly - effectively a subquery.
Another is a technique of doing a fixed full join to separate the unrelated tables so they can join in and group together without interfering with one another. It’s frequently called the ‘Wayatt.ERP’ technique and I go into it in detail in another post. Check it out and adapt it to isolating the ARInvoice and PMProject DACs.
1 person likes this
Thanks for the link to the other post. I’ll check it out.
1 person likes this
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.