Previous Operation in BAQ

I’m using a BAQ to pull details from the JobOper table and need to be able to identify the previous oprseq before a particular opcode. Does anyone know a technique for this?

1 Like

yes you can with a few limitations. Being that there may not be a previous op, you may return a null value. You have to use a inner sub query and that will limit you to returning only one column. If you need multiple columns with this method you may just have to make more than one query. This is the only method i know of at this time, I’m sure there may be other methods.

create a inner sub query

bring in the joboper table (you’ll need to assign an alias), create links under subquery criteria tab.

bring in oprseq column

sort desc

back in your top level query create a calculated field based on the datatype returned from your inner sub. Place the subquery in the expression. Should be good to go at that point…

6 Likes

That’s a nice way to only get the previous operation. I did some goofy pivot table to get next operation similar to this to work in a dashboard, but this would work much better.

I forgot, I wanted to ask why you didn’t just drag the subquery into the page like a table and display the value there? Why the calculated field?

i think if you do it the other way it will limit the entire results of the overall thing to 1. I could be wrong, its been a while but it was just a method when you wanted to get a single value back that may or may not be null and you didnt want to mess things up with potential one to many issues when bringing back an entire record set. Feel free to verify or update me on whether or not that is the case!

You’re right. I’m trying to set one up right now and that’s exactly what’s happening. If you do it the way you showed, it’s like it’s its own little subquery for each row. That’s awesome, I had no idea you could do this.

So after setting it up, your sub query can only return one column or it pukes, good to know, but it does a good job at the one piece of info.

1 Like

Yeha its an inner select subquery. Sort of like doing

SELECT OrderHed.OrderNum, (SELECT Customer.Name FROM Customer WHERE Customer.CustNum = OrderHed.CustNum)
FROM OrderHed
2 Likes

You crazy coders and your SQL…

3 Likes

lol meanwhile, the OP is nowhere to be seen or heard from again…you’ll find quite a few handy uses for it.

1 Like

I know this is an old post, but I have another question with this technique. A few time now I have run into where I wanted to use this to add something to an existing query that has aggregate functions in it. To get around it, I make the top level into a sub query and bring through all of the same fields, but since it’s been grouped and summed a level below, it works. The problem is, this query is already in place in a dashboard so doing this means I have to recreate everything. Is there a way to get this subquery calculated field to work with a top level that uses aggregate functions?

edit: never mind, I figured out I just had to make sure that the field that I was tying the mini sub to was in the list in the top level. I had the job number from the JobProd table in the field, but I was tying it to the job number from the JobHead table. I added the JobHead.JobNum field into the top level and it works fine. Now I don’t need to redo the whole dashboard!

1 Like

Sorry to bring up an old thread, but I’ve been working on something similar to pull the previous operation code but for some reason it keeps pulling a random operation instead of the next sequential operation. However when I try to pull the operation sequence, it pulls the next sequential operation number.

Has anyone had success with this?

It worked just fine for me. It sounds like maybe you are missing a join or an order by in your query somehow. It’s hard to tell without seeing what you have so far.

I’m actually reading through your post on formatting right now, new to this forum

1 Like

So you have the op code is greater than, so you will need to order your subquery ascending and then limit to the first row. Did you do that?

1 Like

No sir, would I do that in a calculated field?

select 
	[JobHead].[ProjectID] as [JobHead_ProjectID],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[JobAsmbl].[Description] as [JobAsmbl_Description],
	[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
	[JobHead].[DueDate] as [JobHead_DueDate],
	(((select distinct top (1)  
	[JobOper2].[OpCode] as [JobOper2_OpCode]
from Erp.JobOper as JobOper2
where (not JobOper2.OpCode = 'ENG'  and not JobOper2.OpCode = 'PROG')
 and JobOper2.Company = JobOper.Company  and JobOper2.JobNum = JobOper.JobNum  and JobOper2.AssemblySeq = JobOper.AssemblySeq  and JobOper2.OprSeq < JobOper.OprSeq

order by JobOper2.OpCode))) as [Calculated_PreviousOp],
	[JobOper].[OpCode] as [JobOper_OpCode],
	[JobOper].[OprSeq] as [JobOper_OprSeq],
	(((select distinct top (1)  
	[JobOper5].[OprSeq] as [JobOper5_OprSeq]
from Erp.JobOper as JobOper5
where JobOper5.Company = JobOper.Company  and JobOper5.JobNum = JobOper.JobNum  and JobOper5.AssemblySeq = JobOper.AssemblySeq  and JobOper5.OprSeq > JobOper.OprSeq

order by JobOper5.OprSeq))) as [Calculated_NextOp],
	(((select top (1)  
	[JobOper3].[OpCode] as [JobOper3_OpCode]
from Erp.JobOper as JobOper3
where (JobOper3.OpCode = 'BBLST'  or JobOper3.OpCode = 'PLATE'  or JobOper3.OpCode = 'HEAT')
 and JobOper3.Company = JobOper.Company  and JobOper3.JobNum = JobOper.JobNum  and JobOper3.AssemblySeq = JobOper.AssemblySeq  and JobOper3.OprSeq > JobOper.OprSeq

order by JobOper3.OpCode))) as [Calculated_Finish],
	(((select top (1)  
	[JobOper4].[OpCode] as [JobOper4_OpCode]
from Erp.JobOper as JobOper4
where (JobOper4.OpCode = 'P2PRTMIL'  or JobOper4.OpCode = 'SMAC'  or JobOper4.OpCode = 'P2CNCMIL')
 and JobOper4.Company = JobOper.Company  and JobOper4.JobNum = JobOper.JobNum  and JobOper4.AssemblySeq = JobOper.AssemblySeq  and JobOper4.OprSeq > JobOper.OprSeq

order by JobOper4.OpCode))) as [Calculated_Machining],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[JobMtl].[Description] as [JobMtl_Description],
	[JobMtl].[QtyPer] as [JobMtl_QtyPer],
	[JobMtl].[IUM] as [JobMtl_IUM],
	[JobAsmbl].[AnalysisCode] as [JobAsmbl_AnalysisCode],
	(((select distinct top (1)  
	[JobOper6].[OpCode] as [JobOper6_OpCode]
from Erp.JobOper as JobOper6
where JobOper6.Company = JobOper.Company  and JobOper6.JobNum = JobOper.JobNum  and JobOper6.AssemblySeq = JobOper.AssemblySeq  and JobOper6.OpCode > JobOper.OpCode))) as [Calculated_Test]
from Erp.JobHead as JobHead
inner join Erp.JobOper as JobOper on 
	JobHead.Company = JobOper.Company
	and JobHead.JobNum = JobOper.JobNum
	and ( JobOper.OpCode = @Operation  and JobOper.OpComplete = No  and JobOper.ActProdHours = 0.00  )

inner join Erp.JobAsmbl as JobAsmbl on 
	JobOper.Company = JobAsmbl.Company
	and JobOper.JobNum = JobAsmbl.JobNum
	and JobOper.AssemblySeq = JobAsmbl.AssemblySeq
inner join Erp.JobMtl as JobMtl on 
	JobAsmbl.Company = JobMtl.Company
	and JobAsmbl.JobNum = JobMtl.JobNum
	and JobAsmbl.AssemblySeq = JobMtl.AssemblySeq
	and ( JobMtl.MtlSeq = 10  )

where (JobHead.JobType = 'MFG'  and JobHead.DueDate >= @Date  and JobHead.JobComplete = NO  and JobHead.ProjectID = @Project)

Nope, for this specific case you need to go the the display fields tab, then there is tab there for order (normally you can’t do this is a subquery, but since this is a single sub select you can). Select the opSeq to order by.

Also, I just noticed that you had the OpCode in your filter, you need the OpSeq for the order of the sequences. The OpCode is what the actual operation is.

Sorry about not having any screen shots, I don’t have Epicor anymore to reference, so I have to go off of memory.

1 Like

Sorry for being a bit unclear, but I would like it to also show the next OpCode. Returning the operation number is working perfectly! But the next actual operation code I can’t seem to get.

You can show the next op code, but you will have to get the operation sequence number, then rejoin to the JobOp table to get which specific code it is, since you can only bring one field with you for a single sub select.

This is the problem filter I am talking about. Having and opcode greater than another opcode doesn’t make sense. The OpCode, don’t inherently have an order (even though they might naturally have one in your business) so the filter for later opertations needs to be for greater than the opSeq. That’s why I think you are getting random operations back.

1 Like

join to the JobOp table

Ahhh that specifically is what I am struggling with and need to learn how to do

Go to your top level, and bring in the JobOp table (again) and then do your joins so that it it being joined to the OpSeq that you brought in with the single sub select. You might have to make more levels to make this work out correctly.

1 Like