BAQ calculated field to show part number of parent assembly

I am building a BAQ that is showing all the assemblies and subassemblies associated with a certain operation. I have the assembly sequence field and the parent field from the JobAsmbl table and I am trying to make a calculated field to display the part number of a part’s parent assembly but can’'t figure out how to do it. The way I see it working is it looks at the parent assembly sequence, finds the assembly that has the same assembly sequence number & job number, then returns the part number associated with that assembly.

I think a calculated field would be the best way to do this but if there is a way to do this with subqueries or something I am wide open to suggestions.

I think a subquery may be needed because you have to reference a different assembly record all together. Your child record would be joined on company, job, Child.ParentAssy = Parent.AsmblSeq

How do I make the field show up in my results? I’m not sure if I set it up right, I’ve never used subqueries so I don’t quite know how they work. this is what I have.

This is what my subquery is like, it is an innerSubQuery

This is what my results look like, grouped by jobNum. A specific part’s parent may not be in the list because I am filtering by a specific OpCode

What are you trying to get? Operation information?
There is a Job Part Number then there are job assemblies and part numbers.

You have jobmat and job operations that you can link to the job assembly. Is there another level to the MOM that you are trying to get to? Or are you trying to show the material parts and the assembly part on one line?

something like this?
job.baq (26.1 KB)

I am trying to make an updatable dashboard that will allow our machine shop supervisor to view any parts that are associated with the machine shop operation so that he can print labels and assign operation time. The BAQ you attached is only returning the higher level assemblies.

The baq returns the assembly and the material needed for the assembly, is there another layer?

If you remove the JobAsmbl table, you will get all of the parts and operations You will need to add more to the join to get the JobMtl and Job Opr synced up.

What does the query phase say? I might be missing a table in your sub query.

Think I get what you are asking now. To show the parent part information… How does this look?

job2.baq (44.7 KB)

1 Like

The main assembly on the job has 6 different operations needed to complete it, one of which is machine shop. under that assembly there are 8 subassemblies, some of which may be assigned to that top level machine shop operation. Some of those have subassemblies under them that may require a machine shop operation, which I have nested under them.

Here’s what my structure generally looks like, notice how the first subassembly is not assigned to a specific op(although it will probably end up being MECHASSM), but one of it’s subassemblies has a MACHSHOP op. I would need that part, ASM: 16, to show up in my parts list, with Mtl 10 listed as a material, and a Used On column that shows ASM: 5’s name.

sorry for the long explanation I hope I cleared up what I’m trying to do. here’s the query phrase, I’ll check out that second BAQ and let you know how it works.

select
(0) as [Calculated_DrawingPrinted],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobOper].[DueDate] as [JobOper_DueDate],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[JobAsmbl].[TLASetupHours] as [JobAsmbl_TLASetupHours],
[JobAsmbl].[TLAProdHours] as [JobAsmbl_TLAProdHours],
(0) as [Calculated_Programmed],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[Parent] as [JobAsmbl_Parent],
(5) as [Calculated_Priority],
[JobOper].[OpCode] as [JobOper_OpCode]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.JobMtl as JobMtl on
JobAsmbl.Company = JobMtl.Company
And
JobAsmbl.JobNum = JobMtl.JobNum
And
JobAsmbl.AssemblySeq = JobMtl.AssemblySeq

inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
And
JobAsmbl.JobNum = JobOper.JobNum
And
JobAsmbl.AssemblySeq = JobOper.AssemblySeq
and ( JobOper.DueDate is not null and JobOper.OpCode = ‘MACHSHOP’ )

where (not JobAsmbl.AssemblySeq = 0)
and JobAsmbl.Company = ANY (select JobAsmbl1_Company from ((select
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobAsmbl1].[Parent] as [JobAsmbl1_Parent],
[JobAsmbl1].[Company] as [JobAsmbl1_Company],
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum]
from Erp.JobAsmbl as JobAsmbl1)) as SubQuery2) and JobAsmbl.JobNum = ANY (select JobAsmbl1_JobNum from ((select
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobAsmbl1].[Parent] as [JobAsmbl1_Parent],
[JobAsmbl1].[Company] as [JobAsmbl1_Company],
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum]
from Erp.JobAsmbl as JobAsmbl1)) as SubQuery2) and JobAsmbl.AssemblySeq = ANY (select JobAsmbl1_Parent from ((select
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobAsmbl1].[Parent] as [JobAsmbl1_Parent],
[JobAsmbl1].[Company] as [JobAsmbl1_Company],
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum]
from Erp.JobAsmbl as JobAsmbl1)) as SubQuery2)
order by JobOper.DueDate

That BAQ still didn’t drill down far enough in assemblies but that subquery seems to work perfectly

What do you mean not far enough? Sorry our BOMs only go 1 down, so I am not able to test out more than one level.

Can you show the output of the BAQ? What is missing based on your 3836-010 job from above?

Is the Mtl: 10 showing up when you expand the Materials click on the ASM 16? If not, then we need to bring in another table. We are close though.

If you need to go down the BOM recursively, you’re probably looking at a CTE query.

I don’t think we need to jump into CTE just yet.

If you just use the two table jobmtl and jobopr, you will get all material for all operations on all jobs and seq, regardless of level.

Once you are good with the data being returned, then join to the jobasmbl part to get the assembled part name and parent assembly number.

Then you can join to the parent assembly (jobasmbl again) to get the parent assembly part num.

1 Like

I figured it out. I was told that any assemblies that go through the machine shop have a standard part number format so I am just pulling all assemblies on all jobs and then filtering them by parts that fit that format. Your subquery works great and I implemented it into my query. now I just have to figure out what to pull exactly for the production and setup times but I think I’m equipped to do that now. Thanks for the help!

I attached my query for the curious
MachineShopMainDashboard.baq (32.5 KB)

1 Like

great. don’t forget the JobOpDtl it might store some of what you are looking for.