Some values are duplicating on Generic Inquiry

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

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 

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

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?

OK, 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.

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.