Dataverse - How to sort the available choice items from a choice column

May 12, 2022

4 min read

Dataverse

Compared to lookup columns, choice columns in Dataverse can be difficult to work with, particularly when we want to sort or filter choice values.

To highlight a typical issue, the Sort formula that many users intuitively use will give the error "The function 'Sort' has some invalid arguments. Cannot sort on the expression type".

What's the correct way to sort choice items in a canvas app? This post describes the available methods.

Setting up a choice column in Dataverse

To demonstrate the techniques in this post, we'll create a table called 'Office'. This table contains a choice column called 'Region' with the choices shown below.

If we now create an auto-generated app based on this table, the regions drop-down displays the regions in the order that they've been added as shown below. A common challenge for app builders is how to sort the choice items in alphabetical sequence.

Defining the sort order of choice items in Dataverse

The preferred way to define the sort sequence is to apply the change globally at the Dataverse level. When we apply a sort sequence globally, it applies to all apps, which saves us from having to duplicate the work in multiple apps. By using this technique, it's also easy to define custom sort sequences that are not in strict alphabetical sequence.

The slight difficulty with this method is that this feature is currently available only in the classic designer - it isn't yet available in the modern designer.

One way to open the classic designer is to open the default solution in the modern app designer. In the toolbar, we then click the menu item to 'Switch to classic'.

From the classic designer, we can locate the choice items by navigating to the table. Note that the classic designer uses the CDS naming terminology, therefore, the tables will appear beneath the 'entities' folder.

From the table, we then click the edit option against the choice column (Region in this example).

This opens the properties of the column, and there will be a link towards the bottom that opens the 'option set' window (option set is the CDS name for choices).

From this window, we can view the choice items (labelled 'Options'). As the screenshot beneath highlights, we can click the green up and down arrows to reorder the choice items.

Finally, we save and publish the changes here to apply the new sort sequence.

How to sort Dataverse choice items in alphabetical sequence from a canvas app

Instead of sorting the choice values from Dataverse, let's say we want to sort the values from within the canvas app. A use case scenario could be to apply a sort sequence that differs from the global sort sequence.

To overcome the common problem mentioned at the beginning of this post, the trick is to convert the choice items values to text by calling the Text function.

Sort(Choices(Region), 
     Text(Value)
)

If we set the Items property of a dropdown/combo box to this formula, it won't trigger any errors in the designer and will work as expected, as highlighted below.

The natural extension of this example is to sort the values in alphabetical descending sequence. We can achieve this by adapting the formula like so:

Sort(Choices(Region), 
     Text(Value), 
     Descending
)

How to apply a custom sort sequence to Dataverse choice items

Another common requirement is to apply a custom sort sequence. Taking our example, the use case scenario here could be to configure a dropdown so that "North America" and "Europe" appear at the top of the list (as these are most frequently used), and the remaining values should then appear in alphabetical sequence.

We can accomplish this with the following formula:

Sort(AddColumns(
             Choices(Region),
             "SortColumn",
             Switch(Text(Value),
                    "North America",
                    "1" & Text(Value),
                    "Europe",
                    "2" & Text(Value), 
                    "3" & Text(Value)
            )
     ), 
     SortColumn
)

Technically, this formula adds a column called SortColumn. The formula sets the value of SortColumn to the choice value text and prefixes the "North America" value with 1, "Europe" with 2, and everything else with 3. Visually, the result looks like this.

When we now sort this data by SortColumn, the choice items will appear in the required sequence as highlighted beneath.

A caveat with this technique is that if any of the choice values begin with a number, we would need to adapt the formula that sets the SortColumn value to take account of this.

Conclusion

With Dataverse choice columns, there is often the requirement to sort dropdowns or combo boxes in alphabetical or custom sequences. This post describes how we can carry out this task by defining the sort order in Dataverse, or by using formula in a canvas app.