User Guide : Scripting : Script Operators : Logical Operators
 
Share this page                  
Logical Operators
Logical operators evaluate conditions. Use logical operators to specify multiple criteria in an expression. For example, use them in record filtering expressions when you want to select particular records based on information from two or more fields.
Examples
To select only records where ("Start Date") equals 01/01/94 and where ("End Date") equals 01/31/94 (within the same record). The And operator usually yields fewer records than the Or operator. The DateValue function is used to convert text strings to dates. Write:
FieldAt("/SOURCE/R1/Start Date") == DateValue(01/01/94) And FieldAt("/SOURCE/R1/End Date") == DateValue(01/31/94)
To select records when the ("Start Date") and ("End Date") fields are 'Text' or 'Character' data, enclose dates in quotation marks. The DateValue function is not necessary. Write:
FieldAt("/SOURCE/R1/Start Date") == "01/01/94" And FieldAt("/SOURCE/R1/End Date") == "01/31/94"
To select records where either the first or the second criteria is met. Select records where the ("Start Date") field contains 01/01/94, regardless of the ("End Date"), and all records where the ("End Date") field contains 01/31/94, regardless of the ("Start Date"). The Or operator usually yields more records than the And operator. Write:
FieldAt("/SOURCE/R1/Start Date") == DateValue(01/01/94) Or FieldAt("/SOURCE/R1/End Date") == DateValue(01/31/94)
To select records where the ("DateofBirth") field is not blank, write:
Not IsNull(FieldAt("/SOURCE/R1/DateofBirth"))
And Operator
 
Description
Logical conjunction of two operands
Syntax
result = operand1 And operand2
Remarks
The And operator determines the logical conjunction of the two operands by treating nonzero values as a value of True and zero values as False.
Example
The following expression evaluates to True if the contents of field Salary are greater than 50000 and the field HomeOwner is false.
FieldAt("/SOURCE/R1/Salary") > 50000 And Not FieldAt("/SOURCE/R1/HomeOwner")
The following truth table shows how different combinations of operands are evaluated.
Operand 1
Operand 2
Result
True (nonzero)
True
True
True
False (0)
False
True
Null
Null
False
True
False
False
False
False
False
Null
False
Null
True
Null
Null
False
False
Null
Null
Null
Contains (~) Operator
 
Description
Compare a string with a regular expression pattern to see if the pattern is contained somewhere in the string
Syntax
result = string ~ "pattern"
Remarks
The result of a contains comparison is always True or False. If the pattern is found, a True is returned. If not, False.
The advantage of this operator is that you can search for a particular string or pattern of data in a large field. If that string or pattern exists anywhere in the field, then some action can be taken on the whole field. Also, the option of matching a pattern, not necessarily a literal string, makes the matching capabilities far more flexible.
This operator requires a regular expression as the pattern. It recognizes all special characters. For information on regular expressions and special characters, see Regular Expressions.
If either operand is Null, the result is Null.
Example
The regular expression ".y.*" means any string containing any character followed by 'y' followed by any number of characters. This expression will test to see if that pattern exists in the string.
If "abcxyzzy" ~ ".y.*" Then
LogMessage("It's in there.")
Else
LogMessage("It's not in there.")
End if
This expression converts any record where the Field7 contains the letter X somewhere in it.
FieldAt("/SOURCE/R1/Field7") ~ "X"
Or Operator
 
Description
Perform a logical disjunction on two expressions
Syntax
result = expr1 Or expr2
Remarks
If either or both expressions evaluate True (nonzero), result is True (-1). To see how result is determined, see the Truth table following this table.
Example
This expression evaluates both the ("Total Cost") field and the ("Zone") field. When using the Or operator, either condition can be true to return "Yes" to the target. If both criteria are false, then "No" is returned.
The Trim Function is used to trim any leading or trailing spaces. The Val Function converts text data types into numeric values before mathematical evaluations are performed. See Trim, Trim$ Functions and Val Function.
If Trim(Val(FieldAt("/SOURCE/R1/Total Cost")) >100000) Or Trim(FieldAt("/SOURCE/R1/Zone") Like "SW") Then
  "Yes"
Else
  "No"
End If
 
Expression 1
Expression 2
Result
True (nonzero)
True
True (-1)
True
False (0)
True
True
Null
True
False
True
True
False
False
False (0)
False
Null
Null
Null
True
True
Null
False
Null
Null
Null
Null
The Or operator also performs a bit-wise comparison of identically positioned bits in two numeric expressions and sets the corresponding bit in result according to the following table:
Bit in Expression 1
Bit in Expression 2
Result
0
0
0
0
1
1
1
0
1
1
1
1
Not Operator
 
Description
Negate a numeric expression
Syntax
result = Not expr
Remarks
The Not operator inverts the bit values of any variable. If an integer variable has the value 0 (False), the variable becomes -1 (True); if it has the value of -1, it becomes 0.
Example
This example uses the Not operator to determine whether the value 1000 occurs in the source ("Field1").
Not(FieldAt("/SOURCE/R1/Field1") == 1000)
 
Expression
Result
True (nonzero)
False (0)
False (0)
True (-1)
Null
Null
The Not operator performs a bit-wise comparison of identically positioned bits in two numeric expressions and sets the corresponding bit in result according to the following table.
Bits in Expression
Bit in Result
0 (zero)
1
1
0 (zero)
Like Operator
 
Description
Compare two string expressions
Syntax
result = expression Like pattern
Remarks
If expression matches pattern, result is True (-1); if there is no match, result is False (0); and if either expression or pattern is a Null, result is also a Null. The default string-comparison method is Binary.
Built-in pattern matching provides a versatile tool for string comparisons. The pattern-matching features allow you to use wildcard characters, like those recognized by the operating system, to match strings. For the wildcard characters you can use and the number of digits or strings they match, see the table following this one.
Example
This illustrates how Like is used in an expression when the data you want to match in a particular field does not occupy the entire field. In this case, the "Equal to" (==) comparison operator does not produce the results you expect, so use the Like operator as follows:
("FIELD1") Like "No*"
 
Characters in Pattern
Matches in Expressions
?
Any single character
*
Zero or more characters
#
Any single digit (0 - 9)
[charlist]
Any single character in charlist
[^charlist]
Any single character not in charlist
word1|word2|word3
Any of the words on each side of the line (|)
A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character in expression and can include almost any characters in the ANSI character set, including digits. In fact, the special characters left bracket ([ ), question mark (?), number sign (#), and asterisk (*) can be used to match themselves directly only by enclosing them in brackets. The right bracket ( ]) cannot be used within a group to match itself, but it can be used outside a group as an individual character.
In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, [A - Z] in pattern results in a match if the corresponding character position in expression contains any of the uppercase letters in the range A through Z. Multiple ranges are included within the brackets without any delimiting. For example, [a - zA - Z0 - 9] matches any alphanumeric character.
The pipe (|) has special meaning when used with the Like operator. It can be used to provide two or more alternative strings to match against expression. All the strings except for the last are used to match the start of a string. The text after the last pipe is used to match the end of the string. For example, "yes|no|maybe" matches strings that have "yes" or "no" at the start and/or "maybe" at the end.
Like Operator Notes
The Like operator requires an entire string, rather than just a portion, to match the regular expression.
Examples
'The following example matches "abc" and "ccc", but not "bbbbbb" or "cababc".
Like "[abc]{3}"
Note:  Regular expressions can also be used in SQL actions. However, it is important to note that the syntax and special characters used in these depends on the connector database. Refer to your database application documentation for details.
Important Pattern Matching Rules
The caret character (^) at the beginning of charlist means that a match is made if any character except those in charlist are found in expression. When used outside brackets, the caret matches itself.
The hyphen (-) can appear either at the beginning (after a caret if one is used) or at the end of charlist to match itself. In any other location, the hyphen is used to identify a range of ANSI characters.
When a range of characters is specified, they must appear in ascending sort order (from lowest to highest). [A - Z] is a valid pattern, but [Z - A] is not.
The character sequence [ ] is ignored; it is considered to be a zero-length string.
The following table shows how to use Like to test expressions for different patterns using special characters:
Number of Characters
Pattern
True
False
Multiple
"a*a"
"aa", "aBa", "aBBBa"
"aBC"
Special
"a[*]a"
"a*a"
"aaa"
Multiple
"ab*"
"abcdefg", "abc"
"cab", "aab"
Single
"a#a"
"a0a", "a1a", "a2a"
"aaa", a10a"
Range of characters
"[a - z]"
"f", "p", "j"
"2", "&"
Outside a range
"[^a - z]"
"9", "&", "%"
"b", "a"
Not a digit
"[^0 - 9]"
"A", "a", "&"
"0", "1"
Combined
"a[^b - m]#"
"An9", "az0"
"abc", "a99", "aj0"
Alternative branches
"yes|no"
"yes", yesterday", "no", "albino"
"nothing", "eyes", "know", "grayest"
Note:  This operator returns all character strings with the same sequence, such as "No", "Non", and "None".