I'm trying to order a SELECT list by another

[Simon_Bailey]

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_KULACOGL]

@Simon_Bailey is [MOVED TO] column your key?

[Steven_Coile]

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]

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]

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]

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]

Yep, that’s exactly what I want

[Steven_Coile · Accepted solution]

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]

+Steve Coile Thanks Steve. That’s done it