Content Query Web Part and the Yes/No Field

The Content Query Web Part (CQWP) is a pretty powerful beast. It allows you to do multiple site queries and aggregate the results. This is great for rolling up content and doing some summary type reporting. Here’s a trick to remember about Yes/No fields and using the CQWP.

If you’re building a news style site and want to aggregate say all the announcements that people tag a certain way, up onto the home page this might be a solution.

First we need to allow a way for users of all our sites to mark an announcement for inclusion on our Intranet Home Page. We’ll do this by just modifying the Announcement Content type and adding a Yes/No field to it. There are alternate ways of doing this like building a new Announcement type or stapling a feature to all sites to add our column but this is pretty low impact and only affects our current site collection so let’s go with it for now, okay? You can berate me in the comments about the proper way I should have done this part.

Go to the Site Settings for the Site Collection and click on Site Content Types under the Galleries.

This takes you to the gallery for this site and all subsites. Scroll down until you see the List Content Types and click on Announcements.

Now we’re modifying the Announcement content type which affects all those announcement lists that are created by default if you’re building sites using the Team Site template (or creating a new Announcements list on any site for that matter).

Click on Add from new site column under the Column list. This will allow us to create a new Yes/No field that users will see in Announcement items. This field will allow the user to flag the announcement for inclusion on the home page. Feel free to modify the fields as you see fit for your environment, this is just an example.

Now that we’ve added the column to our Announcements Content type we can go into any site that has an announcement list, modify that announcement and flag it to be included on our home page.

See the new Featured column? That was the result of modifying our Announcements Content Type on this site collection.

Now we can move onto the dirty part, displaying it in a CQWP on the home page. And here is where the fun begins (and the head scratching should end).

On our home page we want to drop a Content Query Web Part and aggregate any Announcement that’s been flagged as Featured by the users (we could also add the filter to handle Expires so we don’t show old content so go ahead and do that if you want).

First add a CQWP to the page then modify the settings for the web part. In the first section, Query, we want the List Type to be set to Announcements and the Content type to be Announcement so set your options like this:

Click Apply and you’ll see the results display all Announcements from any site in the site collection.

I have five team sites created each with a unique announcement added to them.

Now comes the filtering. We don’t want to include every announcement, only ones users flag using that Featured column we added.

At first blush you might scroll down to the Additional Filters part of the Query options and set the Featured column to be equal to Yes:

This seems correct doesn’t it? After all, the column is a Yes/No column and looking at an announcement in the site, it displays the field as Yes or No:

However after applying the filter you get this result:

(I have the announcements from Team Site 1 and Team Site 4 flagged as Featured)

Huh?

It’s BACKWARDS!

Let’s confirm that. Go back in and change the Additional Filters section from Yes to No and hit Apply and you get this:

Wait a minute? Shouldn’t I see Team Site 1 and 4 if the logic is backwards? Why am I seeing the same thing as before. What gives…

For whatever reason, unknown to me, a Yes/No field (even though it displays as such) really uses 1 and 0 behind the scenes. Yeah, someone was stuck on using integer values for booleans when they wrote SharePoint (probably after a long night of white boarding ways to mess with developers heads) and came up with this.

The solution is pretty simple but not very discoverable. Set the filter to include your flagged items like so:

And it will filter the items marked as Featured correctly giving you this result:

This kind of solution could also be extended and enhanced. Here are a few suggestions and ideas:

• Modify the ItemStyle.xsl file to add a new style for this aggregation which would include the first few paragraphs of the body (or perhaps add another field to the Content type called Excerpt or Summary and display that instead)
• Add an Image column to the Announcement Content type to include a Picture field and display it in the summary
• Add a Category choice field (Employee News, Current Events, Headlines, etc.) and add multiple CQWPs to the home page filtering each one on a different category

I know some may find this topic old and dusty but I didn’t see a lot out there specifically on filtering the Yes/No fields and the whole 1/0 trick was a little wonky, so I figured a few pictures would help walk through overcoming yet another SharePoint weirdness.

With a little work and some creative juices you can easily us the power of aggregation and the CQWP to build a news site from content on your team sites.