I figure this is an issue with joins but I struggle with knowing what to do to fix or how to experiment to fix this type of problem. Any advice is appreciated.
Yeah, like @Banderson said… I just pulled it in an ran it (after changing it to not be updateable and cross-company) and it ran fine for me. So, probably your data conflicting with your calc fields.
while this isn’t wrong… it’s not right either… Setting this to distinct is generally a band aid when you don’t understand the data. Sure it has it’s purpose, but if you don’t know exactly why you are getting duplicates, isn’t the best idea.
You have duplicates because the part mtl and partOpr tables have multiple rows of data related to the job assembly.
Also, what data are you trying to get? Because it’s probably in the job tables and you’ll run into potential problems looking at the master instead.
Completely agree. It works. It’s not the right answer most of the time. Best practice is to try to eliminate (or understand) the duplicates based on the tables and fields you added.
I’ve found the most useful reasons for using distinct are
A) When I need a quick band-aid to fix a BAQ that is pulling duplicate rows, and someone needs it fixed quickly so their dashboard is correct, or something along those lines. I’ve run into this a lot when I have to make a fix on someone else’s BAQ, and I want to get the user accurate data quickly. Then I have the time to go back and correct the actual Query structure.
B) In a subquery when I need to tie two tables together but, but a direct relationship doesn’t exist. For example, if I wanted to get the Shipment date(s) for an order, I’d join OrderHed > ShipDtl > ShipHead to get ShipHead.ShipDate, but that would introduce a new row for every shipment line. I can use distinct to only return the ShipDate(s) tied to that order.
