Do you need to get items from a SharePoint list based on a Yes/No column?

Working with SharePoint’s Yes/No columns in Power Automate can be a bit tricky, especially when it comes to using the Get Items action with a filter query. This article will provide you with the necessary knowledge to efficiently use the Get Items filter query with SharePoint’s Yes/No column.

When using the SharePoint connector you might face challenges when trying to filter list items based on a Yes/No column. This is because Power Automate recognizes these columns as Boolean values, where Yes is represented by the true, and No is represented by false.

However, the OData filter uses neither Yes/No nor true/false values for these columns.

This post will show you how to work with the Power Automate Get items action OData query filter for a Yes/No column in your SharePoint list.

Understanding SharePoint Yes/No Column Values

A SharePoint Yes/No column is a type of column that displays simple binary choices for the user. This column type allows you to easily add Yes or No values that appear as either a checked checkbox or an unchecked checkbox.

Behind the scenes these checks are stored as true (checked) or false (unchecked) values.

There is a third possible value for this type of column. This column can also take on the null value. This is an absence of value and unfortunately, it visually appears the same as the No/unchecked/false value.

• Yes: Appears as a checked box in SharePoint. This appears as true in the JSON returned by the Get items action in Power Automate.
• No: Appears as an unchecked box in SharePoint. This appears as false in the JSON returned by the Get items action in Power Automate.
• Null: Appears as an unchecked box in SharePoint. This field is missing in the JSON returned by the Get items action in Power Automate.

When an item contains a null value, the field will be missing for that item in the JSON returned to Power Automate. This means it has a null value.

⚠️ Warning: The null value in a Yes/No column can be very confusing and is often the reason why your Get items filter query appears not to be working.

You will see how to filter for each of these situations.

How to Use a Filter Query with Yes No SharePoint Column

The Get Items action contains a Filter Query option that allows you to return only those items that meet your given filter criteria.

Begin by adding the Get Items action, the Filter Query option can then be found by clicking on the Show advanced options link at the bottom of the action.

Once added, you’ll need to configure the action by selecting your SharePoint site Site Address and List Name from the available options.

ColumnName eq 'Yes'
ColumnName eq 'No'
ColumnName eq 'true'
ColumnName eq 'false'

You might have tried one of the above Odata filters and been frustrated to find it doesn’t return the items you expected from the list.

The solution to get your filter query working correctly is not obvious. Instead of Yes, No, true, or false values you will need to use 1 for Yes/true, and 0 for No/false.

ColumnName eq 1

The above filter query will return all the Yes/checked/true items from your list.

ColumnName eq 0

The above filter query will return all the No/unchecked/false items from your list.

ColumnName eq null

The above filter query will return all the items from your list with no value.

ColumnName eq 0 or ColumnName eq null

So if you want to return all the items in your list that appear as unchecked you will need to use the above or condition to get both the No values and null values.

Conclusions

When dealing with a SharePoint Yes/No column filter query, it is essential to consider that these columns are represented as boolean values, but when dealing with the Filter Query, Yes corresponds to 1, while No corresponds to 0.

To create a working filter for a Yes/No column, use the following format: ColumnName eq/ne 1 or ColumnName eq/ne 0.

You might also need to account for items that look like No’s but are actually null. In this case, you can use the format ColumnName eq/ne null.

Did you have problems with Yes/No filter queries in your flow? Let me know in the comments!