SharePoint Explained: Yes/No fields aren’t your friend – Why Choice fields are a better option
Not too long ago, I was working with a client who reminded me of one of my least favorite nuances about SharePoint – Yes/No site columns. It sounds pretty simple right? Perhaps you need to make a list to store information about projects. You would create a new list and add the site columns to store the information for the project.
Imagine that one of the requirements is to create a field to answer the question “Is this an open project?” You review your options and decide that since this is a “yes” or “no” type question that clearly the Yes/No field type would be the best option for the job. Look at the name of the field type – it is so obvious this is the best option, right? Technically it would “work” just fine but there’s an issue.
Before we get ahead of ourselves, you may have used this field type before and think to yourself, “It works just fine. I added the field and it shows up in my view as ‘No’.” You are correct, you probably selected No in your list item and when it saved it will show up as “No” in your view. But what actually happened is that behind the scenes that No has been stored as a “0″ — if you had said Yes it would have been stored as a “1″. SharePoint knows how to interpret this and will display these 1s and 0s as “yes” and “no” for your convenience.
This can cause confusion if you try to run searches or use things like the Content Query Web Part (CQWP). For example, a user might create a CQWP that only shows projects that are listed as open. The user would add the CQWP, set the filter criteria to look at the Yes/No field and set the filter value to “Yes”. However, no projects would show up in the CQWP because in order to get the expected results you would need to specify the filter value as “1″ not “Yes”.
There is an easy answer to this common issue. To avoid any potential confusion I strongly recommend against using Yes/No fields and instead suggest people use a Choice field and specify the values as “Yes” and “No” which behaves exactly like you’d expect. The values that are stored in the database are actually “Yes” and “No”, no need to mess with 1s and 0s.
Let’s see this in action. I’ve created a list called Projects and added two site columns: One is a Yes/No field and the other is a choice field with values of Yes and No. I’ve entered in a few items and you can see the values that were selected:

To illustrate the behavior of the how this would work with the above list items I’ve created four CQWPs, each pointed at the list. The titles of the web parts should give a hint to the filter criteria that was used for each:

If you compare the items in the web parts with the corresponding list above you can see that everything fell into place where it should be.
Let’s go back to the original project list that was described at the beginning. In this case, to address your requirement of creating a site column to determine whether a project was open or not, you decide to create a choice field and give it values of “Yes” and “No”. Now when any of your users try to perform the obvious task of searching or filtering on that field, they will get expected results without needing to deal with this little nuance.
This week’s installment was brief due to some nasty germs floating around the Ross household, but plan to tackle some more in depth business solutions in upcoming articles. I am always interested in feedback, so if you have an idea for something you’d like to see covered, feel free to leave a comment. Until next time!
John
Author: John Ross:
Personal Blog: SharePoint911.com
Twitter: JohnRossJr
John is a Senior Consultant with SharePoint911 and co-author of the upcoming book “MOSS Explained: MOSS Explained: An Information Worker’s Deep Dive into Microsoft Office SharePoint Server 2007”
He is also a co-author of The Ted Pattison Group’s SPG301: SharePoint Planning and Governance. Check out his blog at http://www.sharepoint911.com/blogs/john or follow him on Twitter as .
Hi
I’m having some trouble with an IF-fuction combined with a YES/NO field.
All i want to do is to create a new column in which “EC” is displayed when in the YES/NO-column (called “EC topic”) YES is indicated.
I’ve tried this formula:
=IF([EC topic]=1,”EC”,”")
All fields in the calculated column remain blank like this…
Can someone help please?
Could I say a big thank-you for posting this article. I have just spent an entire afternoon cursing my CQWP as it wasn’t behaving as expected (as I was naively filtering on yes and no). Gah!
Wow, what a saver! I gave up with Yes/No fields and went with the suggested choice. IF() code now behaves as expected. Thanks John.
Thank you very much, John, I’ve been banging my head on my keyboard trying to figure out why my simple CQWP wouldn’t display the correct results.
@ Ralf:
Using the Yes/No type in an IF statement must be done using TRUE/FALSE where TRUE = Yes and FALSE = No.
=IF([EC topic]=TRUE,”EC”,””)
Excellent article. Thanks! John for sharing.
Is there a way to add comments if Yes or No.?
We have requirement for a list which has bunch of fields of Yes/No type. When user selects either Yes or No, we should provide a text field to capture data.
Thx. in advance
I am trying to do a calculated field where I want “Yes” to show up if a Date column is equal to or less than 365 days and “No” to show if the Date is more than 365 days old. I have several dates fields I need to apply this to and it stems from wanting the system (MOSS 07) to tell me when someones training has expired.
Thank you for saving my sanity!! I had googled and seen to use the TRUE/FALSE thing, but I was still putting “TRUE” with quotes and until I removed the quotes, it was failing miserably. Thanks!
Thank you! I was killing myself trying to determine why my IF statement was not working.. I love this site!