Month and Year Drop-Downs for Advanced Date Range Filtering in SharePoint

Update: we introduced a much simplified setup approach to value-range filters on 26^th Feb 2010. We recommend you try that first, before falling back if necessary to the steps described in this article.

When we introduced you to FilterZen's value range filtering capabilities for SharePoint List Views, Data Views and other Web Parts back in June, we showed you the simplest possible solution that uses Date Filters. As you probably know, Date Filters can send date or date/time values, optionally in a specific locale and format, and optionally offering a SharePoint Date Picker for end-users to make a selection. With the above solution in place, you can let your users choose the bounding start and end dates to form the filter range just by making your Date Filters interactive from their settings pane.

Sometimes, however, that is not quite the user experience you want to deliver for date range filtering. For example, users may prefer just selecting from readily populated drop-down lists bounding start and end months rather than picking the 1st and 31st dates via That Nuisance of a Date Picker. Just two days ago we received a request to that effect:

We need the ability to filter a list by date range, bracketed by months that the user selects. IE: a user would select a beginning month/year of November 2009 and an ending month/year of January 2010 and get all items in and between those months. Further, if no entry is selected for beginning or ending month, that end of the bracket is ignored. IE: a user would select only a beginning month of November 2009 and get all items in and after that month. IE: a user would select only an ending month of January 2010 and get all items in and before that month.

Achieving this is with FilterZen requires some minimal effort, but really not that much. For starters, the latter requirements are already implicitly supported by FilterZen out of the box: if start or end dates or months are blank (not selected), then that's an "open" range into the past or the future.

How, then, do you replace the SharePoint Date Pickers with drop-down lists readily populated with months such as Nov 2009, Dec 2009, Jan 2010 and so on? In FilterZen, you could start out setting up Choice Filters, since they let you provide drop-down lists. But you want to logically associate each pickable "month/year" choice with a real start and end date. (Even though pick "month/year" choices, FilterZen still needs to send real date/time values to your connected Web Parts!)

Your next best choice, then, is to set up a helper list associating your pickable "month/year" choices with their corresponding starting and ending dates, as Real Date Columns. If you're on WSS 3.0 / MOSS 2007, SP2 English, just import this List Template (STP) file, which already contains the years 2008 through 2010. Your goal is to end with a List such as this:

Once you have those date mappings available in your SharePoint Site Collection, you go ahead to set up filters in your FilterZen Web Part. Start by following our earlier date range filtering tutorial. Only once you have the two bounding Date Filters (remember to give both of them the same name, that of the Column being filtered) in place and made sure they actually work and do indeed send the correct range to your connected Web Part (make them interactive by ticking their Allow users to enter a filter value check box options so you can test them out easier), only then should you return to this article to continue with the last remaining steps.

• First, remove the interactivity of your Date Filters again by un-ticking their Allow users to enter a filter value check box options.
• Create two List Lookup Filters, one called StartDate and one EndDate. Set both of them up with the following settings:
  • Tick the This filter is enabled check box option.
  • Just underneath, specify the URL of your Months helper List mentioned above. If this resides in the current Web Site, a "relative" (partial) URL such as Lists/Months/AllItems.aspx will work just fine.
  • For value Column, specify either Start_x0020_Date or End_x0020_Date, depending on the filter (and your Months helper List Column names, of course).
  • For display Column, specify Title.
  • Tick the Allow users to pick a filter value check box option.
  • Expand the Advanced Filter Properties section, and from the Always suppress certain filter values drop-down list, select Suppress (don't send) all except...

At this point, you now have:

• two interactive (ie. end user-visible) drop-down filtering controls (your List Lookup Filters) listing the pickable month/year choices but not sending any values (because we "suppressed" them and because your connected Web Parts probably don't contain any StartDate and EndDate Columns), as well as
• two invisible Date Filters (both connected to a single connected Web Part Column) that could send values but have no idea which values they should send.

The last step therefore is to somehow "connect" your interactive drop-down filtering controls (that is, your List Lookup Filters) to your invisible Date Filters. Starting with version 3.5, this is easily accomplished. Just open the settings tool pane for each of your two Date Filters, and for default date filter value, specify either {$StartDate$} or {$EndDate$}, depending on the Date Filter and the names you gave your List Lookup Filters during the steps taken earlier and described further above.

If you run into any issues while trying to get this to work, just let us know in the FilterZen Feedback Forum and we will be happy to help!