Need to sort a Dataverse Choice column in a Canvas app? It seems like it should be straightforward to do, but unfortunately it’s not! Read on to see the possible options
To sort a Dataverse Choice column, your first thought is likely to be the same as mine, to use the Sort function. Unfortunately this returns an error and won’t work so we need alternatives
To understand the possibilities, it helps to understand that a Dataverse Choice essentially consists of a 2-column table. One column contains the contents, the Choices entered into Dataverse. The second column is not one you see in Dataverse and is the Choice ID. This is a sequential number that reflects the order in which the Choices were entered in Dataverse. The Choice ID column defines the order in which the Choices are displayed both when viewed in Dataverse and in your Canvas app
So what are the options to sort a Choice column?
Option 1 - Enter Choices in the Required Order
It may be obvious, but by far the simplest way is to enter your Choices in the order you want them to be displayed
That’s fine if you are entering the data yourself and the Choices remain static. But what if you need to subsequently add additional data to an existing Choice column?
Option 2 - Cut, Paste & Add in Dataverse
If the number of Choices is small, the most straightforward way to make updates is to add the new options in Dataverse. To change the order of the Choices, create additional blank entries, re-arrange your data by cutting and pasting the existing entries using Ctrl-x / Ctrl-v; and add the new data in the order you want it displayed. Every item will need to be moved individually and placed in the required order
So far so good, but if you have a large number of Choices or you have multiple Choice fields that require updates, then you won’t appreciate option 2 very much!
Option 3 - Use the XRM ToolBox
The third option is to use the XRM Toolbox. The Option Set Editor tool is easy to use once you have the ToolBox installed and connected to Dataverse
Using the Option Set Editor, download your Choices as a template CSV file, edit in Excel and upload back to Dataverse. Quick and simple
This technique is also great for creating large Choice sets quickly rather than typing them all into Dataverse individually
Option 4 - Sort in a Canvas App
All the above options re-define the order in which the Choices data is held in Dataverse
If you don’t have direct access to Dataverse to edit the Choice content, or if you need flexibility in how the Choices are presented in a Canvas app, then none of the above will work for you. Under these circumstances, the only possibility is to sort the Choices within your Canvas app
Using the JSON function, you can get access to the Choice ID. Once you have the Choice ID in a variable, with some clever data manipulation, the Choices text can be aligned with its respective Choice ID in a 2 column collection
This collection replicates the Choice column in Dataverse and can be used as the Items property in a Dropdown box, instead of the Dataverse Choice column. Using the Sort function, the content can now be sorted as required, either by the Choices text or the Choice ID.
The Patch function is then used to add or update the Dataverse Choice column with the selected content of the Dropdown box
This technique is described in full detail by Django Lohn in his excellent blog post here
Summary
So those are the options to sort a Dataverse Choice column. Use the one that best meets your needs!
Resources
- Knowhere365: Get Dataverse Option Set ID as well as Label