The third step in setting up the Data Publisher is optional. If you want to, you can define a filter to limit the records that are included in the copy or move. You can filter on any one or more fields in the source list, as long as you mapped the field(s) in Step 2 (Select Lists and Map Fields).
Individuals with administrator access can turn on or off a user interface that makes it easy to build and apply filters for the data transfer. When the Filter User Interface (Filter Builder) is turned on, the fields described below are displayed. When it is disabled, users are presented with a single field called "Filter" and are required to construct a filter string manually. For syntax and other details regarding manually building complex filters, please see Manually Build a Filter.
When you are finished defining your filter, click the Store Settings button and then select the link in the lower right corner of the screen to go to the next step in the configuration.
This field presents a drop-down list of all of the fields in the source list or library that you selected on the previous screen (Select Lists and Map Fields). It is important to remember that you can only build a filter based on the fields you mapped in the previous step. Click to select the field you want use to start building your filter.
This field is used to select the desired operator for your filter (equal to, less than, contains, etc.).
This field is used to specify the field value you want the filter to find. Wildcard characters (*, %) are allowed at the beginning or end of a value but not in the middle.
If you selected a date field above and you leave the Value field blank, it will default to today. You can also enter a date in this field in any standard format, or any of the following variables:
• [Today] = Today's Date
• [CurrentYearStart] = The first day in the current year
• [CurrentYearEnd] = The last day in the current year
• [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
This field will only be displayed if you selected a date field above. It allows you to define the number of days to add or remove from the value specified above. You can enter either a positive or a negative whole number. For example, if you wanted to filter on records that were modified within the past week, you would set the Field to Modified, When to "Is Greater Than Or Equal To," leave the Value field blank, and set the Days to -7.
If you want to add to your filter and look for multiple fields or multiple values in a field, click Add Another Filter below the Value field. An additional set of entry fields is displayed. All of the additional fields are the same as those described above, with the exception of the And/Or field.
The And/Or field is used to specify how the additional expression should be added to the filter.
• And: Filter on records that meet both criteria
• And - New: Filter on records that meet both criteria and treat the next criteria as if it was in a set of parentheses
• Or: Filter on records that meet either criteria
• Or - New: Filter on records that meet either criteria and treat the next criteria as if it was in a set of parentheses
For a detailed explanation of the "And-New" and "Or-New" options, please see Creating Filters in Usage Topics.
When you manually build a filter, the basic format
is:
Field=SearchText (no spaces)
Example:
Postal Code=11111
Standard filter functions are supported.
When creating a complex filter, use the column name to refer
to columns. For example, if the column name for one column is UnitPrice and the
column name of a second is Quantity, the complex filter would be:
UnitPrice *
Quantity
When creating a complex filter, enclose strings with single
quotes:
LastName = 'Jones'
The following characters are special characters and must be
escaped, as explained below, if they are used in a column name:
\n
(newline)
\t (tab)
\r (carriage
return)
~
(
)
#
\
/
=
>
<
+
-
*
%
&
|
^
'
"
[
]
If a column name contains one of the above characters, the
name must be wrapped in brackets. For example, to use a column named "Column#"
in a complex filter, you would write [Column#] as in the example below:
Total
* [Column#]
Because brackets are special characters, you must use a slash
("\") to escape the bracket if it is part of a column name. For example, a
column named "Column[]" would be written:
Total * [Column[\]]
Only the second bracket must be escaped.
The same holds true if your column name contains a space in
the name. For example, to use a column named "First Name" in a complex filter,
you would write [First Name]:
[First Name]='John'
User-defined values may be used within complex filters and will be compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs (#). Decimals and scientific notation are permissible for numeric values. For example:
• FirstName = 'John'
• Price <= 50.00
• Birthdate < #1/31/82#
For columns that contain enumeration values, cast the value
to an integer data type. For example:
EnumColumn = 5
Concatenation is allowed using Boolean AND, OR, and NOT
operators. You can use parentheses to group clauses and force precedence. The
AND operator has precedence over other operators. For example:
((LastName =
'Smith' OR LastName = 'Jones') AND (FirstName = 'John'))
When creating comparison complex filters, the following
operators are
allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in complex filters:
• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)
• % (modulus)
To concatenate a string, use the + character. Whether string comparisons are case-sensitive or not is determined by the value of the DataSet class's CaseSensitive property. However, you can override that value with the DataTable class's CaseSensitive property.
Both the * and % can be used interchangeably for wildcards in
a LIKE comparison. If the string in a LIKE clause contains a * or %, those
characters should be escaped in brackets ([]). If a bracket is in the clause,
the bracket characters should be escaped in brackets (for example [[] or []]). A
wildcard is allowed at the beginning and/or end of a pattern. For
example:
ItemName LIKE '*product*'
ItemName LIKE '*product'
ItemName
LIKE 'product*'
Wildcards are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
The following aggregate types are supported:
• Sum (Sum)
• Avg (Average)
• Min (Minimum)
• Max (Maximum)
• Count (Count)
• StDev (Statistical standard deviation)
• Var (Statistical variance)
• Null Identifiers
If a column does not contain data, the use of a column='' is not permissible and will not return the results that you are trying to find. Since the column row is empty, it will not respond with a String result, but rather a NULL response. Because of this, you will need to utilize the "IS NULL" or "IS NOT NULL" request to see if a column contains or does not contain any data.
Example:
[First Name] IS NULL
[First Name] IS NOT
NULL