Restrictions to Source Filtering Queries when Source Server Type is ServiceNow

When the source server type is ServiceNow, the following restrictions apply:

a) For a Relational Operation, the Left Hand Side of the expression must be a field or a project variable, e.g:

1. [Name] = ‘Tom’
2. [company] = #Company Name#
3. #Single Company# = TRUE
4. ([company] = #Company Name# AND #Single Company# = TRUE) OR #Single Company# = FALSE

The value can be either a fixed value, project variable or another field of the same type.

b) All arithmetic operations (+ - * / MOD) are supported for numeric values.   The ‘+’ operator can also be used for string concatenation.

c) If a query contains a string value for a field which has choices (e.g. State field on HR Case), the choice label value will be replaced with the underlying value when the query is run. This should be the case for both string and integer fields. If no underlying value can be found for the choice label given, the choice label will be used in the query (usually resulting in no entries being returned)   e.g. [State]='Draft' will run as [State]=1

d) If a query contains a display value for a reference field, the value will be replaced with the corresponding sys id before the query is run. Again if no matching record is found, the display value is used, generally resulting in no match.  e.g. [assigned_to]='Abel Tuter' will run as [assigned_to]=<sysid of Abel Tuter user entry>

e) Conditions c) and d) above also hold true when using variables. The variable used should be defined as a STRING, even if the field being compared to is actually a non string field.  e.g. [state]=#STATE#, where #STATE# is a string variable with value 'Ready' will run as [State]=10

f) Date/Time arithmetic is NOT supported. The additional parameterised keywords are available for this purpose.

g) Only variables and literal values can be used in arithmetical operations. Field references cannot be used.

h) LIKE operator. This is supported in a limited way:

i) Only the '%' (match zero or more characters) wildcard is supported.

ii) Field references cannot be used in place of a value.

iii) The wildcard must be either at the start and/or end of the string. e.g. 'Bob % Backline'  is not a valid search pattern but '%Bob%',  '%Backline' and 'Bob%' are all valid.

iv) The use of a variable to define a search string is not supported, however arithmetic operations can be used to build a pattern string. e.g. [Name] LIKE '%'+#searchName# +'%' is valid.

i) When using a project variable as the left hand side of a relational operation such as <Variable><op><Value>, the following additional restrictions apply:

<Variable> must be a valid project variable

<Value> can be either a constant value, keyword or another variable.

<Value> CANNOT be an arithmetical expression. The type should be consistent to that of the variable.

<op> must be any relational operator that is relevant to the data type. LIKE is also supported for string comparison (case insensitive).

For example the following are supported:

#Boolean#=TRUE

#Boolean 1#!=#Boolean 2#

#DATE TIME#>TIMESTAMP

#Company Name# LIKE '%bridge%'

There can be multiple clauses of this type in a single query.

This last case does NOT apply to lookups. If an attempt is made to add such a clause in a lookup it will result in a design time error.