Admin: Filtering Tab
Filtering allows you to limit the items returned by an Active Display to only those that meet the criteria you define. When a filter is performed, the Active Display looks for text matches on the metadata returned by the Active Display. Users can further refine the results by performing searches on a filtered Active Display. Note that filtering allows you to locate data in a specific field, while searching locates data in any field in the list.
The Filter Builder makes is easy for non-technical individuals to define filters because it is not necessary to know the correct syntax; the fields prompt you for each necessary piece of information.
This topic includes the following subjects:
Defining a Filter with the Filter Builder
NOTE 1: The Filter Builder cannot be used to build the equivalent of this expression:
NOTE 2: When applying a CAML filter to an Active Display, documents in the root folder and folders one level deep are shown. Documents in folders below the first level are not shown. If you must include items in multiple levels of folders, you will need to do one of the following:
Use either a Spreadsheet or a Tree View roll-up and manually define a basic (not CAML-based) filter.
Defining a Filter with the Filter Builder
When you first access the Filtering tab, the Filter Builder is displayed. Each component of the filter is comprised of three fields:
The first field presents a drop-down list of all of the fields that are available for filtering. Click to select the field you want to start building your filter with.
The next field is used to select the desired operator for the filter (is equal to, is less than, contains, etc.).
The third field is used to identify the value you want the filter to find. You can simply type in the value you want to find, or you can use a function (see Supported Functions below).
If you only want to filter on a single field and a single value, all you need to do is click Apply or OK to save your filter. When your filter is saved, it will be converted to CAML automatically. This is done to help improve performance.
NOTE: Please do not enter any CAML code in the Filter Builder. The Builder will automatically convert the criteria to CAML for you.
The following values can be entered into the third entry field for filters based on Boolean fields:
To filter on multiple fields and/or values, you need to define additional filter components. The Filtering tab presents two groups of criteria that can be used:
The group of fields on the top half of the tab are used to define the and components of the filter. The Active Display will only display items that meet all of the criteria entered here. (For example, Status is equal to In Progress AND Priority is equal to High.)
The group of fields on the lower half of this tab allow you to define the or components of the filter. The Active Display will display items that meet any (one or more) of the criteria entered here. (For example, Status is equal to Not Started OR Status is equal to On Hold.)
To define multiple filter components on either the top or bottom half of the tab, click the Add More Filter Criteria link in the appropriate section. This adds another set of three builder fields so you can define additional criteria.
The drop-down field in the middle of the tab (below the line) presents two options, Or and And. If you have defined components on both halves of the tab, select the appropriate option for your needs.
Select Or if you want the filter to locate items that meet the criteria on the top half of the tab or any of the criteria on the lower half
Select And if you want the filter to locate items that meet the criteria on the top half of the tab plus at least one of the criteria on the lower half
NOTE: The Filter Builder cannot be used to build the equivalent of this expression:
If you want to create this type of filter, the filter will need to be defined manually.
Supported Functions
The Filter Builder supports the functions listed below. The date functions are all relative to the current date, and can only be used if the filter is defined based on a date field.
[ME] The currently logged in user
[CurrentWeekStart] The first day in the current week
[CurrentWeekEnd] The last day in the current week
[CurrentMonthStart] The first day in the current month
[CurrentMonthEnd] The last day in the current month
[CurrentYearStart] The first day in the current year
[CurrentYearEnd] The last day in the current year
[Quarter1Start] The first day in the first quarter of the current year
[Quarter1End] The last day in the first quarter of the current year
[Quarter2Start] The first day in the second quarter of the current year
[Quarter2End] The last day in the second quarter of the current year
[Quarter3Start] The first day in the third quarter of the current year
[Quarter3End] The last day in the third quarter of the current year
[Quarter4Start] The first day in the fourth quarter of the current year
[Quarter4End] The last day in the fourth quarter of the current year
[OneWeekAgoStart] The first day of the previous week
[OneWeekAgoEnd] The last day of the previous week
[TwoWeeksAgoStart] The first day of the week before last
[TwoWeeksAgoEnd] The last day of the week before last
[OneWeekFromNowStart] The first day of next week
[OneWeekFromNowEnd] The last day of next week
[TwoWeeksFromNowStart] The first day of the week after next
[TwoWeeksFromNowEnd] The last day of the week after next
[OneMonthAgoStart] The first day of last month
[OneMonthAgoEnd] The last day of last month
[OneMonthFromNowStart] The first day of next month
[OneMonthFromNowEnd] The last day of next month
NOTE: The date functions listed above look for list items with a date format of month/date/year (e.g., June 15, 2007 stored as 6/15/2007). As a result, it is not recommended that they be used if international date structures are utilized (e.g., June 15, 2007 stored as 15/6/2007).
Sample Filters
To help you see how filters can be set up, a few sample screen captures are provided here.
Example 1
In this example from the Partner Extranet Dashboard, a filter is defined to locate leads that have been created within the past 30 days.
NOTE: If you wanted to add 30 days to todays date, rather than subtract them, you could simply type +30 above instead of -30.
Example 2
This example is taken from the Picture Phone Book solution. It looks for contact items where the last name starts with A, B, C, or D.
Example 3
In this example, a filter is defined to look for all items that are due in the third quarter of this year AND EITHER:
Tips on Defining Filters
Balancing Filters Not Always Necessary with Active Displays
With Active Displays before the Spring 2007 release and with all standard roll-ups, the Filter Builder works as expected with one, two, or any even number of clauses. However, it needs help with three, five, or any other odd number of clauses. In these cases, the filter must be balanced. Rather than having an odd number of clauses, you will need to add a dummy query to create an even number. The dummy query can be an "is not Null" check and exists only to even out the query to be built by the Filter Builder.
This is not necessary for Active Displays if you are working with the Spring 2007 release or later.
Available Columns for Filter Definition
Columns do not have to be displayed in order to be referenced in a filter, but they do need to be searchable. Only searchable columns will be listed in the drop-down field in the Filter Builder.
If you are working with Spring 2007 or a later release, you can use the bottom section of the Columns tab to identify the columns that should be available for searches and filters. Any columns that are selected for display are automatically made available for searches and filters.
If you are working with a release prior to Spring 2007, to make a column searchable, the Search box must selected when the Active Display is created or modified via the Roll-Up Wizard. By default, all columns that are selected in the Roll-Up Wizard for display are also marked as searchable.
Contains vs. Is Equal To or =
Because SharePoint sometimes stores data differently than the way it is displayed, you may find that you get better results from your filters when you use the "Contains" operator instead of "Is Equal To" or =.
For example, you may see the name "Bob Smith" in a list item that includes the Assigned To field. However, SharePoint stores this name with extra characters, so the actual stored value may be something like "3;#Bob Smith." Therefore, if you want to filter on records that were assigned to Bob, you get the desired results if you build the filter with the "Contains" operator.
Using Yes/No Columns vs. Choice Columns with Yes/No Options
It is important to understand the difference between Yes/No columns and Choice columns with Yes and No options.
When you use a Yes/No column, SharePoint stores a True value when the field is selected and leaves it blank when it is not selected. As a result, if you want to filter on a Yes/No column, you need to set the filter to look for the appropriate value:
To
filter on a selected (Yes) value, the filter would be
column is equal to True (using the Filter Builder)
OR
column=True (manually defining the filter)
(replace column with the
name of your column)
To
filter on a non-selected (No) value, the filter would be
column is not equal to True (using the Filter Builder)
OR
column<>True (manually defining the filter)
When you use a Choice column with Yes and No options, the
filter should be defined as:
column is equal to value (using
the Filter Builder) OR
column=value (manually defining the filter)
Working with Documents in Folders
When the Filter Builder is used to apply a filter to an Active Display, documents in the root folder and folders one level deep are shown. However, documents in folders below the first level are not shown. If you must include items in multiple levels of folders, you will need to do one of the following:
Use either a Spreadsheet or a Tree View roll-up and manually define a basic filter (not CAML-based, not created with the Filter Builder).
When You DONT Want to Use the Filter Builder
The Filter Builder cannot be used to build the equivalent of this expression:
If you want to create this type of filter, the filter will need to be defined manually.
Conversion to CAML
All filters built using the Filter Builder are automatically converted to Collaborative Application Markup Language (CAML) when they are saved. You do not need to know how to write CAML code; the conversion is done automatically.
The benefit of CAML is that it applies the filter to list items before they are collected and stored by the web part and brings back only the items that match both the schema and the filter, thereby improving the web parts performance. Basic filters, on the other hand, bring back all of the items that match the schema and then applies the filter at the Active Display level. CAML is particularly beneficial when Active Displays search through a large number of lists to locate data, yet the amount of data returned could be small. It is also useful for more complex filtering.