Sorting an Enumlist type / Ref base type

[Connor_Mooney]

I’m having trouble with alphabetically sorting a multi-select list that is Enumlist type and Ref base type. I’ve added “=SORT(Test Directory[Staff Name])” to the Valid If formula field, and while it does successfully sort the list, I’m seeing validation warning symbols throughout my list, like this:

I’ve read through several posts in this community and cannot find a solution that works for me. Essentially, the list of 100+ names the user selects from is pulled from a table that includes names, emails, departments, etc. The user needs to be able to select multiple names from the list, and the emails for those selected names are recorded as a comma-separated list of emails. All of this is working as expected so far and the list of names appears in the original order from the source with no warning symbols. Then when I add the SORT() into the Valid If formula, I get those warning symbols. Is there some other way I should be setting up the multiple-select list, or a different area where I can apply SORT() or ORDERBY()?

[Connor_Mooney]

Problem solved… After deleting and re-adding the table of names/emails being used as the Ref, the warning signs disappeared and the multi-select list seems to be working/sorted as expected.

[WillowMobileSys]

Be sure to confirm that you have the proper column set as the key after re-adding. Usually the yellow warning symbols are either because the wrong column is set as the key OR the values in your list are not the key value - which is required for REF columns to work properly.

On Sorting Ref columns – typically you would have a column as the key and then set a column as a label - in your example Staff table you might have Staff ID as the key and then Name set as the label. Because the key value is what is stored you can’t simply sort the list. Instead, yo woul dneed to use the ORDERBY() function to order the key values based on the Label column - in your case order by Name.

Probably confusing but I hope it helps understand it better.

[Connor_Mooney]

You’re right, my re-added table had reset the key/label selections. After correcting those and refreshing, the validation warning symbols are back. I’ve removed the SORT() from the Valid If formula on that Enumlist field… Can you explain where the ORDERBY() would be added?

Currently, I have a “Directory” table with a key of [Staff Email] and a label of [Staff Name]. Directory is the Ref table for the Enumlist.

[WillowMobileSys · Accepted solution]

In the Valid_If of your EnumList column.

I would first get the SELECT() function returning the proper values without the warning symbol. Note: SELECT() must always select the list of Key columns.

Then you you would simply wrap that SELECT() function with the ORDERBY. So the final expression should be similar to this:

ORDERBY(SELECT(Table[Key Column], <select criteria>), [Column to Order by])

[Connor_Mooney]

Made the update as you suggested and it’s working beautifully. Thanks!

[daniel5905]

Hello, 

This is another way in Valid If, of the table where you´re capturing the information

ORDERBY(Table[id], [Column])