Open Order Report - Misc Charges being pulled in under wrong line

,

I was asked to generate an open order report for everything with a misc charge attached to it. After the first attempt we noticed a ton of duplicates. After we investigated, we noticed that each misc charge was being applied to each and every line (even with FIRST set on each of the charges). So for example, if line 1 has 2 misc charges, lines 2-10 will all display 2 misc charges even though they should only be applying to the first line. But if you look in the SO Entry screen, you will see that only the first line has the misc charges attached to it.

Is there a way i can modify the report to only show a line IF it has misc charges attached? Below is my current setup. the criteria on each table item is openLine/Order=True and VoidOrder=False. I did have the release table in there at some point but i thought that was causing the duplicates so i removed it. Ive also experimented with switching the join types but that still displays misc charges on the wrong lines.

image

OpenOrders_Tooling Query Phrase.txt (1.4 KB)

OrderMsc contains all misc charges. Whether they are applied to the Header, or applied to the Line.

1 Like

To see the misc charges PER LINE, you need to link the OrderMsc table to the OrderDtl table instead of the OrderHed table. OrderMsc.OrderLine will link to OrderDtl.OrderLine.

1 Like

this did the job i was looking for! thank you! I am however having an issue with what @cfinley mentioned; im getting misc charges on all lines when we’d prefer them to hide once the first line item (which most of the misc charges apply solely to) is closed. i guess we need to either reconfigure how we make SO’s or find another way to hide misc charges after the first line is closed.

Make sure that you have the criteria on OrderDtl.OpenLine = TRUE, and that your join is “matching records from OrderDtl and OrderMsc”. Closed lines (and therefore miscellaneous charges linked to them) should not show up.

If the line is actually not closed, that is a different problem.