How to work with Choice columns in Dataverse

What is a choice column?

A choice column provides a list of values for users to pick from. They help streamline data entry in turn ensuring clean data. There are two types - local and global. Local choices can are local to the table and column that they are created for. They cannot be reused on other tables. For example, a list of statuses for an application may not apply anywhere else (e.g. Submitted, In Review, Approved, Rejected). On the other hand, global choices are available across all tables and columns allowing them to be reused. When creating a new choice, you can choose to make it local under the View more option. For example, days of the week can be global since there might be a need to use that across tables. We will use the Students table for this example. To review the data model, click here.

Choice vs Lookup

Choices make more sense when the list is short and the values don't change frequently. They are also good because they are part of the solution. Lookups make sense if the data frequently changes or if users need to add new values or if you need to capture more metadata than just the list item. For example, for a list of cities, using a lookup column will be better as that will allow storage of other attributes like area, population etc. as part of the Cities table.

Displaying choices

We will use the Major field on the Students table as an example. To display the list of majors in a dropdown or combo box (cmbMajors), set the Items property to:

Choices(Majors)

To learn more about the Choices function, click here.

Filtering using a list

Add a gallery galStudents. Set its Items property to the Students table. We will use the same cmbMajors to filter the list of students based on their major.

To filter the list of students, set the Items property of galStudents to:

Filter(
    Students,
    IsBlank(cmbMajors.Selected.Value) || Major = cmbMajors.Selected.Value
)

Let's breakdown the filter condition:

1. The first part checks if the combo box is blank, if it is, then the filter condition becomes true and all records are returned.
2. The second part (to the right of the ||) comes into play when the combo box isn't blank. Only those students are returned that match the selected major.

Filtering by a specific value

For a global choice (like Major in our example), to filter by a specific major, set the Items property of a gallery to:

Filter(
    Students,
    Major = Majors.'Corporate Finance'
)

where Majors is the name of the global choice.

For a local choice (like Status which is an out of the box column), to filter by a specific status, set the Items property of a gallery to:

Filter(
    Students,
    Status = 'Status (Students)'.Active
)

As you can see, a local choice is referenced slightly differently than a global choice. Since a local choice is local to a table/column, it is referenced by the column name followed by the table name in brackets.

Patching a Choice column using a list

To patch a student record, set the OnSelect of a button to:

Patch(
    Students,
    Defaults(Students),
    {Major: cmbMajors.Selected.Value}
)

Patching a Choice column using a specific value

To patch a student record using a specific Major value, set the OnSelect of a button to:

Patch(
    Students,
    Defaults(Students),
    {Major: Majors.'Corporate Finance'}
)

Recent articles

1. Polymorphic lookups
2. Indirect relationships
3. One to one relationships