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> |

FOREACH <variablename> IN <expression> <statement> |

BREAK |

TRACE <expression> |

GLOBAL <variablename> [ = <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; or, the number of elements in a list/array	string value or array 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
PadLeft	returns the specified string padded to the left to the specified length if the string is less than that length	string value	minimum length to pad to	(optional)character to use for padding
PadRight	returns the specified string padded to the right to the specified length if the string is less than that length	string value	minimum length to pad to	(optional)character to use for padding
MatchRegex	returns whether or not the specified string matches the supplied regular expression	string test value	regular expression (ECMA script)	(optional) true for case-insensitive, false for case-sensitive
ReplaceRegex	returns the result of a regular expression replacement	string value	regular expression match string (ECMA script)	regular expression replacement string	(optional) true for case-insensitive, false for case-sensitive
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
GetProperty	gets the specified property of an object	object value	string property name
GetPropertyNames	gets an array of all of the properties in an object	object value
HasProperty	gets whether or not the specified property exists in an object	object value	string property name
GetListItem	gets the specified array/list item	array value
CurrentRecord	returns the current field values wrapped up as an object

<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

Comments

Comments may be added anywhere in within a formula body using C/C++ style comment notations

VAR @A = 3  //Everything after the two slashes on this line is considered a comment and is not executed
RETURN /*everything between the slash-stars is a comment and not executed*/ "Hello World"

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

FOREACH <variable> IN <expression> <statement>

Iterates through an array and executes a statement for each item in the array

For example:

VAR @CountNoIncome = 0
FOREACH @Student in [Students] BEGIN
	IF GetProperty(@Student, 'NoIncome') = True
   		SET @CountNoIncome = @CountNoIncome + 1
END
RETURN @CountNoIncome

BREAK

Breaks out of the current innermost WHILE or FOREACH loop.

For example:

VAR @NoIncomeExists = FALSE
FOREACH @Student in [Students] BEGIN
	IF GetProperty(@Student, "NoIncome") = True BEGIN
   		SET @NoIncomeExists = TRUE
		BREAK	//break out of FOREACH and stop looking at the rest of the students
	END
END
IF @NoIncomeExists BEGIN
END

TRACE <expression>

Writes the value of <expression> to the trace log. This is useful for debugging a formula. When using the 'Test' button to test a formula, the trace log is displayed along with the results. Trace events are logged to log4net as DEBUG events for all formula evaluations, whether test or production.

For example:

VAR @NoIncomeExists = FALSE
FOREACH @Student in [Students] BEGIN
	TRACE Format("Checking student {0} for NoIncome flag", GetProperty(@Student, "Fullname"))
	IF GetProperty(@Student, "NoIncome") = True
   		SET @NoIncomeExists = TRUE
END

RETURN @NoIncomeExists

GLOBAL <variablename> [ = <expression> ]

Declares a global variable and optionally initializes it to a value. Global variable persist their values within the same formula body over multiple records. They are not shared between different formulas. The initial value is only used to initialize the global variable. It does not reset the value every time the statement executes. Use SET to change or reset the value of a global variable.

For example (running summary operations):

GLOBAL @RowCount = 0 //This does not get reset to 0 every time the formula executes, it only initializes the value to 0 the first time
SET @RowCount = @RowCount + 1
RETURN @RowCount //returns the row number of the record within an export

Or (accessing previous records):

GLOBAL @PreviousStudent = NULL
VAR @Result
IF @PreviousStudent = NULL
	SET @Result = NULL
ELSE
	SET @Result = GetProperty(@PreviousStudent, "Fullname")
SET @PreviousStudent = CurrentRecord()  //CurrentRecord() takes the current field values and places them in a object-value result with properties matching the field names
RETURN @Result	//returns the Fullname field value of the previous student record

Browser not supported