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!!!
2 Likes
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
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.
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
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
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.
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.
@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.
Not to re-reopen an old post, but this seems to no longer work in Kinetic. Iâm in the browser BAQ designer and when I try to apply the SubQuery Criteria to the subquery it only gives me tables in that subquery as an option to choose a field value from, so I cannot choose the JobOper table from the Top Level.
Also, running a converted dashboard that is built from a Classic version of this BAQ in Kinetic does not return results. So I checked the server event logs and itâs giving me âinvalid columnâ errors for all of the columns that were made via a select subquery like shown above.
So is this method no longer valid for Kinetic grids?
1 Like
Yeah, itâs missing that. I reported it in the CR last time around, but looks like they didnât get a fix for it. Put in a ticket, as that should be a parity bug that they should fix.
4 Likes
Actually, I went back and found my case. I didnât realize that they rejected the bug and closed it. I didnât see the notes and so I never responded, plus I havenât had the energy to explain to epicor why this is useful.
3 Likes
It was put in classic for a reason, it should exist in the new designer.
What I see in that response â
6 Likes
Sure looks to me that their option 1 isnât even possible in the example of this thread, because you canât select the TopLevel from a subquery.
1 Like
Yeah, if I had seen this back when I submitted it, I probably would have fought harder. But it it was CR submission, and they ask for tickets, but I donât have the energy to fight with them when they are asking for us to do their QA job. Feel free (please do actually) to take up the torch @d_inman .
3 Likes
Well look, a repeatâŚ
5 Likes
3 Likes