I am very new to Epicor and am trying to create a BAQ that gives me the Job Scrap and Material Unit Costs for those job transactions. For this, I have used LaborDtl, PartTran and JobMtl tables. The output that I am getting for this baq is giving me ecactly 4x duplicated rows for each job. I have changed the subquery options to get Distinct values as well but i am getting the same output. Could someone help me out with this please?
Thank you
You got one-to-many tables in PartTran and JobMtl. Check the trantypes in PartTran to see if you can exclude some. May have to move PartTran & JobMtl to a subquery then use Aggregate formulas.
Randy,
Thank you for your quick response. I tried changing the relationships but that didn’t work.
How would I move the two tables you mentioned to a subquery? I see an option to create a new subquery. Does it already have options to use aggregate functions?
Im sorry, its too many questions.
To move tables to a subquery, disconnect the link between LaborDtl and PartTran, then click-drag a box around the two tables, right-click on PartTran and select “Move to Subquery”.
1 Like
Thank you, I was able to do that.
How would I go about joining these two? I tried to add a union subquery and I get a Bad SQL statement error.
Set the new at “innerSubQuery” type then it’ll be available to drag into the Top level query and connect like a normal table.
1 Like
@FionaLas , I would highly recommend checking out the help files, specifically the tools user guides. While I wouldn’t say the help system is spectacular most places, they actually did a really nice job with the BAQ section and have some case studies with step by step examples to show you how some of this works. It’s going to get you a lot farther a lot faster then getting little bits and pieces here. After you get through that, if you still have question, I believe you will be in a better place to ask them and get better answers, and you’ll move from “I don’t know what I don’t know” to “I know what I don’t know” which can be a huge step.
5 Likes
Thank you very much for your help, Randy. I will try that and see if my BAQ is giving the desired output.
Thank you Brandon, I will check it out.
You can also try by changing the Options to show only Distinct values:
I have it set to distinct, it didn’t change anything unfortunately. Its duplicating exactly 4X so there has to be something in the table joins that I am doing wrong 
Can you show your data? If there are “Duplicate” rows, then something in the data is different.
And, I say this to a lot of people, but be careful with the distinct rows setting. Just using it because you don’t know why you are getting duplicates can lead to issues in your queries.
1 Like
Yes, of course. I have attached the BAQ and here’s the code from the General Tab.
select distinct
[LaborDtl].[Complete] as [LaborDtl_Complete],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[PartTran].[PartNum] as [PartTran_PartNum],
(LaborDtl.ScrapQty * JobMtl.QtyPer) as [Calculated_Material_Scrap],
(Material_Scrap * JobMtl.EstUnitCost) as [Calculated_Scrap_Cost],
[PartTran].[MtlUnitCost] as [PartTran_MtlUnitCost]
from Erp.LaborDtl as LaborDtl
inner join Erp.PartTran as PartTran on
LaborDtl.Company = PartTran.Company
and LaborDtl.JobNum = PartTran.JobNum
inner join Erp.JobMtl as JobMtl on
PartTran.Company = JobMtl.Company
and PartTran.JobNum = JobMtl.JobNum
InventoryScrapVariance.baq (22.3 KB)
The BAQ doesn’t include the data in your database. I probably won’t see the same things you are seeing because my data is not the same as yours. The BAQ is just the question (that’s why it’s called a query). The answer depends on your data. Can you post a screen shot of the rows that you think are duplicate?
Yes, of course.
We track scrap using a custom dashboard but when I try to duplicate the dashboard and add another table, it crashes.
Here is the screenshot from the Scrap Reporting Dashboard, the kind of output I am looking for.
Doesn’t look like duplicates as the Materia_Scrap, Scrap_Cost or Mtl_Unit_Costs are different. I would recommend on trying to a few more columns/fields to your query such as OprSeq, EmployeeNum, LaborDtlSeq, ClockInDate, etc. Adding this fields may give you more visibility of why those records seems to be duplicated.
I have all those added except for the employeeNum, I will try adding that and see what it looks like. The data is definitely duplicated, just not in the same order. JobNum and Scrap_Cost is the only column that needs to be compared in the two tables.
With multiple possible runs of the same operation, they might look like duplicates but not be…
Also, looking at the data if you have cost adjustment to a material it might contain different costs thus looking like duplicate records.