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: Do not enter any CAML code in the Filter Builder. The Builder will automatically convert the criteria to CAML for you. If you have criteria that is too complex to enter in the Filter Builder, use the "Click to manually build a filter" link at the bottom of the tab and define the filter in the CAML Filter field.
The following values can be entered into the third entry field for filters based on Boolean fields:
•True
•False
•Yes
•No
•0 (translates to False)
•1 (translates to True)
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 SharePoint Data Provider 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 SharePoint Data Provider 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.
NOTE: 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.
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:
(A AND B) OR (C AND D)
If you want to create this type of filter, the filter will need to be defined manually.
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
•[MEI] – Includes the ID of the user along with their display name
•[MEID] – The currently logged in user’s ID
•[MeEmail] – The currently logged in user’s email
•[MeLoginName] – The currently logged in user’s name (domain\user)
•[Today] – Today's date
•[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 1: The filter functions [SiteTitle] and [SiteURL] functions are no longer supported.
NOTE 2: 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).
If you would like to specify a URL parameter that is used within the CAML template, specify the parameter name with [%...%] characters around it. If you want to specify a default value, separate the parameter name and the default value with a pipe character. If you want a different CAML filter to be used, specify the value CWBlankFilter and fill in the property Default CAML with the CAML to be used.
Example 1:
[%SearchText|Task%]
Example 2:
[%SearchText|CWBlankFilter%]
If you would like to specify a Cookie Name that is used within the CAML template, specify the parameter name with [:…:] characters around it. To specify a default value, separate the parameter name and the default value with a pipe character. To use a different CAML filter, specify the value CWBlankFilter and fill in the property Default CAML with the CAML to be used.
Example 1:
[:Company|CorasWorks:]
Example 2:
[:Company|CWBlankFilter:]
To help you see how filters can be set up, a few sample screen captures are provided here.
In this example, 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 today’s date, rather than subtract them, you could simply type "+30" above instead of "-30".
This example looks for contact items where the last name starts with A, B, C, or D.
In this example, a filter is defined to look for all items that are due in the third quarter of this year AND EITHER:
•High priority OR
•Not started
