Operators

AND (&&)

OR (||)

NOT (!)

LIKE

Performs a pattern search using wildcard characters, including %, -, [ ], [-], and [^]. For example, 'Submitter' LIKE "Bob%ton" finds all requests with a submitter name that begins with the letters "Bob" and ends with the letters "ton"--such as Bob Compton and Bobby Fenton. See Wildcards. The LIKE operator applies to character and diary fields only. LIKE with comma separators can be used as the accrue operator in full text searches. For more information about full text search, see Enabling and disabling full text search.

Some databases do not support all the possible wildcard matching characters. To determine whether there are restrictions on the wildcard characters that you can use in SQL queries, check your database documentation.

EXTERNAL()

Enables all or part of a qualification to come from a field on the form. Used in qualifications for active link or filter Run If conditions, Push Fields or Set Fields actions, table field properties, and Search type menu definitions. EXTERNAL() is not used in escalations and flashboard variable qualifications. Enter a character field name or field ID into the EXTERNAL() operator, for example, EXTERNAL($field_name$) or EXTERNAL('50'). EXTERNAL() evaluates to True when:

• The field specified contains an expression that evaluates to True.
• The field is empty.

• The field does not exist or is not a character field. You can also concatenate expressions. For example:

  EXTERNAL($field_name$) AND 'Submitter' != $NULL$
  

If the qualification in a field called by EXTERNAL() includes keywords, you must add an escape character () to the keywords. This prevents the keywords from expanding before being used in the qualification. For example, if you have a table field with a qualification of EXTERNAL($Qualify Field$), and and Qualify Field is a character field with a value of:

('Create Date' < $TIMESTAMP$) AND ('Login Name' = $USER$)

Then, the table field does not produce the expected results when refreshed. Instead, the keywords expand, producing a qualification such as:

('Create Date' < 05/22/02 11:00:34 AM) AND ('Login Name' = Demo)

This is an invalid query because the date/time and character values are not enclosed in quotation marks. To prevent the keywords from expanding, write the qualification like this:

('Create Date' < $\TIMESTAMP$) AND ('Login Name' = $\USER$)

In forms viewed in the web client, if EXTERNAL() references a field that contains a qualification such as $Date 1$ <= 'Date 2', you must add double quotation marks around $Date 1$:

"$Date 1$" <= 'Date 2'

If EXTERNAL() references a field whose value is set by a Set Fields or Push Fields action, use four double quotation marks to create a single double quotation mark. For example, to set the field's value to:

'Create Date'>="10\31\05" AND 'OtherField'="Hello"

Set it as follows in the Set Fields or Push Fields action:

"'Create Date'>=" + """" + "10\31\05" + """"+ " AND 'OtherField'=" + """"+ "Hello" + """"

In the web client, the EXTERNAL operator reads the date and time string from client's time zone. For more information, see Case sensitivity and using quotation marks section in the topic Process command syntax.

+

-

Performs any of the following calculations:

• Subtracts two integer, real, or decimal values.
• Subtracts two time values (resulting in an integer).
• Subtracts an integer, real, or decimal interval from a time value. (Real and decimal values are truncated to an integer value.)

For example, 'Create Date'>$DATE$-604800 finds all requests created within the last seven days (where 604800 is the number of seconds in one week).

*

/

%

Supplies the modulo of two integer values (the remainder of a division of the values). For example, 'Group ID'%2=1 finds all requests with an odd number in their Group ID field.

Use the modulo operator only with integer fields. If you use this operator with fields that have other data types, such as Date/Time, an error occurs.

<

>

!=

Matches contents that are not equal to the value. For example, 'Status'!="Closed" finds all requests that are not closed.

If possible, avoid using the != operator when building qualifications. For more information, see Creating efficient qualifications. On Sybase databases, you cannot use the != operator against unlimited character fields where the database input length equals 0.

<=

>=

+