I'm trying to order a SELECT list by another ...

expressions
(Simon Bailey) #1

I’m trying to order a SELECT list by another column (date) but I’m obviously doing it wrong.

This expression gives me the first thing in the list: =TOP(SELECT(MOULDING BOX MOVEMENTS[MOVED TO],[Product Code on Overstock]=[_THISROW].[Product Code on Overstock]),1)

But I want the first thing based in the date: Have tried this =TOP(ORDERBY(SELECT(MOULDING BOX MOVEMENTS[MOVED TO],[Product Code on Overstock]=[_THISROW].[Product Code on Overstock]),[Date Received]),1)

But it says ORDERBY has invalid inputs

Am I totally wrong, or close?

(Levent KULAÇOĞLU) #2

@Simon_Bailey is [MOVED TO] column your key?

(Steven Coile) #3

ORDERBY() expects a list of KEY column values as the first argument. I recommend using FILTER()–which returns a list of KEY column values–rather than SELECT() when using ORDERBY(). For instance:

=TOP(ORDERBY(FILTER(“MOULDING BOX MOVEMENTS”, [Product Code on Overstock] = [_THISROW].[Product Code on Overstock]), [Date Received]), 1)

(Steven Coile) #4

TOP() returns a list, even if just a list of one item. If you just want the one item rather than a list of one item, use INDEX() instead of TOP().

(Simon Bailey) #5

MOVED TO isn’t the key column and neither is the Date column I want to order by.

INDEX might work, but i want to return the Moved To column based on the 1st Date Column

(Steven Coile) #6

So you want the MOVED TO column value of the row with the matching Product Code on Overstock with the oldest Date Received value?

(Simon Bailey) #7

Yep, that’s exactly what I want

(Steven Coile) #8

This should get you the desired row:

MINROW(“MOULDING BOX MOVEMENTS”, “Date Received”, ([Product Code on Overstock] = [_THISROW].[Product Code on Overstock]))

If you use that as the app formula for a virtual column, you could then get the MOVED TO column value via the virtual column:

[My VirtCol].[MOVED TO]

Would that be adequate?

(Simon Bailey) #9

+Steve Coile Thanks Steve. That’s done it