MCS Software provides a formula language in used by various imports and exports throughout our suite of applications. This formula language is intended to produce a single output value from available input values. The language is not white-space, line-break, or case sensitive. Using an informal BNF, the language can be described in the following way:
<formula> :: = <statement> [ <statement> [ <statement> ... ] ]
<statement> ::=
RETURN <expression> |
IF <expression> THEN <statement> [ ELSE <statement> ] |
BEGIN <statement> [ <statement> [ <statement> ... ] ] END |
WHILE <expression> <statement> |
VAR <variablename> [ = <expression> ] |
SET <variablename> = <expression> |
TRY <statement> CATCH <statement>
THROW <expression>
<expression> :== <literal> |
<constant> |
<variablename> |
<fieldname> |
<functionname> ( <expression> [ , <expression> [ , <expresion> ... ] ]
<expression> <binary_operator> <expression> |
<unary_operator> <expression> |
( <expression> )
<literal> :== [ - ] [ 0-9*] [ . 0-9* ]
"string literal" |
true |
false |
null
<constant> := @@identifierName
<variablename> :== @identifierName
<fieldname> :== [ fieldName ]
<functionname> :== one of the following functions
| function name | meaning | parameter1 | parameter2 | parameter3 | parameter4 |
|---|---|---|---|---|---|
| IIF | Conditional If, if parameter1 is true then returns parameter2 otherwise returns parameter3 | condition | expression to return if condition is true | expression to return if condition is not true | |
| Coalesce | returns the first non-null parameter | first expression to check if not-null | second expression to check if not-null | (optional) 3rd expression to check | (optional) 4th expression to check (you can have an unlimited number of parameters) |
| NullIf | returns NULL if parameter1 equals parameter 2, otherwise it returns parameter1 | value to return if not equal to parameter2 | value to test parameter1 against | ||
| NullIfError | returns NULL if the evaluation of parameter1 throws an exception, otherwise it returns parameter 1 | expression to evaluate | |||
| Date | returns a new date value from the supplied Gregorian calendar components | year | month | day | |
| Now | returns the date and time right at the time the formula evaluation began (this value does not change when called multiple times with a given formula evaluation context, such that if used on an export all rows would have the same value) | ||||
| AddDays | returns the result of adding a number of days to a date | starting date | number of days to add | ||
| AddMonths | returns the result of adding a number of months to a date | starting date | number of months to add | ||
| AddYears | returns the result of adding a number of years to a date | starting date | number of years to add | ||
| Day | returns the day of the month of a given date | date | |||
| Month | returns the calendar month of a given date | date | |||
| Year | returns the calendar year of a given date | date | |||
| DayOfWeek | returns the day of the week (sunday = 0, monday = 1, ... saturday = 6 ) of a given date | date | |||
| ParseDate | converts a string to a date | string value to parse | (optional) the expected format of the string. For example MM/dd/yyyy | ||
| Len | returns the length of a string | string value | |||
| Left | returns a portion of a string starting from the left | string value | maximum number of characters to return | ||
| Right | returns a portion of a string starting from the right | string value | maximum number of characters to return | ||
| Substring | returns a portion of a string starting at a given position (this function will error if you specify a start and/or length beyond the range of the string) | string value | the starting index (beginning with 0) | (optional) the number of characters to return | |
| Replace | returns a the result of a string search-and-replace operation (case-sensitive) | string value to search | substring to search for within parameter1 | string value with which to replace any occurrences of parameter2 | |
| IndexOf | returns the first occurrence of a search within a string (-1 means not found, >= 0 means the index within the string that the match was found) | string value to search | substring to search for | (optional) the starting position (beginning with 0) to begin searching. | (optional)true for case-sensitive, false for case-insensitive |
| StartsWith | returns true if a string starts with a given substring, false otherwise | string value to check | substring to search for | (optional) true for case-sensitive, false for case-insensitive | |
| EndsWith | returns true if a string ends with a given substring, false otherwise | string value to check | substring to search for | (optional) true for case-sensitive, false for case-insensitive | |
| StringCompare | returns -1 if the parameter1 is alphabetically less than parameter2, 0 if they are equal, and 1 if parameter2 is greater than parameter1 | first string to compare | second string to compare | (optional) true for case-sensitive, false for case-insensitive | |
| ToUpper | returns the result of converting any lower case letters in parameter1 to upper-case letters | string value | |||
| ToLower | returns the result of converting any upper case letters in parameter1 to lower-case letters | string value | |||
| Trim | returns the result of removing any leading or trailing white-space characters from parameter1 | string value | |||
| ParseFirstName | returns the first name component of the supplied full name | full name to parse | |||
| ParseLastName | returns the last name component of the supplied full name | full name to parse | |||
| ParseMiddleName | returns the middle name component of the supplied full name | full name to parse | |||
| ParseSuffix | returns the suffix component of the supplied full name | full name to parse | |||
| Format | returns the result of formatting zero or more supplied values into a string using a standard .net formatting string. Parameter1 is returns with { n } replaced by value-n, and { n : format } replaced by value-n formatted by the specified format. For example Format("{0} + {1:0.00} = {2:0.0}", 1, 2, 3) would return "1 + 2.00 = 3.0" | .net style format string | (optional) value 0 to apply to the format string | (optional) value 1 to apply to the format string | (optional) value 2 to apply to the format string. You can supply an unlimited number of value |
| Round | returns a numeric value rounded to a given number of decimal places. Midpoints are rounded away from 0 (e.g. 2.5 rounds to 3) | numeric value to round | (optional) number of decimal places to round to (0 is the default) | ||
| Truncate | returns the integer portion of a number, discarding any fractional amounts (can be thought of as rounding towards 0) | numeric value | |||
| Ceiling | returns a numeric value rounded up to the first integer greater than or equal to it (can be thought of as rounding towards positive infinite) | numeric value | |||
| Floor | returns a numeric value rounded down to the first integer less than or equal to it (can be thought of as rounding towards negative infinite) | numeric value | |||
| Frac | returns the fractional portion of a number | numeric value | |||
| Abs | returns the absolute value of a number | numeric value |
<binary_operator> :== one of the following operators
| operator | meaning |
|---|---|
| + | if the left-hand operand is a string, then string concatenation, otherwise numeric addition |
| - | subtraction |
| * | multiplication |
| / | division |
| \ | integer division |
| % | modulus division ( the remainder after integer division) |
| | | bit-wise OR |
| & | bit-wise AND |
| ^ | bit-wise XOR |
| OR | logical OR |
| AND | logical AND |
| XOR | logical XOR |
| = | equals |
| <> | not equal |
| > | greater-than |
| >= | greater-than or equal |
| < | less-than |
| <= | less-than or equal |
<unary_operator> :== one of the following operators
| operator | meaning |
|---|---|
| - | negative |
| Not | logical NOT |
| ~ | bit-wise NOT |
Execution
Each statement within a formula is executed until a RETURN statement is encountered. The expression supplied to the first executed RETURN statement is the result of the formula. If no RETURN statement is executed, the result of the formula is NULL. Each statement type is described below:
RETURN <expression>
Stops execution of the formula and returns <expression> as the result
For example:
RETURN "hello world"
IF <expression> THEN <statement1> [ ELSE <statement2> ]
if <expression> is true then execute statement 1. If the optional ELSE portion is present, then statement2 is executed if <expression> is not true
For example:
IF @A > 4 THEN RETURN true; ELSE RETURN false;
BEGIN <statement> [ <statement> [ <statement> ... ] ] END
Combines multiple statements into a single statement. This is useful if you need a conditional execution of more than one statement.
For example:
IF @A > 4 THEN
BEGIN
IF @B < 3 THEN
RETURN "hello"
IF @B < 5 THEN
RETURN "world"
END
ELSE
RETURN "hi"
WHILE <expression> <statement>
Executes a statement in a pre-test loop. If <expression> is true, then statement is executed and the test repeated until <expression> returns false
For example:
WHILE @A > 10 SET @A = @A - 3
VAR <variablename> [ = <expression> ]
Declares a variable and optionally initializes it to a value
For example:
VAR @A = 3
SET <variablename> = <expression>
Sets a previously declared variable to a new value
For example:
SET @A = @A + 4
TRY <statement1> CATCH <statement2>
Execute statement1. If an error occurs while executing statement1, statement1 is immediately aborted and statement2 is executed
For example:
VAR @A = 0 TRY RETURN 4 / @A CATCH RETURN NULL
THROW <expression>
Raises an error / exception
For example:
IF @A = 0 THEN THROW "Unexpected value" ELSE RETURN @A