User-Defined Formula Language

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

<binary_operator> :== one of the following operators

<unary_operator> :== one of the following operators

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