9

I am trying to simply apply a Boolean filter to a Rest API call that is based on a Yes/No

It has been documented in many locations that this functionality is broken for many years. I am unable to find any firm indication that it is indeed broken from Microsoft.

Has anyone been able to successfully achieve this in SharePoint online. I understand it is simple enough to filter the local collection, but this just feels sub-optimal in so many ways.

Examples:-

  • items?$filter=YesNoColumn eq 1 - returns nothing
  • items?$filter=YesNoColumn ne 0 - return everything
  • items?$filter=YesNoColumn gt 0 - return nothing

5 Answers 5

12

Assuming the column is a Yes/No column, and not a column with "Yes" and "No" text, the following works for me in SharePoint Online:

Returns false items:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq 0

Returns true items:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq 1

Neither return items with a blank value.

2
  • It turns out I was dealing with a quirk regarding a PnPJs implementation. May 31, 2018 at 6:02
  • This worked for me as well, using Graph SDK v4.14 to access SharePoint Online list data. Mar 16, 2022 at 6:47
2

Using the following pattern works in our implementation:

&$filter=(Category eq 'Work Description')and (Archived eq false)
0
_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq 1

This is the correct answer.

0

Mike Smith - MCT gave the correct answer, I'll just add this:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq null 

you will get the blank data.

So, for all negative values as well as blank (or null) values you can use:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq null or YesNoColumn eq 0
0

We have to use expand property in REST API:

/_api/web/lists/getbytitle('TestList')/items?$select=IsActive&$expand=isActive&$filter=IsActive eq 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.