General Refine Expression Language

Basics

GREL (General Refine Expression Language) is designed to resemble Javascript. Formulas use variables and depend on data types to do things like string manipulation or mathematical calculations:

Example	Output
value + " (approved)"	Concatenate two strings; whatever is in the cell gets converted to a string first
value + 2.239	Add 2.239 to the existing value (if a number); append text "2.239" to the end of the string otherwise
value.trim().length()	Trim leading and trailing whitespace of the cell value and then output the length of the result
value.substring(7, 10)	Output the substring of the value from character index 7, 8, and 9 (excluding character index 10)
value.substring(13)	Output the substring from index 13 to the end of the string

Note that the operator for string concatenation is + (not “&” as is used in Excel).

Evaluating conditions uses symbols such as <, >, *, /, etc. To check whether two objects are equal, use two equal signs (value=="true").

See the GREL functions page for a thorough reference on each function and its inputs and outputs. Read on below for more about the general nature of GREL expressions.

Operators

Arithmetic Operators

Refer GREL functions page for details on Division Operator.

Modulus

When using the % operator, if both operands are numbers such as 1 % 2 the result will be a whole number. However, if either or both of the operands are floating-point numbers like 1.0 % 2 they will be promoted to floating point and the result will also be in floating-point format. It's important to note that the % operator may not behave as expected with floating-point numbers due to precision issues.

Multiplication

The behavior of the * operator is nuanced based on the data types of the operands. When both operands are integers such as 1 * 2, the result is an integer. Conversely, if either or both operands are floating-point numbers the result becomes a floating-point number. You can use simple evaluations such as 3.5 * 2

Relational Operators

== and != operators are used to assess equality and inequality. For instance, "a" == "b" returns false and "a" != "b" returns true. When applied to integers, 5 == 5 returns true, while 3 != 3 returns false.

The < operator checks if the left operand is less than the right operand, while <= checks if it's less than or equal to. Similarly > verifies if the left operand is greater than the right and >= checks if it's greater than or equal to. These comparison operators apply to numbers, strings and dates.

References

• String Concatenation
• Logical Functions

Syntax

In GREL, functions can use either of these two forms:

• functionName(arg0, arg1, ...)
• arg0.functionName(arg1, ...)

The second form is a shorthand to make expressions easier to read. It simply pulls the first argument out and appends it to the front of the function, with a dot:

Dot notation	Full notation
value.trim().length()	length(trim(value))
value.substring(7, 10)	substring(value, 7, 10)

So, in the dot shorthand, the functions occur from left to right in the order of calling, rather than in the reverse order with parentheses. This allows you to string together multiple functions in a readable order.

The dot notation can also be used to access the member fields of variables. For referring to column names that contain spaces (anything not a continuous string), use square brackets instead of dot notation:

Example	Description
cells.FirstName	Access the cell in the column named “FirstName” of the current row
cells["First Name"]	Access the cell in the column called “First Name” of the current row

Square brackets can also be used to get substrings and sub-arrays, and single items from arrays:

Example	Description
value[1,3]	A substring of value, starting from character 1 up to but excluding character 3
"internationalization"[1,-2]	Will return “nternationalizati” (negative indexes are counted from the end)
row.columnNames[5]	Will return the name of the fifth column

Any function that outputs an array can use square brackets to select only one part of the array to output as a string (remember that the index of the items in an array starts with 0).

For example, partition() would normally output an array of three items: the part before your chosen fragment, the fragment you've identified, and the part after. Selecting only the third part with "internationalization".partition("nation")[2] will output “alization” (and so will [-1], indicating the final item in the array).

Controls

GREL offers controls to support branching and looping (that is, “if” and “for” functions), but unlike functions, their arguments don't all get evaluated before they get run. A control can decide which part of the code to execute and can affect the environment bindings. Functions, on the other hand, can't do either. Each control decides which of their arguments to evaluate to value, and how.

Please note that the GREL control names are case-sensitive: for example, the isError() control can't be called with iserror().

if(e, eTrue, eFalse)

Expression e is evaluated to a value. If that value is true, then expression eTrue is evaluated and the result is the value of the whole if() expression. Otherwise, expression eFalse is evaluated and that result is the value.

Examples:

Example expression	Result
if("internationalization".length() > 10, "big string", "small string")	“big string”
if(mod(37, 2) == 0, "even", "odd")	“odd”

Nested if (switch case) example:

if(value == 'Place', 'http://www.example.com/Location',

if(value == 'Person', 'http://www.example.com/Agent',

if(value == 'Book', 'http://www.example.com/Publication',

null)))

with(e1, variable v, e2)

Evaluates expression e1 and binds its value to variable v. Then evaluates expression e2 and returns that result.

Example expression	Result
with("european union".split(" "), a, a.length())	2
with("european union".split(" "), a, forEach(a, v, v.length()))	[ 8, 5 ]
with("european union".split(" "), a, forEach(a, v, v.length()).sum() / a.length())	6.5

filter(e1, v, e test)

Evaluates expression e1 to an array. Then for each array element, binds its value to variable v, evaluates expression test - which should return a boolean. If the boolean is true, pushes v onto the result array.

Expression	Result
filter([ 3, 4, 8, 7, 9 ], v, mod(v, 2) == 1)	[ 3, 7, 9 ]

forEach(e1, v, e2)

Evaluates expression e1 to an array. Then for each array element, binds its value to variable v, evaluates expression e2, and pushes the result onto the result array. When e1 is a JSON object, forEach iterates over its keys.

Expression	Result
forEach([ 3, 4, 8, 7, 9 ], v, mod(v, 2))	[ 1, 0, 0, 1, 1 ]

forEachIndex(e1, i, v, e2)

Evaluates expression e1 to an array. Then for each array element, binds its index to variable i and its value to variable v, evaluates expression e2, and pushes the result onto the result array.

Expression	Result
forEachIndex([ "anne", "ben", "cindy" ], i, v, (i + 1) + ". " + v).join(", ")	1. anne, 2. ben, 3. cindy

forRange(n from, n to, n step, v, e)

Iterates over the variable v starting at from, incrementing by the value of step each time while less than to. At each iteration, evaluates expression e, and pushes the result onto the result array.

forNonBlank(e, v, eNonBlank, eBlank)

Evaluates expression e. If it is non-blank, forNonBlank() binds its value to variable v, evaluates expression eNonBlank and returns the result. Otherwise (if e evaluates to blank), forNonBlank() evaluates expression eBlank and returns that result instead.

Unlike other GREL functions beginning with “for,” forNonBlank() is not iterative. forNonBlank() essentially offers a shorter syntax to achieving the same outcome by using the isNonBlank() function within an “if” statement.

isBlank(e), isNonBlank(e), isNull(e), isNotNull(e), isNumeric(e), isError(e)

Evaluates the expression e, and returns a boolean based on the named evaluation.

Examples:

Expression	Result
isBlank("abc")	false
isNonBlank("abc")	true
isNull("abc")	false
isNotNull("abc")	true
isNumeric(2)	true
isError(1)	false
isError("abc")	false
isError(1 / 0)	true

Remember that these are controls and not functions: you can’t use dot notation (for example, the format e.isX() will not work).

Constants

Name	Meaning
true	The boolean constant true
false	The boolean constant false
PI	From Java's Math.PI, the value of pi (that is, 3.1415...)