I am trying to make a BAQ to see the most recent costs of the parts we manufacture but I only want it give me the most recent job for each part. Is there a way to do this? Please help!
Yes, there a bunch of ways depending on what you want. What do you have so far?
The first few columns are: Part, Job, Start Date, Qty
And then I have columns for the actual cost, the estimated cost, the difference, and a yes/no column that indicates if the actual cost is greater than the estimated. And I have these columns 3 times for each different cost we account for.
The base (the first rectangle that the other ones branch off from) in the Phrase Build is Erp.Part
Can you post some screen shots and/or the SQL phrase from the general tab?
Also, Can I assume that you have read through the BAQ section in the tools user guide? If you haven’t you should drop what you are doing and read through that first. It will give you some of the basics that you need. Pay special attention when you get to aggregate functions, that’s what you are going to need for this BAQ. Once you are done reading through that, and creating a couple BAQ’s following along the case studies, come back with questions.
1 Like
I would make a sub-query that of the PartTran table with the following:
- Table Criteria: TranType = MFG-STK or MFG-CUS
- Display fields:
- Company
- PartNum
-
MAX(TranDate)MAX(TranNum)
Then relate that to the PartTran on the main query (relating all three fields). You should get the last PartTran record for each PartNum for trans of MFG-STK or MFG-CUS. The MtlUnitCost should give you the answer
with the relationships:
the inner-subquery:
I know the calc field name is Date, but its actually the TranNum
1 Like
I tried this but I am getting the error below which is so vague. Do you know what this could be referring to?
The only place where you get to use any code is in calculated fields, so It’s probably in there.
If you can post the SQL from the first tab of the BAQ, that will help us help you. We need more info.
double check the table relationships.
select
[Part].[PartNum] as [Part_PartNum],
[JobHead].[JobNum] as [JobHead_JobNum],
[PartTran1].[TranNum] as [PartTran1_TranNum],
[JobHead].[StartDate] as [JobHead_StartDate],
[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
[JobAsmbl].[TLAMaterialCost] as [JobAsmbl_TLAMaterialCost],
[JobAsmbl].[TLEMaterialCost] as [JobAsmbl_TLEMaterialCost],
(JobAsmbl.TLAMaterialCost - JobAsmbl.TLEMaterialCost) as [Calculated_DiffMtl],
(case
when JobAsmbl.TLAMaterialCost > JobAsmbl.TLEMaterialCost then 'yes'
else 'no'
end) as [Calculated_UpdateMtl],
[JobAsmbl].[TLABurdenCost] as [JobAsmbl_TLABurdenCost],
[JobAsmbl].[TLEBurdenCost] as [JobAsmbl_TLEBurdenCost],
(JobAsmbl.TLABurdenCost - JobAsmbl.TLEBurdenCost) as [Calculated_DiffBurden],
(case
when JobAsmbl.TLABurdenCost > JobAsmbl.TLEBurdenCost then 'yes'
else 'no'
end) as [Calculated_UpdateBurden],
[JobAsmbl].[TLASubcontractCost] as [JobAsmbl_TLASubcontractCost],
[JobAsmbl].[TLESubcontractCost] as [JobAsmbl_TLESubcontractCost],
(JobAsmbl.TLASubcontractCost - JobAsmbl.TLESubcontractCost) as [Calculated_DiffSubcon],
(case
when JobAsmbl.TLASubcontractCost > JobAsmbl.TLESubcontractCost then 'yes'
else 'no'
end) as [Calculated_UpdateSubcon]
from Erp.Part as Part
inner join Erp.JobHead as JobHead on
Part.PartNum = JobHead.PartNum
and Part.Company = JobHead.Company
and ( JobHead.JobComplete = yes and JobHead.JobNum like '%J' and JobHead.QtyCompleted > 0 and JobHead.StartDate > '1/1/2019' and JobHead.StartDate < Constants.Today )
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join (select
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum],
[JobAsmbl1].[TLAMaterialCost] as [JobAsmbl1_TLAMaterialCost],
[JobAsmbl1].[TLEMaterialCost] as [JobAsmbl1_TLEMaterialCost],
(JobAsmbl1.TLAMaterialCost - JobAsmbl1.TLEMaterialCost) as [Calculated_MaterialDifference],
[JobAsmbl1].[TLABurdenCost] as [JobAsmbl1_TLABurdenCost],
[JobAsmbl1].[TLEBurdenCost] as [JobAsmbl1_TLEBurdenCost],
(JobAsmbl1.TLABurdenCost - JobAsmbl1.TLEBurdenCost) as [Calculated_BurdenDifference],
[JobAsmbl1].[TLASubcontractCost] as [JobAsmbl1_TLASubcontractCost],
[JobAsmbl1].[TLESubcontractCost] as [JobAsmbl1_TLESubcontractCost],
(JobAsmbl1.TLASubcontractCost - JobAsmbl1.TLESubcontractCost) as [Calculated_SubcontractDifference]
from Erp.JobAsmbl as JobAsmbl1) as Material on
JobHead.JobNum = Material.JobAsmbl1_JobNum
and ( Material.Calculated_MaterialDifference > 0 or Material.Calculated_BurdenDifference > 0 or Material.Calculated_SubcontractDifference > 0 )
inner join Erp.CostPart as CostPart on
Part.PartNum = CostPart.PartNum
inner join Erp.PartTran as PartTran1 on
Part.PartNum = PartTran1.PartNum
and Part.Company = PartTran1.Company
inner join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
(max( PartTran.TranNum )) as [Calculated_Max_Tran_Num]
from Erp.PartTran as PartTran
where (PartTran.TranType = 'MFG-STK' or PartTran.TranType = 'MFG-CUS')
group by [PartTran].[Company],
[PartTran].[PartNum]) as Job_Limit on
(PartTran1.Company = Job_Limit.PartTran_Company
and (PartTran1.TranNum = Job_Limit.Calculated_Max_Tran_Num
and (PartTran1.PartNum = Job_Limit.PartTran_PartNum
What is the issue?
I get this error
What is your join on the JobLimit Subquery and the PartTran1 table look like in the GUI?
[PartTran].[PartNum]) as Job_Limit on
(PartTran1.Company = Job_Limit.PartTran_Company
and (PartTran1.TranNum = Job_Limit.Calculated_Max_Tran_Num
and (PartTran1.PartNum = Job_Limit.PartTran_PartNum
There are a bunch of ) missing should be
[PartTran].[PartNum]) as Job_Limit on
(PartTran1.Company = Job_Limit.PartTran_Company)
and (PartTran1.TranNum = Job_Limit.Calculated_Max_Tran_Num)
and (PartTran1.PartNum = Job_Limit.PartTran_PartNum)also
JobHead.JobComplete = yes
might need to change that to
JobHead.JobComplete = true
No need for joining PartTran.PartNum to Job_Limit.PartTran_PartNum, as the TranNum is unique to ensure the records match
(I know I did it in my example, but it wasn’t necessary)