Creating an ordered list around a Select function

[1minManager]

I’m trying to pulling an ordered list into an EMUNLIST via a Vlaid_IF formula

I have a table with the following 3 columns

[Code] = 3 digit code for the course, also the key
[Name] = the name of the course as a TEXT
[Order] = a NUMBER column where each row has a different number from 1 to 18. I want it to order the list so that 1 is at the top
[Addon] = a Yes/No column

So I want a list of [Name] in [Order] order where [Addon] is false

I’ve tried this and also numerous other ways:
SORT(Select(CourseData[Name],[Addon]=FALSE),[Order],TRUE)

The issue that keeps cropping up is - Unable to find column ‘Order’, did you mean ‘Name’?

I’m presuming this is because the Select is just pulling in [Name], so you then can’t use [Order].

I’m presuming I need some combination or Select and Orderby, but I can’t seem to make it work

[Aleksi]

SORT will only work with the column itself. No additional column is available. You need to use ORDERBY. Something like…
ORDERBY(SELECT(Course Data[Code],[Addon]=FALSE),[Order],FALSE). It will show the list with the Code value, but if you choose Name field as label and you use Ref field, it could work.

[1minManager]

Hi @Aleksi, no that doesn’t work. It just pulls in the Codes, albeit in the correct order.

If I change it from a base type of Text to Ref then it give me warning triangles and still just the codes

If I change it to this
ORDERBY(SELECT(Course Data[Name],[Addon]=FALSE),[Order],FALSE)
we are back to the above error message

I’ve even tried putting them in the correct order in the spreadsheet, but it seems to ignore the order

[Steve]

ORDERBY() must be given a list of keys, and produces a list of the same keys ordered as directed.

SORT() operates strictly on the list of values given.

There is no way–to my knowledge–to sort a list of arbitrary non-key values by some related column value. Nor is there any way to convert an ordered list of keys to an ordered list of related column values.

In your case, the best I can imagine is:

• Make the Name column the label column of the CourseData table.

• Make the target column’s type EnumList of base type Ref to CourseData.

• Set the target column’s Valid_If expression to:

  ORDERBY(FILTER("CourseData", NOT([Addon])), [Order])
  

The target column will receive the key value of the selected row, not the selected row’s Name column value.

[AlfredoChavez]

You could use that expression in a vc and then get the desired value in another one.

vc1:  

ORDERBY(FILTER("CourseData", NOT([Addon])), [Order])

 vc2:

[vc1][Name]

[1minManager]

Hi @Steve

In the end I’ve gone for this. Very inelegant But it does work…

LIST(
Any(Select(CourseData[Name],AND([Order]=1,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=2,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=3,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=4,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=5,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=6,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=7,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=8,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=9,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=10,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=11,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=12,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=13,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=14,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=15,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=16,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=17,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=18,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=19,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=20,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=21,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=22,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=23,[AddOn]=FALSE))),
Any(Select(CourseData[Name],AND([Order]=24,[AddOn]=FALSE)))
)

[Bellave_Jayaram]

An alternative is to create a VC by concatenating [Order] and [Code]