Expression Syntax |
Top Previous Next |
An Expression is simply a formula with values and functions, or at least one side of a formula, which evaluates down to a single value. The primary power of expressions comes from the built-in functions for extracting data values, converting and manipulating values, and making things happen.
An expression may contain the following elements:
Operators : +, -, >, etc.
Numeric values : 2, 5.3, .005, etc.
Text values : "Hi", 'a', etc.
Boolean values : .T. or .F.
Date values : {1/1/2005}
Function calls, often with parameters : Upper("hi")
Variables, user-defined : R, Total, etc.
The lowest-level format of an expression is a single value or function, or two expressions separated by an operator.
Here are some simple examples of expressions:
"Hello world"
2 + 2
Pi() * (R ^ 2)
Percent( 55/100 )
Resv:Resv_Adults > 2
iif( Resv:Resv_Adults > 2, "Extra adults present", "Two or less adults" )
FieldDate( ThisResv(), "Resv_First_Date" )
"Today is " + DateToText( Today() )
"Tomorrow is " + DateToText( Today() + 1 )
MessageBox( "Last name is " + FieldText( ThisCust(), "Cust_Last_Name") )
For a complete list of operators, functions, and other expression elements you can use, refer to the Expression Elements dialog. This is available anywhere expressions are entered so that you can build expressions by selecting from a list rather than memorizing functions or typing everything in.
Operators
For most programmers, especially C and C++ programmers, the operators will be familiar. Here's a list of possible operators in expressions:
Mathematical operators (used with numeric values, numeric results)
•+ or - can be used as a unary operator. There must not be a space between this and the value following it. Examples: +5, -10 -Len(cText)
•+ -- Add. Can also be used with dates to add a # of days, and with text to concatenate. Examples: 3 + 2, Today() + 3, "Name:" + Cust:Cust_LName
•- -- Subtract. Can also be used with dates to subtract a # of days. Examples: 3 - 2, Today() - 30,
•*, / -- Multiply and divide, respectively. Examples: 2 * 3, 2 / 3
•^ -- Raised to the power. Example: 2 ^ 3 (equals 2 times 2 times 2)
•% -- Modulo, or remainder. Gives the remainder after division. Example: 10 % 4 (the result is 2)
Conditional operators (Boolean results, used in condition expressions)
•> -- Greater than. Example: 3 > 2
•< -- Less than
•>= -- Greater than or equal to
•<= -- Less than or equal to
•=, == -- Equal to (both work the same)
•!=, <> -- Not equal to (both work the same). Example: Len(cText) != 3
•AND, && -- Logical "And" (for boolean values, both must be true for the result to be true). Example: Empty(cText) AND Empty(cName)
•OR, || -- Logical "Or" (for boolean values, result is true if either values are true). Example: Empty(cText) || Len(cText) = 4
•NOT -- Logical negation, e.g. makes boolean True become False. A space should follow it. Example: NOT Empty(cText)
•! -- unary version of NOT. There should not be a space following if. Example: !Empty(cText)
Operator Precedence
As with any programming language, some operators have hight precedence, or priority, than others. This is the order of precedence, with the highest priority on top:
| 1. | + or - unary operator |
| 2. | NOT, ! |
| 3. | ^ |
| 4. | *, /, % |
| 5. | +, - |
| 6. | >=, <= |
| 7. | =, ==, !=, <>, >, < |
| 8. | AND, && |
9. OR, ||
Of course you can use parenthesis around expressions to force priority, like ( (23 + 5) * (3-1) ) % 3.
Value Types
As hinted at above, there are several different types of values. Expressions are largely "type-safe", which means that in most cases you must use the expected type of value or else a parsing error will occur. For instance if a function requires a numeric value argument, then any other argument type will create an error. Also most operators must have matching types on each side (see the Syntax Rules).
Numeric values -- Integer or floating point numbers, using digits, a decimal an an optional minus sign in front. Do not include commas (e.g. 10,000 should be entered as 10000). Scientific notation (e.g 2.3E+9) is not allowed.
Character/Text values -- Anything enclosed in single or double quotes is considered text. There is no difference between single-character text an text strings. Quotes of the same type cannot be nested, but mixed single/double can be nested -- e.g. "He said "Hi"!" is not correct, but 'He said "Hi"!' is correct. If you must use the same kind of quote in the text, it can be "escaped" with a backslash: "He said \"Hi\"!" will work. Also, text should not contain any control characters, like the return or linefeed characters.
In some special places where long text is used such as in Forms definitions or MessageBox() messages, the escape sequence "\r" can be used for return, as well as "\n" for line feed and "\t" for tab. Any other control characters can also be entered with hexadecimal escape sequences like "\x07", but again keep in mind that these are only of use in certain places like Forms definitions. If you need to have a backslash character in the text and it happens to precede a letter that would normally make it an escape sequence (like "\r"), then you can force the backslash by using a double-backslash, e.g. "\\". Thus to enter the text "set\reset", the expression "set\\reset" would be used.
Also keep in mind the quote-nesting rules -- each layer of nesting will do escape sequence processing, so any backslashes used for escape sequences may need to be multiply-escaped. For instance to get a carriage return in a MessageBox message that's in quotes itself, duplicate the backslashes for each level: EvalQ('MessageBox("Line1\\rLine2")')
Note: As a shortcut, text values can be "added" together (concatenated) using the plus (+) operator -- for instance: "Hello " + TextFunction() + " World".
Boolean values -- Also referred to as yes/no or true/false values. These are represented as the letter T or F with periods on each side (.T. or .F.). Keep in mind that this is also the way they would be displayed in a Query, for instance, if used as a raw boolean value. A simple way to convert these to Yes/No text is to use the iif function: iif(value, "Yes", "No") will return "Yes" if the value is true, "No" if it's false.
Date/time values -- There isn't a specific difference between date and time values -- technically one value contains both a date and a time. However the context in which it's used will determine whether the date or time portion is of interest. To enter a raw date in an expression, enclose it in curly-braces: {4/15/06}. A raw date value will also be shown this way, but you can convert a date to text using DateToText function. Dates are the one exception to the operator-type-matching rule. Adding a number to a date is an easy way to add (or subtract) a number of days. For instance, {4/15/06} + 10 = {4/25/06}.
Record values -- This is a special value type, in that records only have an internal representation (they're essentially an internal pointer), and therefore can be part of an expression as a result of a function or as a variable. The only valid operators for records are the equal and not-equal conditional operators. One common operation is to test for a record being "null", e.g. to see if the ThisSite context is valid. This must be done using the NullRecord function, like: ThisSite() != NullRecord(). Note that if a raw record value is displayed, only its record ID will be shown with a "#" prefix (you may see this when testing an expression in the Expression Creator dialog).
Unknown values -- This isn't really a "type", but you'll see reference to this in some function definitions. For instance, the Macro function has an unknown return value type because the result type will not be known until the macro expression is executed. Of course as the creator of the Macro you should know what the result will be, but the expression parser won't know. This can cause a parsing error if it's in an expression where a known type is needed, such as an argument to a function or on either side of an operator. In this case, you'll want to use one of the single-letter type-casting functions to force it to be a known type: N( ) for numeric, C( ) for character, D( ) for date, etc. -- e.g. N(Macro("MyMacro")) if the macro is known to return a numeric value.
Date Formats
When using date values in curly-braces as shown above, the date must be in a recognizable format. There are a few different formats that are recognized, shown below.
Mon dd, yyyy - e.g. Mar 25, 2007 -- Month must be 3-letter English abbreviation
mm/dd/yyyy - (or d/m/y for non-U.S.) e.g. 3/25/07, 03/25/2007
Mon/dd/yyyy - e.g. Mar/3/2007
yyyy/mm/dd - e.g. 2007/03/25
yyyy/Mon/dd - e.g. 2007/Mar/25, Month must be 3-letter English abbreviation
Note that a dash or period (- or .) can be used instead of a slash ( / ), and that the day or month numbers can be with or without leading zeroes (e.g. 3 or 03). Except for the formats where the year is first, the year can be either 4 digits or 2 digits. Where the 3-letter month abbreviation is used, it can be upper or lower case, or mixed.
Expression Syntax Rules
Here are the basic rules for expressions:
•Function names are not case-sensitive (Today = TODAY)
•Function names must be immediately followed by the '(' character, with no space after the function name.
•There is no distinction between integer or floating-point numeric values. Internally they are all treated as floating-point, though some functions will only use the integer (truncated) portion.
•Parenthesis may be used around expressions to force the operator precedence.
•Anything not recognized as an operator, value or a function call is assumed to be a Variable, e.g. in 2 * R * Pi(), "R" is assumed to be a variable.
•Spaces are not required around operators, values, or variables, but they are allowed (and recommended for readability).
•Text can be enclosed in single or double quotes ( " or ' ). These can be nested inside each other to one level, if a function call requires a quoted expression (for instance, the following are OK: Evalq( "Evalq( 'Str(5)' )" ) or Evalq( "Str(5) + ' this is a 5.' " ), but this is not: Evalq( "Evalq( "Str(5)" )" )
•If a quote is needed inside a text string, it can be escaped with a backslash: "He said \"Hi\" to me".
•All expression elements have a result "type", e.g. numeric or text, and the result type is determined by the expression's final result. For instance, 2+2 is numeric, 2>2 is boolean, "2+2" is text. Some types cannot be mixed, e.g. 2 + "2" is not valid, but numbers and dates can be added or subtracted: {1/1/05} + 30 will add 30 days to the date 1/1/05.
•Any part of an expression not enclosed in quotes is parsed and evaluated, even if the result is not used. For instance in the expression iif( 2>3, 2+2, 2+4 ), all 3 expressions inside the iif() function are parsed an evaluated, even though the 2+2 expression is ultimately ignored. This is especially important when using functions which do things, like SetFieldValue(). You may not want it to "evaluate" all of them. This is where functions like iifq() come in handy, since the quoted expressions passed to it are just considered text except for the one that's used as the return value.
•There is no limit to the length of an expression or the levels of nesting, e.g. within function calls or parenthesis.
•There is a shorthand that can be used for some data field values, if used on the "Operational" record. For instance: Resv:Resv_Type is equivalent to FieldText( ThisResv(), "Resv_Type"). The shorthand versions are parsed slightly slower but are executed much faster. The Expression Elements dialog or Select Fields dialog will insert the shorthand version automatically when possible.
Expression Variables
You can pass information from one portion of an expression to another through variables. There are two types of variables: Local and Global.
A Global variable will retain its value as long as the program is still running, so it can be referenced later. These are useful for situations where you need to transfer a value from one area to another -- for instance to set a value in a menu that will be needed in a custom dialog later. Use global variables sparingly, since it can be difficult to keep track of them.
A Local variable only exists as long as you're within the same Expression execution cycle where it's created, or at any level of CallScript, Macro, Eval, etc. executed within that same Expression. So it's unique to that Expression -- e.g. a local variable created in a Query's Data expression will not exist for any other expression executed separately such as the Query's Filter expression (and thus will also not conflict with local variables by the same name in other Expression spaces).
Local variables are created with the SetVar( ) function -- they do not exist in the current expression's execution space before that. Within the SetVar function the variable name must be given as text, e.g. in quotes, like SetVar ("Index", 1). However when used in an expression, the name is not in quotes -- for instance: ThisListRec(Index). See the Scripts section for more examples.
Global variables are created with the SetVarGlobal() function. The SetVar function can be used subsequently to change the value of the global variable, but this is not recommended (always use SetVarGlobal when possible just to keep it straight).
Note that a variable used in an expression must exist when an expression is parsed, or else a parsing error occurs. Note the difference between these two expressions, which both eventually execute the two expressions contained as arguments:
Eval( SetVar ("Index", 1), ThisListRec(Index))
EvalQ( 'SetVar ("Index", 1)', 'ThisListRec(Index)')
The first expression will result in a parsing error because everything is parsed at once, and the variable "Index" used in ThisListRec() does not exist until execution time.
However the second expression will work because the arguments to EvalQ are only parsed as text initially. During execution, the first quoted expression containing SetVar is parsed and executed, creating the variable, and then the next quoted expression is parsed while the Index variable exists (it's in the same execution space).
Important: Avoid duplicate names between global and local variables! If SetVar is used to create a local variable for the first time, and then SetVarGlobal is used to create a variable by the same name, the value of the global variable cannot be retrieved as long as the local variable is still in context (using a variable in an expression will always use the local variable if it exists, and it will check for global variables only if no local variable exists by that name). Likewise, using SetVar will always set the value of the local variable as long as it's still in context.
Here are the rules for using variables:
•Variables can be of any type. Once the variable is created, its type is set also (to the type of the expression used in the SetVar function).
•Variable names must start with a letter and may contain letters or digits. They are not case-sensitive.
•Global variables will retain their value system-wide, but can be overridden by a local variable created with the same name.
•Local variables are short-lived, generally only for the current expression or expressions evaluated therein.
•Any local variable that exists before a sub-expression is parsed and executed, such as in functions like CallScript, Macro, IIFQ, EvalQ, etc., is copied into the sub-expression and available therein, and its value is also copied back out.
•Any local variable that does not exist before a sub-expression is parsed and executed but is created within the sub-expression will be destroyed before sub-expression returns.
•These rules apply for any depth level of sub-expressions.
Additional Topics:
Advanced Customizations Overview & other topics