Filtering

The Filtering page allows you to use CAML filters to dynamically filter records or use Complex Filters to define static filter for the data.

The Filtering page contains the following:

 

Table 10. Filtering

Element	Description
CAML Filter	Allows you to specify the CAML (Collaboration Application Markup Language) query expression to filter your data. CAML is an XML-based query language. The CAML Filter will only be applied to the data. The filter will not be applied to the responses. For more information, see CAML Filters
Complex Filter	Allows you to use the Complex Filter expression to define a static filter for the data. The complex filter is applied to the data after the CAML Filter is applied. NOTE: The Complex Filter is provided here only for backward compatibility reason. In the past, Complex filter was used to support date functions. For more information, see Complex Filters

CAML Filters

CAML Filter expressions help filter data by providing a way to link multiple conditions.

Turn Advanced Mode on if you want to edit CAML Filters in XML format.

CAUTION: If the web part supports both CAML Filter and Complex Filter, use the CAML Filter. The CAML Filter processes data in the SQL Server, so the data is transferred to the Web Server. The Complex Filter processes data in the Web Server. If you specify both CAML Filter and Complex Filter, the CAML Filter is processed first.The Complex Filter remains for backward compatibility reasons.

The operators and field names in the CAML Filter are case sensitive. For the field name, use the internal name of the field.

 

NOTE: This is different from the Complex Filter that uses the Display Name.

The Boolean operators <And> and <Or> are supported in CAML Filter. These operators are used to combine two conditions. They must contain exactly two conditions. For example:

<And><Eq>...</Eq><Gt>...</Gt></And>

If you need to link more than two conditions, such as Condition1 And Condition 2 And Condition3, you must nest these operators. For example:

<And><Eq>...</Eq><And><Gt>...</Gt><Lt>...</Lt></And></And>

You can specify conditions for CAML Filter in either of the following ways:

•	<IsNull><FieldRef Name="InternalFieldName"/></IsNull>

This returns all entries whose field specified by InternalFieldName does not contain any value.

•	<IsNotNull><FieldRef Name="InternalFieldName"/></IsNotNull>

This returns all entries whose field specified by InternalFieldName contains values (including empty string value).

•	<Operator><FieldRef Name="InternalFieldName"/><Value Type="TypeName"> ValueToCompare</Value><Operator>

Where:

 

Value	is
Operator	One of the following: • Eq — equals to operator • Neq — not equals to operator • Gt — greater than operator • Geq — greater than or equals to operator • Lt — less than operator • Leq — less than or equals to operator • Contains — a string operator to check whether a string contains the specified value • BeginsWith — a string operator to check whether a string begins with the specified value
TypeName	One of the following: • Text • Integer • Float • DateTime NOTE: When you compare a DateTime value, only the date is being processed. The time part is ignored by SharePoint
ValueTo Compare	One of the following: • String • Number • Date/Time value in the format — YYYY-MM-DDTHH:MM:SSZ • Date/Time value in the format : - <Today OffsetDays="number"/> — today's date with optional number of days to offset the today's date. You can use the OffsetDays attribute to do date range filter. The OffsetDays attribute value must be a number (both positive and negative numbers)) - <UserID/> — the current user. This value only works if the field is a user lookup field)

Using CAML Filters

CAML filters allow you set the following variables to validate text fields:

 

Table 11. CAML Filter variables

Variable	Description
<%Now%>	Current time and date
%FilePath%>	Complete path for a file in a document library
<%CurrentUserName%>	Display name of the current user
<%CurrentLoginName%>	Login name of the current user
<%CurrentUserID%>	ID of the current user, for example: 1
<%CurrentUserEmail%>	Email address of the current user

Variables work if you store either the user name, login name, user ID, or email address in a Single Line of Text field. qListForm and qSIListForm use variables in the Fixed Value or Form Component Behavior sections.

For a field of type Single-line-of-Text or Multi-line Plain Text, the Operator drop-down menu contains the value, Matches, as an operator for comparing the field value to a regular expression.

Examples of common regular expressions:

•	Match beginning of a string: ^Test matches any string that starts with “Test”.

•	Match ending of a string: test$ matches any string that ends with “test”.

•	Telephone number: ^\d{3}-\d{3}-\d{4}$ matches any phone number in the format of 555-555-5555.

Using Dynamic Variables

The CAML Filter can also be used with dynamic variables. The Value element in the CAML Filter can come from various sources such as an HTTP Parameter and Session.

 

NOTE: If you want to use Advanced Mode, you can extend the value element in the CAML filter. Some examples are: • to enter the value in the Value element - <Value Type="Text">Some Text</Value> • to retrieve the value from an HTTP parameter - <Value Type="Text" Source="HttpParameter" SourceName="NameOfHTTPParameter"/> to retrieve the value from the Session variable - <Value Type="Text" Source="Session" SessionName="SessionName" SourceName="NameOfHTTPParameter" SiteUrl="url" ListName="ListName"/> to compare values of two fields - <Value Type="FieldName" Source="Field">Editor</Value>

Complex Filters

Complex Filter provides a powerful way to concatenate multiple conditions.

CAUTION: If the web part supports both CAML Filter and Complex Filter, use the CAML Filter. The CAML Filter processes data in the SQL Server, so the data is transferred to the Web Server. The Complex Filter processes data in the Web Server. If you specify both CAML Filter and Complex Filter, the CAML Filter is processed first. The Complex Filter remains for backward compatibility reasons.

The date and time value must be enclosed in # and specified in ISO 8601 format: YYYY-MM-DDThh:mm:ssZ. For example, midnight of February 14, 2002 is #2002-02-14T00:00:00Z#.

The operators and field names in the Complex Filter are case sensitive. For the field name, use the display name of the field (include all spaces if there are any).

The following Boolean operators are supported in Complex Filter:

•	NOT — This is used to negate a condition. For example, to get all employees whose name does not start with M, they syntax is: NOT Employee Name LIKE 'M%'

•	AND and OR — This is used to concatenate two conditions. The AND operator takes precedence over the OR. Use parentheses to change the precedence of the operator.

For example, to get all employees in the Accounting or IT department and hired after 1/1/2004: (Department='Accounting' OR Department='IT') AND Hired Date>#2004-01-01T00:00:00Z#

Specify conditions for Complex Filter is specified in one of following ways:

1	FieldName IS NULL

This will return all entries whose field specified by FieldName does not contain any value.

Some entries contain an empty string value ("") and their display is indistinguishable from the entries with a NULL value. If you want to return such entries, use FieldName='' as the filter expression.

2	FieldName IS DBNULL

This will return all entries whose field specified by FieldName contains DBNull value. It is tricky to determine whether a field is empty, contains null value or contains DBNull value. It all depends on the field type and the type of back end system that you are dealing with. Therefore, try each one ("fieldName IS NULL", "fieldName IS DBNULL" or "fieldName=''") in order to identify which one is the appropriate option for your needs.

3	FieldName IS NOT NULL

This will return all entries whose field specified by FieldName contains values (including empty string value).

4	FieldName comparison-operator FieldExpression

Comparison operator is one of these: =, <> (not equal), <, >, <=, >=, LIKE

FieldExpression can be one of the following:

•

String — A string is enclosed with single quote; for example 'Accounting'. A string can contain wild-card character % or * when used in conjunction with the LIKE operator. For example: 'M%', 'M*'. A wild-card character is not allowed in the middle of a string. Therefore, this expression is illegal: 'Te*xt'

•	Numbers — Numbers are not enclosed. For example: 20, 20.5, 0.5. If you enclose a number with a single quote, it is treated as string.

•	Dates — For example: February 5, 2005 11:50 PM is #2005-02-05T23:50:00Z#.

•	[ME] — This expression is replaced with the name of the currently logged-in user.

•	[TODAY] — This expression is replaced with midnight of today's date.

•	[NOW] — This expression is replaced with the current date and time.

•	Date function — The following date functions are supported: AddSeconds, AddMinutes, AddHours, AddDays, AddMonths and AddYears. Their syntax is DateFunction(dateValue, number). For example: AddDays([TODAY], 7) adds 7 days to today's date

The following are some examples of the complex filter:

1	All my current task that are due within 7 days

PerformedBy=[ME] AND DueDate>=[TODAY] AND DueDate<=AddDays([TODAY], 7)

2	All unassigned tasks that are due within 3 days

PerformedBy IS NULL AND DueDate>=[TODAY] AND DueDate<=AddDays([TODAY], 3)

3	All overdue tasks

DueDate<[TODAY]