Previous Operation in BAQ

Epicor ERP 10
22

Holy cow I had no idea that I could bring in a table again, that worked! I was able to join the table as I did below, and simply display the opcode for the opseq in my subquery.

Thank you so much Brandon!!!

image
image705×392 18.9 KB

1 Like
23

When I tried this, the InnerSubQuery doesn’t allow me to bring in any other fields associated to that previous job and operation in the subquery. I get an ‘not linked by EXIST’ error if I try to bring in any other fields besides the OprSeq in the display columns.

1 Like
24

How would I bring in additional fields since it can only bring in one field? I want to bring in the OprComplete field in that previous operation but it won’t since like you said, it only allows you to bring one field and I need a second field for the OprComplete for that previous operation.

25

For that I would check out windowing functions in a subquery. You can make a calculated field like below (syntax not double checked…)

Row_Number () over (partition by table.field1, table.field2, table.field3 order by table2.field3 asc)

Use that to rank your results then filter the subquery by the row number 1 to get your “Top 1” setting. Then you can bring over other fields that come with it.

1 Like
26

Depending on how your BAQ is constructed:

You could use a calculated field.

LAG (JobOper.OprSeq ,1 ,0) OVER (partition by JobOper.JobNum,JobAsmbl.AssemblySeq order by JobOper.OprSeq)

2 Likes
27

image
image838×718 36.6 KB

Hi All, apologies for reopening this old thread, but using all the above to create my BAQ with subqueries, etc… I’ve got this result, but I want to only show the first oprseq line. So really i want to see only a total of 4 lines on the BAQ as there is only four jobs. Any help would be much appreciated.
Kind regards
Ross.

28

Is the top level grouped by job, part, date and start day? That would give you one line per job and then the other fields would be calculated.

29

@gpayne Thank you for your input, I found that I had an extra grouped by column on my top level. Can’t remember column I had to remove, but you message got me to look that the top level and it now works. Thank you.