Creating a Source Filter Query

Source Filtering is an attribute of the Form Mapping and is used to restrict the number of records to be included in the migration.  For example, you may only want to migrate the records that relate to a particular company or fall within a specified date range.

Source Filtering allows you to restrict the records that are migrated either by constructing a query or by defining an Inclusion rule or both.  During execution, the query and/or Inclusion rule is evaluated and only the resulting set of records are included in the migration.

Filtering Records Using a Query

In order to define the Source Filtering Query, first navigate to the Source Filtering window by selecting the Source Filtering tab on the Form Mapping.  Then click on the Query Builder button to create or edit the query.

The query can be constructed from a combination of Field values, Keywords, Project Variables and various operators.

To add a field from the Source Form, a keyword or a variable to the Query, select the value from the corresponding dropdown list, then click on the Add button next to the Field/Keyword/Variable.

Note that fields must always be enclosed in square brackets and strings must always be enclosed in single quotes.

For BMC Remedy source servers, the following keywords are available:

Keyword	Meaning
TRUE	Logical TRUE condition for comparison with boolean fields.
FALSE	Logical FALSE condition for comparison with boolean fields.
NULL	Logical NULL condition
DATE	Equates to the current DATE with no time component;
TIME*	Equates to the current TIME with no date component;
TIMESTAMP	Equates to the current  DATE and TIME
USER	Equates to the userid for the source Remedy server connection;

*The TIME keyword is exclusively available for BMC Remedy source servers.

For ServiceNow source servers, these additional keywords are available:

HOURSAGO(INTEGER)	Used for comparison with date-time fields. e.g. [date-time field] < HOURSAGO(12) would return all records where the date-time field was more than 12 hours earlier than the current time.
YEARSAGO(INTEGER)	Used for comparison with date fields. e.g. [date field] < YEARSAGO(5) would return all records where the date field was more than 5 years earlier than the current date.
MONTHSAGO(INTEGER)	Used for comparison with date fields. e.g. [date field] < MONTHSAGO(6) would return all records where the date field was more than 6 months earlier than the current date.
DAYSAGO(INTEGER)	Used for comparison with date fields. e.g. [date field] < DAYSAGO(7) would return all records where the date field was more than 7 days earlier than the current date.
USER_SYS_ID	Equates to the SysID for the user connected to the source ServiceNow instance.

For native database source servers (Oracle, SQL Server or Postgres), the only  keywords available are TIMESTAMP and NULL.

Project variables can be used in the Query instead of a literal value.  All project variables must be created in advance before they can be selected and used in the Query, (see article on Defining project variables).

A summary of the operators available and their meaning is provided in the table below:

Operator	Description
AND	Logical AND operator to be used with two expressions within the Query: {Expr 1} AND {Expr 2}
OR	Logical OR operator to be used with two expressions within the Query: {Expr 1} OR {Expr 2}
NOT	Logical NOT operator to be used with one expression within the Query: NOT {Expr}
()	Brackets used to group a logical set of expressions together within the Query.
=	Logical Equivalence between two expressions in the Query:  {Expr 1} = {Expr 2}.  Note {Expr 1} or {Expr 2} may just be field values or literals in this case.
!=	Logical Unequal comparison of two expressions in the Query: {Expr 1} != {Expr 2}
>, >=	Logical ‘Greater than’ / ‘Greater than or Equal to’ comparison of two integer values or date values in the Query.
<, <=	Logical ‘Less than’ / ‘Less than or Equal to’ comparison of two integer values or date values in the Query.
LIKE	Used for partial match of a string.  e.g. [Surname] LIKE (‘Park’ + ‘%’)  would match all surnames beginning ‘Park’.
Str	Used to generate two single quotes in the Query to enclose a string value.
+	Used to add two strings together OR to add two integers, decimals or real numbers together OR to add an offset (in days) to a date value OR add an offset (in seconds) to a date-time or time value.
-	Used to subtract one integer/decimal/real value from another OR to subtract an offset (in days) from a date value OR to subtract an offset (in seconds) from a date-time or time value.
*	Used to multiply two integer/decimal/real values together
\	Used to divide one integer/decimal/real value by another
MOD	Used to calculate the remainder when one integer is divided by another.

When you have finished entering your query, click OK to validate the query and close the Query Builder window. If you update the query without using the Query Builder, you can use the Validate button on the bottom right to validate the syntax  If ok, a message will be displayed to indicate that the query is valid.  If not it will indicate that there is an error in the query that needs to be corrected before it can be saved.

Once your query has been validated, use the Apply button on the bottom right to apply and save the query.

Some example queries are provided below for reference:

Example 1:

[Last Modified Date] > (TIMESTAMP - (((60 * 60) * 24) * 30))

Restricts selection to those records modified within the last 30 days.

Example 2:

([Last Modified By] != 'Demo') AND ([Status] = #Status#)

Restricts selection to those records that were not modified by Demo and whose status matches the project variable called status (value provided at execution).