Dataverse - How to filter Dataverse choice columns

June 07, 2021

4 min read

Dataverse

With Dataverse, we can create tables with choice columns. A choice column defines a list of acceptable values, and data entry forms typically display these through drop down or combo box controls.

Whilst this offers a simple way to restrict values during data entry, the syntax to filter and to retrieve records that match a choice value isn't entirely straightforward. This post describes this topic in more detail, including how to filter a table by the choice value text.

How to filter by a Dataverse choice column

To give a simple example, let's take the example of a "property document" table. Each row in the table stores a file attachment and associated meta data. This table includes a choice column called DocumentType, as shown in the screenshot beneath.

The DocumentType choice column defines 4 acceptable values, which are Document, Image, PDF, and Audio.

From Power Apps, let's imagine that we want to show a list of records that match the document type PDF. To add this functionality, we add a gallery control to a screen and set the Items property to the following formula:

Filter(PropertyDocuments, DocumentType=[@DocumentType].PDF)

The highlight of this formula is that Power Apps exposes the available choice values through an enumeration with a keyword that consists of the column name prefixed with the @ symbol, and surrounded by square brackets.

As the screenshot beneath shows, we can type [@DocumentType] into the formula bar, and the code completion feature of the designer shows the available choice items.

Filtering a table through the drop-down value of choice items

A common requirement is to build a search screen where users can filter by a specific value. We can add this feature by adding a combo box or drop down control, and setting the items property to the following formula:

Choices(PropertyDocuments.DocumentType)

The Choices function returns the available choice values for a specified column. Assuming that we name our combo box control cboDocumentType, we can set the items property of the gallery control the following formula.

Filter(PropertyDocuments, 
       DocumentType=cboDocumentType.Selected.Value
)

At run time, the user can then filter the gallery by the selected choice item in the combobox control.

Filtering a Dataverse choice column by the text value

There's sometimes the need to filter a choice column by the text of the choice item. A typical use case scenario is where we want to filter a choice column by a variable, or some other user input. Unfortunately, this task isn't entirely easy to carry out.

For example, let's say that we want to return all records with a document type that matches the text value "Audio". As we might expect, it's not possible to filter a choice column by a text value due to a mismatch in data types.

It's also not possible to match a choice value based on choice item that we return through a call to LookUp. This is unfortuanate because it uses syntax that is logically correct and syntax that I would expect to work.

Therefore, this limitation can force app builders to workaround this problem with formula that looks like this:

Switch(txtDocmentTypeSearch.Text, 
       "Audio", 
       Filter(PropertyDocuments, DocumentType=[@DocumentType].Audio),
       "Document", 
       Filter(PropertyDocuments, DocumentType=[@DocumentType].Document),
       "Image", 
       Filter(PropertyDocuments, DocumentType=[@DocumentType].Image),
       PropertyDocuments
)

This not ideal because it imposes the requirement to hardcode every possible choice item. If we subsequently add an additional choice item to the column, we would need to respectively modify the formula.

Therefore, another workaround is to use a hidden combobox. This works on the basis that although it isn't possible to filter a choice column by a call to LookUp, it's possible to filter by the selected item of a combobox.

Let's suppose want to return records that match the document type that a user enters into a text input control called txtDocumentTypeSearch. We would add a combobox (let's call this cboDocumentTypeSearch), set the visible property to false, and set the items property to the following formula:

Filter(Choices(PropertyDocuments.DocumentType), 
       Text(Value)=txtDocumentTypeSearch.Text
)

To specify a case insensitive search, we would apply the Lower function to both sides of the equality operator.

Next, we would set the items property of the gallery control to the formula beneath:

Filter(PropertyDocuments, 
       DocumentType=cboDocumentTypeSearch.Selected.Value
)

At runtime, the user can now type a document type value into the text input control, and the gallery control will show matching records.

Conclusion

With Dataverse, we can create tables with choice columns. The syntax to filter a table based on a choice column may not be entirely intuitive. This post described how to filter a table by a choice column, including how to filter a table by the choice item text.