Max value of a sub-query

building reports desktopintelligence
1

I have a toughie, I think.

I have one query, where I grab a manufacturing order, its steps (10, 20, 30) and it’s work area (PA2, PA3, PA4). However, in my query, I specify which work area through a condition.

Results are a single row within a manufacturing order

MO | MO step | Work Area

2000 | 40 | PA4
2001 | 30 | PA4
2002 | 30 | PA4

This works fine.

Now, I want to show Work area where the last completed MO Step was in a new column beside the work area. I make another query that grabs MO, MO Step, Work Area, Step Completed (yes/no).

In order to do this, I have to first grab all the completed steps within the MO (through a sub-query: Object: MO Step Condition: MO Step Complete = Yes), then within all the completed MO steps dataset, I have to grab the MAX MO step. Then I will link the 2 queries based on MO and put in the last work area complete.

The problem is that I am finding is that I can’t put a calculation on my sub-query.

Is there a way to do this?

dusted (BOB member since 2003-09-18)

2

Are you trying to put the calculation as a condition of the subquery ?
What problem are you encountering trying to use the calc ?

Michele Pinti (BOB member since 2002-06-17)

3

I am actually trying to use the results of the sub-query in a condition that contains a calculation.

Basically, it would be this (if BO would allow it).

MO Step equal to (Max value returned by sub-query per Manufacturing order)

dusted (BOB member since 2003-09-18)

4

I think that you should be able to do this in the subquery have you tried using the Calculation Operand ?
There are several threads about this for example https://bobj-board.org/t/248

Michele Pinti (BOB member since 2002-06-17)