Having some issues getting a BAQ to show me proper data.
I want to see Active MFG parts that are processed by MRP for each site and their production run rates.
I am using PartPlant - Active = TRUE / SourceType = M / ProcessMRP = TRUE
PartRev - Approved = TRUE
ECOopr to show ProdStandard
I am getting far too many results than I expect to see.
What am I missing here?
Are you getting multiple rows per part? Or just more parts than you figured you would receive in your result set?
Are you putting a criteria on PartPlant.Plant? Otherwise you’d get a row for each plant where the part meets the criteria (but perhaps that’s the goal).
Could be your joins. Can you post up a pick of the query? Need more to go on.
Thanks for your response.
Yes, I am getting multiple rows per part. I am not wanting to see multiple rows per part, just the current run rates for each part (active rev) in each plant.
Seeing as you’re using Part Plant, that can be a many-to-one, right? The Part could exist in multiple plants, so you’re going to get multiple rows. Do you have multiple sites?
Same with Part Rev. A Part could have multiple revisions, so you would again have multiple rows.
But I expect the issue may be the fact that you’re using ECO tables. There are records for every ECO group that has ever touched this part. In your ECOOpr table, add the GroupID as a display table. Do you see multiple results? If so, you may want to use a subquery on ECOGroup with a calculated field for max(CloseDate) or something along those lines. I would expect that to be the group that made the most recent change to the method. But some companies leave groups open for long periods… so this could be an issue. But then you could join that subquery to ECOOpr so only results for the ECOGroup that had the latest CloseDate would be displayed.
My thoughts at this point anyway.
Edit: See @jkane MUCH better solution to that portion 
Agree with @dcamlin , you should not be using ECOOpr. You should use PartOpr instead.
Thanks @jkane, I almost tagged you on that post because I assumed there was a better table option but couldn’t think of it. My head got stuck in ECO world.
Thanks, this really narrowed my search results down and I can see now what I want to see.
You were right! it was the ECO tables. Changed to PartOpr as @jkane suggested and I am happy with results
I say this every time I see this. ECO tables are working tables. Not history tables, and not master tables. Treat them like temp tables and don’t ever use them unless you are specifically looking at things that are currently checked out in engineering workbench. I wish Epicor would just clear them out when parts get checked in because it’s super easy to get sucked into thinking that these are reliable tables to use.
