I have a report that currently pulls information like Invoice Numbers, Product ID, Quantity Shipped, Management Cost and then summarizes all of this information to give me total management cost, profit, etc.
Currently the report pulls the "Invoice.InvoiceMgmtCost". However, we have recently updated all of our management costs in ACCTivate and I would like the report to use the ProductWarehouseSummary.MgmtCost instead. However, when I change the field to "PoductWarehouseSummary.MgmtCost" it starts giving me duplicate entries using the same Invoice Number multiple times, and therefore giving me incorrect totals.
I have tried using the "Select Distinct Records" and it does not work.
I cannot simply suppress the duplicates because they still interfere with my running totals.
Does anyone have a clean way of fixing this that will not interfere with any formulas I already have inside the report? 😭