Not sure if anyone already has something like this, but we are trying to identify any jobs that have sub-assemblies where “Get Details” has not been run. We seem to have this happen on our jobs somewhat regularly as we add some sub-assemblies manually. I’ve been trying to push to build fully built out bills of materials, but we aren’t quite there as a company yet. I don’t know if this can be done with a BPM or possibly just a quick dashboard, but was hoping somebody might already have a solution that you use. Thanks in advance for any help!
I created somethign similar it look for an specific assembly on the tree but it can be modified to find assemblies with no details. you will need a baqview to do this to run it on the tree context menu
Can you provide any additional details on this?
I’m looking to create a dashboard that finds all open jobs that have a subassembly (not Assembly 0) that has not had the Get Details process run on it (no additional subassemblies AND no materials on the subassembly). Our engineering department occasionally forgets to run the get details process and misses one of the subassemblies. I have most of the BAQ built and used the Child=-1 to find a subassembly that has no additional subassembly on it, but am having difficulty finding 0 materials on those same subassemblies that have no additional subassemblies.
E.g. Assembly 0 has subassembly 1 >> subassembly 1 has no additional subassembly and no materials on it. This is what I’m trying to find easily.
TBD if this could apply to your case…
The attached example, simple BAQ that
- checks jobs/asms that are not complete
- and does not have any mtls or ops
- it currently includes asm zero but could filter those out too
CUS-JobNoDetails.baq (37.3 KB) .
I think this might work, but when I imported it into my system, I don’t think it imported properly. I’m not seeing how you got the final result as it looks like your Assemblies subquery is on the TopLevel and the TopLevel query does not include the Assemblies subquery on it. Any help would be appreciated. I think I can even re-create it if you can just send the SQL code. Thanks for the help on it!
UPDATE: Mainly, I’m not seeing how you added the Calculated fields as Table Criteria on the JobAssemblies subquery.
Hmmm… wonder if it was because it is from 10.1.6…
Here is the SQL
select
[JobAssemblies].[JobAsmbl_Company] as [JobAsmbl_Company],
[JobAssemblies].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
[JobAssemblies].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAssemblies].[Calculated_JobMtls] as [Calculated_JobMtls],
[JobAssemblies].[Calculated_JobOps] as [Calculated_JobOps]
from (select
[JobAsmbl].[Company] as [JobAsmbl_Company],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
(ISNULL(cJobMtlCount.Calculated_cMtlCount,0)) as [Calculated_JobMtls],
(ISNULL(cJobOpCount.Calculated_cJobOpCount,0)) as [Calculated_JobOps]
from Erp.JobAsmbl as JobAsmbl
left outer join (select
[JobMtl].[Company] as [JobMtl_Company],
[JobMtl].[JobNum] as [JobMtl_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
(count( JobMtl.MtlSeq )) as [Calculated_cMtlCount]
from Erp.JobMtl as JobMtl
group by [JobMtl].[Company],
[JobMtl].[JobNum],
[JobMtl].[AssemblySeq]) as cJobMtlCount on
JobAsmbl.Company = cJobMtlCount.JobMtl_Company
and JobAsmbl.JobNum = cJobMtlCount.JobMtl_JobNum
and JobAsmbl.AssemblySeq = cJobMtlCount.JobMtl_AssemblySeq
left outer join (select
[JobOper].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
(count( JobOper.OprSeq )) as [Calculated_cJobOpCount]
from Erp.JobOper as JobOper
group by [JobOper].[Company],
[JobOper].[JobNum],
[JobOper].[AssemblySeq]) as cJobOpCount on
JobAsmbl.Company = cJobOpCount.JobOper_Company
and JobAsmbl.JobNum = cJobOpCount.JobOper_JobNum
and JobAsmbl.AssemblySeq = cJobOpCount.JobOper_AssemblySeq
where (JobAsmbl.JobComplete = false)) as JobAssemblies
where (JobAssemblies.Calculated_JobMtls <= 0 and JobAssemblies.Calculated_JobOps <= 0)
order by JobAssemblies.JobAsmbl_JobNum, JobAssemblies.JobAsmbl_AssemblySeq
How did you get the calculated fields in the Table Criteria?
These might help
Subqueries
top level criteria - MtlCount <= 0 and OpCount <- 0
and sub criteria - JobHead.Complete = false
the two other subs just count the JobMtls and JobOps - and used with open joins
I got it figured out! Thanks for all the info and help getting it solved! This will help us temendously!