SOLUTION:
this was actually easier to accomplish than i thought it was going to be, thanks to @gpayne for pointing me in the right direction, the articles i found and a little assistance from chatGPT on getting the syntax right made this successful!
my overall BAQ now looks like this:
the calculated fields to allow to ‘group’ the job materials in one line looks as follows:
and then simply using the group-by makes the BAQ work:
Below is the full code from the BAQ and hopefully this will help someone else in the future!
As always thank you to you all for your help!
select
[JobHead].[StartDate] as [JobHead_StartDate],
[OrderDtl1].[RequestDate] as [OrderDtl1_RequestDate],
[JobHead].[JobNum] as [Job],
[JobHead].[PartDescription] as [Description],
(JobHead.ProdQty - JobHead.QtyCompleted) as [Calculated_Calculated_RemainingQty],
(MAX(
CASE
WHEN JobMtl.MtlSeq = 10 THEN
CONCAT(
COALESCE(Part.CommercialBrand, ''), ' ',
COALESCE(Part.Specification, ''), ' ',
COALESCE(Part.CommercialColor, ''), ' ',
CAST(ROUND(COALESCE(Part.Thickness, 0), 1) AS DECIMAL(10,1)), 'mm - ',
COALESCE(OrderDtl1.BackProfile_c, ''), ' ',
CASE
WHEN OrderDtl1.BackFilm_c = 0 THEN '- No Film'
ELSE '- With Film'
END
)
END
)) as [Calculated_Calculated_Internal_Face],
((max(CASE
WHEN JobMtl.MtlSeq = 20 THEN
concat(Part.CommercialBrand
, ' '
, Part.Specification
,' '
, Part.CommercialCategory
, ' - '
, CAST(round(Part.Thickness, 0) AS decimal(10, 0))
,'mm')
END))) as [Calculated_Calculated_Core],
(MAX(
CASE
WHEN JobMtl.MtlSeq = 30 THEN
CONCAT(
COALESCE(Part.CommercialBrand, ''), ' ',
COALESCE(Part.Specification, ''), ' ',
COALESCE(Part.CommercialColor, ''), ' ',
CAST(ROUND(COALESCE(Part.Thickness, 0), 1) AS DECIMAL(10,1)), 'mm - ',
COALESCE(OrderDtl1.FrontProfile_c, ''), ' ',
CASE
WHEN OrderDtl1.FaceFilm_c = 0 THEN '- No Film'
ELSE '- With Film'
END
)
END
)) as [Calculated_Calculated_External_Face],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[Customer].[Name] as [Customer_Name],
[ActiveLabor1].[ActiveLabor_ActiveTrans] as [ActiveLabor_ActiveTrans],
[JobOper].[OpComplete] as [JobOper_OpComplete],
[LaborInfo].[Calculated_ClockInDate] as [Calculated_ClockInDate],
(case
when ActiveLabor1.ActiveLabor_ActiveTrans = 1 then 'RED'
when convert(varchar,LaborInfo.Calculated_ClockInDate, 101) <> '' AND JobOper.OpComplete = 0 then 'YELLOW'
else 'NONE'
end) as [Calculated_Calculated_ColourCode]
from Erp.JobHead as [JobHead]
inner join Erp.JobOper as [JobOper] on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
and ( (JobOper.OpCode = 'LAM' ) )
inner join Erp.JobOpDtl as [JobOpDtl] on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
inner join Erp.ResourceTimeUsed as [ResourceTimeUsed] on
JobOpDtl.Company = ResourceTimeUsed.Company
and JobOpDtl.JobNum = ResourceTimeUsed.JobNum
and JobOpDtl.AssemblySeq = ResourceTimeUsed.AssemblySeq
and JobOpDtl.OprSeq = ResourceTimeUsed.OprSeq
and JobOpDtl.OpDtlSeq = ResourceTimeUsed.OpDtlSeq
and ( ResourceTimeUsed.WhatIf = false )
left outer join (select
[LaborDtl].[Company] as [LaborDtl_Company],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
(max(LaborDtl.ClockInDate)) as [Calculated_ClockInDate]
from Erp.LaborDtl as [LaborDtl]
group by
[LaborDtl].[Company],
[LaborDtl].[JobNum],
[LaborDtl].[AssemblySeq],
[LaborDtl].[OprSeq]) as [LaborInfo] on
JobOper.Company = LaborInfo.LaborDtl_Company
and JobOper.JobNum = LaborInfo.LaborDtl_JobNum
and JobOper.AssemblySeq = LaborInfo.LaborDtl_AssemblySeq
and JobOper.OprSeq = LaborInfo.LaborDtl_OprSeq
left outer join (select
[ActiveLabor].[Company] as [ActiveLabor_Company],
[ActiveLabor].[JobNum] as [ActiveLabor_JobNum],
[ActiveLabor].[AssemblySeq] as [ActiveLabor_AssemblySeq],
[ActiveLabor].[OprSeq] as [ActiveLabor_OprSeq],
[ActiveLabor].[ActiveTrans] as [ActiveLabor_ActiveTrans]
from Erp.LaborDtl as [ActiveLabor]
where (ActiveLabor.ActiveTrans = true)
group by
[ActiveLabor].[Company],
[ActiveLabor].[JobNum],
[ActiveLabor].[AssemblySeq],
[ActiveLabor].[OprSeq],
[ActiveLabor].[ActiveTrans]) as [ActiveLabor1] on
LaborInfo.LaborDtl_Company = ActiveLabor1.ActiveLabor_Company
and LaborInfo.LaborDtl_JobNum = ActiveLabor1.ActiveLabor_JobNum
and LaborInfo.LaborDtl_AssemblySeq = ActiveLabor1.ActiveLabor_AssemblySeq
and LaborInfo.LaborDtl_OprSeq = ActiveLabor1.ActiveLabor_OprSeq
inner join Erp.JobMtl as [JobMtl] on
JobHead.Company = JobMtl.Company
and JobHead.JobNum = JobMtl.JobNum
left outer join Erp.Part as [Part] on
JobMtl.Company = Part.Company
and JobMtl.PartNum = Part.PartNum
inner join Erp.JobProd as [JobProd1] on
JobHead.Company = JobProd1.Company
and JobHead.JobNum = JobProd1.JobNum
inner join Erp.OrderDtl as [OrderDtl1] on
JobProd1.Company = OrderDtl1.Company
and JobProd1.OrderNum = OrderDtl1.OrderNum
and JobProd1.OrderLine = OrderDtl1.OrderLine
inner join Erp.OrderRel as [OrderRel] on
OrderDtl1.Company = OrderRel.Company
and OrderDtl1.OrderNum = OrderRel.OrderNum
and OrderDtl1.OrderLine = OrderRel.OrderLine
inner join Erp.Customer as [Customer] on
OrderRel.Company = Customer.Company
and OrderRel.MFCustNum = Customer.CustNum
where (JobHead.JobFirm = true) and ( (JobHead.JobFirm = 1 ) )
group by
[JobHead].[StartDate],
[OrderDtl1].[RequestDate],
[JobHead].[JobNum],
[JobHead].[PartDescription],
[JobHead].[ProdQty],
[JobHead].[QtyCompleted],
[OrderRel].[OrderNum],
[OrderRel].[OrderLine],
[OrderRel].[OrderRelNum],
[Customer].[Name],
[ActiveLabor1].[ActiveLabor_ActiveTrans],
[JobOper].[OpComplete],
[LaborInfo].[Calculated_ClockInDate]
