Optimizing Dataverse: Guide to Sorting Choice Column Items in Power Apps

Introduction:

When you work with Dataverse, sorting choice columns often becomes a complex task, especially when you compare it to sorting lookup columns. This guide aims to demystify this process, providing a clear approach to efficiently organize choice items in Power Apps.

Power Apps users sometimes face errors when they attempt to use the ‘Sort’ function, receiving messages like “The function ‘Sort’ has some invalid arguments. Cannot sort on the expression type”. This guide will show you the correct methods to sort choice items within a canvas app, ensuring a seamless experience for users.

Creating a Choice Column in Dataverse:

Imagine a table called ‘Office’ that includes a choice column named ‘Region’. When you generate an app based on this table, the regions dropdown appears in the order you entered them. Many app developers ask how they can arrange these choice items alphabetically.

Establishing the Sort Order for Choice Items in Dataverse:

A good strategy is to set the sort order at the global level in Dataverse. This change ensures that every app you create will reflect the new order, saving you from repeated adjustments in multiple apps. You can also easily set up custom sequences that go beyond simple alphabetical sorting. Accessing this feature, though, is currently limited to the classic designer and is not available in the modern designer.

To reach the classic designer, go to the default solution in the modern app designer and choose the ‘Switch to classic’ option from the toolbar. In the classic environment, you’ll find the choice items by navigating to the table. Keep in mind that the classic designer uses the CDS naming convention, listing tables under the ‘entities’ section. From there, you can edit the ‘Region’ column.

Clicking on the column reveals its properties, and at the bottom, a link takes you to the ‘option set’ window—another term for choices in CDS. Here, you can see the choice items listed as ‘Options’. To reorder them, use the green arrows, then save and publish to apply the new order.

Sorting Dataverse Choice Items Alphabetically in a Canvas App:

If you need to sort values within the canvas app rather than in Dataverse, you might want to use a different sequence. To get around the sorting issue mentioned earlier, transform choice item values into text with the Text function:

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

To further build on this, sorting values in a descending alphabetical order can be realized by modifying the formula as follows:

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

Implementing a Custom Sort Order to Dataverse Choice Items:

Sometimes you need a custom sort order. Let’s say you want “North America” and “Europe” at the top of your dropdown because they are used most frequently. You can accomplish this with the formula:

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

Reference: Sort and SortByColumns functions

This approach adds a ‘SortColumn’ and sets its value based on the choice text, giving “North America” and “Europe” priority. Sorting by ‘SortColumn’ then arranges the items as desired.

Conclusion:

Sorting choice columns in Dataverse is a common task, affecting dropdowns or combo boxes. This guide outlines how to sort these columns, whether directly in Dataverse or through formulas in a canvas app.For any queries or further assistance regarding this guide or other technical challenges, don’t hesitate to contact us. We are here to assist and provide expert solutions for your needs.