Hey every, I’m Trying to write a BAQ where only the first operation that is open, is what is displayed. The BAQ is designed to show us what we need and where it’s at. The problem is it will still show me where it will be going after where it’s at. I don’t need this information. Only the current uncompleted operation. Anyone have any ideas?
This is a huge deal to our company, and have been working and researching it for over 2 weeks. is there no way to do this?
What does your BAQ look like?
My boss wants to see everything on one list from FG part to sub components. so I’ve had to use Sub queries to reduce the amount of repeated lines.
So far, this is working perfectly. The only trouble I am having is it will show me all open operations, I just need the first open operation.
Thank you very much.
So after looking into some sql, I believe I came up with a solution to this, by using the distinct keyword in the subquery options tab. The description of distinct says that it specifies that only unique rows can display In the result set. so if I sort by job num, and job op seq, I thought I would get my required result. the first open op. being im not showing only closed ops. Does anyone know how to use this feature? and is there a way to change it in the code? seems like I cannot edit the code. even in developer mode and with a lot of privileges.
select
[Partwhse1].[PartWhse_PartNum] as [PartWhse_PartNum],
[JobMtl1].[JobMtl_PartNum] as [JobMtl_PartNum],
[Partwhse1].[PartWhse_WarehouseCode] as [PartWhse_WarehouseCode],
[Partwhse1].[PartWhse_OnHandQty] as [PartWhse_OnHandQty],
[JobOper].[QtyCompleted] as [JobOper_QtyCompleted],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobOper].[RunQty] as [JobOper_RunQty],
[Partwhse1].[Part_ProdCode] as [Part_ProdCode],
[JobOper].[JobComplete] as [JobOper_JobComplete],
[JobOper].[OpComplete] as [JobOper_OpComplete],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOper].[StartDate] as [JobOper_StartDate],
[JobOper].[DueDate] as [JobOper_DueDate],
[Resource1].[SetupGrp_SetupGroup] as [SetupGrp_SetupGroup],
[Resource1].[SetupGrp_Description] as [SetupGrp_Description],
[JobMtl1].[JobMtl_JobNum] as [JobMtl_JobNum],
[JobHead].[JobClosed] as [JobHead_JobClosed]
from Erp.JobHead as JobHead
inner join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
[PartWhse].[WarehouseCode] as [PartWhse_WarehouseCode],
[PartWhse].[DemandQty] as [PartWhse_DemandQty],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[Part].[ProdCode] as [Part_ProdCode]
from Erp.Part as Part
inner join Erp.PartWhse as PartWhse on
Part.Company = PartWhse.Company
And
Part.PartNum = PartWhse.PartNum) as Partwhse1 on
Partwhse1.PartWhse_PartNum = JobHead.PartNum
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
And
JobHead.JobNum = JobOper.JobNum
and ( JobOper.JobComplete = False and JobOper.OpComplete = False )
left outer join (select
[SetupGrp].[SetupGroup] as [SetupGrp_SetupGroup],
Ive have also been curious if I could use the min() on Joboper.oprseq in a subquery that would get the result im looking for.
Try using a calculated field min() job op and op description
so I did a calculated field with min(joboper.operseq), group by joboper.jobnum. linked it to joboper in my top level with a inner join. and no luck. ill continue to play with this, but its also very necessary to sort by job number and oper seq to be able to pull the lowest number seq of a job. cant sort when doing a group by with the calculated field. Im going to shrink up this BAQ and try to get it to work with a lot less and slowly build up from there. see if I can get anywhere.
Trevor - I’m working on something similar, and narrowed it down to a top level and subquery, both with only the JobOper table, which filters out completed jobs and operations, then does either a min calculated field to show the smallest sequence number, or TOP row 1 to show the first row of the subquery.
Been trying using Top for the Result Set Rows on the Subquery (Subquery Option Tab). It worked at one point, then I changed it attempting to make progress elsewhere, and now it’s spitting out an error when I analyze the BAQ.
I tried adding a calculated field on the subquery for min(JobOper.JobOprSeq), and have issues now, but I believe it worked as well.
It’s tough self-teaching programming through the limited help files, internet and trial and error, but progress is being made… These forums are a huge help!
Just a thought: Depending on how complex your job structure is, you may not want to Min() on the OprSeq since that does not dictate the order in which operations across an entire job is scheduled.
Instead you might want to use the operations start date & time. In that case if you have multiple assemblies you will get the oldest scheduled operation that is open.
Do a CTE query on the joboper table. Make every field in it a group by except for operseq, which you want to do as a min. This will get you to the current record. You can then use the CTE query in another query to do anything else you want.
Any way you could show me an example. I have done what you said and not getting the result I need. all operations still show up. I would like all operations after the first open operation to not even appear.
I think I now see why you were struggling when I started documenting it. You need to get down to the Job # and Op # of the current step before you do anything else.
Create the first query and mark it as CTE.
Add the joboper table and filter out completed ops.
Now just add the JobNum and create a formula on OpNum
This should get you to a single Op per job. You can now use this CTE as the first table in your second query. Make sure to click the subquery button to see the tables you can use.
1 Like
This is exactly what I was looking for. I’m going to need to look up what the CTE table actually did here, but I filtered out all closed operations and jobs, and its spot on. I thank you very very much.
You are welcome and I was happy to help.
CTE stands for Common Table Expression. If you google it, you will get a better definition than what I am about to give you.
Basically, you are creating a “custom” table using a sql query that can be used by other queries in the same overall query. The data is not stored anywhere, but is created whenever it is needed.
I did this so you could get down to one record that could be used as a key in joining to the other tables. Just as a quick example, look at the table below. You want to get to the current operation and are trying to group everything together. It will fail on the qty complete as op 30 is still open (let’s say there are 7 more to complete) and you can not get down to the 1 record that you want.
At this point, you need to think of what the minimal amount of data you need to do what you need. In this instance, you need job and the current operation. So that is why I only put those two fields in the CTE. Now you can use the 2 columns as a key to only pull back the records you want from the other tables.
I hope this explanation helps. My brain goes faster than I can type and I sometimes jumble up what I say.