When building or editing expressions in the Expression Builder, note the following guidelines and best practices:
-
Readability. To improve the readability of your expressions, include the
following formatting:
- Parentheses
- Carriage returns
- Spaces
- Tabs
For example, the expression
LENGTH(Name) = 7 AND PATTERN(Name,"rich") LIKE "u-*"
can be reformatted as follows:LENGTH(Name) = 7
AND
PATTERN(Name,"rich") LIKE "u-*"
-
Parentheses. Use parentheses to determine precedence. For example:
1+2 * 3+4 = 11
(1+2) * (3+4) = 21
Also, when working with complicated expressions, as a best practice, surround the expression in parentheses to ensure expected results.
- Long expression. When creating long or complex expressions, consider breaking down and testing parts of the expression to verify they work as expected, and then add the parts into the more complex expression. Also, periodically copy and save the expression. You may want to backtrack to the saved version or use it as a basis for another expression.
- Comparison. When building a comparison expression and you are unsure of the case of your data values (upper or lower), consider including the UPPER or LOWER function with your expression. For example, UPPER ([Shirt Color]) = "BLUE".
- TRIM function. Consider including the TRIM function when you need to ensure there are no leading or trailing spaces that may affect expression results, such as with numeric values; for example, ISNUMERIC(TRIM(LPACK(value))).
- Attributes. Do not use attribute names that share function keywords; for example, Metaphone, Type, and Average. The Expression Builder will recognize these words as function keywords only. When necessary, change an attribute's name before using it in a business rule, conditional, or other type of expression.
-
Color. As you build your expressions, the syntax is colorized as
follows:
- Numbers without quotes in gray.
- Function keywords in blue.
- Operator keywords and arithmetic operators in purple.
- Strings and other values in double-quotes ("") green.
- Strings and other values in single-quotes (' ') in red.
- Literal numbers and any other values in black.
-
Comments. You can add comments (displayed in green) to expressions in two
ways:
- For multi-line comments, add text between /* .... */.
- For end-of-line comments, add text after a double-hyphen (--).
-
Escape characters. If you use IF/THEN conditional statements, you must precede
nested literal characters with a backslash, referred to as an escape character ( \ ).
Adding the backslash ensures the nested literal character is read as part of the string,
not the end of the string. Use the following valid escape characters:
- \\ = \ (backslash)
- \t = tab
- \' = ' (single quote)
Note: To escape a double quote ("), use " " together instead of the backslash. For example,
Address IN (""""), using two double quotes within double quotes.