Order by Function in dropdown

[Dan_Velus]

Hi There, 

I have a dropdown (EnumList) field in Table1 that uses a SELECT function to get the values from Table2. The data looks something like this:

Table1

First Name	Last Name	Full Name
John	Smith	John Smith
Angel	Boss	Angel Boss

I am trying to bring the above data into the EnumList on the Table2 form and I would like to sort the dropdown by [Last Name]. The SELECT query, I am trying looks like this:
ORDER BY(SELECT(Table1[Full Name], TRUE), [Last Name])

The error I am getting is, that the column [Last Name] doesn't exist. 

Could someone help me with this, please? Thank you so much.

[Aurelien]

Hi @Dan_Velus 

Please try in the Valid-If field of your column:

ORDERBY(
  Table1[keyColumnOfTable1], 
  [Last Name], 
  TRUE
)

For reference:

ORDERBY() 

[Dan_Velus]

@Aurelien, thank you for your response.

I already have the SELECT statement in the Valid if section, though. 
Would I be able to combine the Order By with the SELECT?

[Aurelien]

Hi @Dan_Velus 

If you read the url documentation I provided, you may have noticed that ORDERBY() expects a list of reference values in the first argument. 

In the SELECT expression made with [Full Name], this column does not seem to be the key_column of your table.

I would recommend using a FILTER expression instead of SELECT. This way, there is no possible error.

For reference:

FILTER() 

[Dan_Velus]

Hi @Aurelien 

Thanks again. I tried the following Filter() expression

ORDERBY(FILTER("Table1", ([Group]=[_THISROW].[Group])),[Last Name],TRUE)

The key for the table is UniqueID. 

When I put the above statement in the valid_if section, I get the list of UniqueIDs in the dropdown. So how do I re-write it to show the [Full Name] column sorted by [Last Name] column when [UniqueID] is the key of the table?

Could you please help?

[Aurelien]

You need to select the correct column with the Label Property in the people table.

For reference:

Add row labels 

[Dan_Velus]

Thanks @Aurelien 

I believe I do have the correct label, except that it is a virtual column (calculated column) does that matter?

[Aurelien]

No, it should be OK.

[Dan_Velus]

But do you know why this expression still doesn't work? Sorry to bother you.

[Aurelien]

Did you set the column type as Ref, source table: "your table of users" ?

If not, please do.

If yes, can you please share screnshots, from your app editor, of:

- the table where you use this expression, and the column settings

[Dan_Velus]

Thank you. 
The column type wasn't 'Ref' - great pick, thanks to you. So I changed that column to Ref.

But now, the other columns that use the Ref column type are throwing errors. For example, the column name EnumList is the 'Full Name' dropdown column.

Number Attendees = Counts the number in the EnumList
Who Absent = uses the EnumList to find out who in the entire list did not attend.

[Aurelien]

Hi @Dan_Velus 

The idea is the same for these expressions you highlight. If you have list or enumlist columns that are based on [FullName], you may need to get rid of these and instead use the Ref.

About the screenshot you provide, on your [EnumList] column, I suggest changing type with EnumList type, and Base type Ref.

Something like that, for example: