Formula - formal syntax specifications

Formula - formal syntax specifications

Introduction

The table grid editor allows for column values to be calculated from other columns.  This functionality is based on the Apache Commons JEXL which is an open source Java Expression Language processor.

Additionally, the table grid editor adds 

  • Expression validation
    When an error is created in the configuration the administrator gets warned.

  • Loop detection and avoidance
    Formula's can reuse the values from other calculated columns. As this might create a loop (Column A depends on Column B and the way around), a configuration error is raised.



Check Formula - Examples for recipes to build formulas.

 

Literals

(Copied from JEXL Syntax)

Item

Description

Item

Description

Integer Literals

1 or more digits from 0 to 9, eg 42.

Float Literals

1 or more digits from 0 to 9, followed by a decimal point and then one or more digits from 0 to 9, optionally followed by f or F, eg 42.0 or 42.0f.

Long Literals

1 or more digits from 0 to 9 suffixed with l or L , eg 42l.

Double Literals

1 or more digits from 0 to 9, followed by a decimal point and then one or more digits from 0 to 9 suffixed with d or D , eg 42.0d.

Big Integer Literals

1 or more digits from 0 to 9 suffixed with b or B , eg 42B.

Big Decimal Literals

1 or more digits from 0 to 9, followed by a decimal point and then one or more digits from 0 to 9 suffixed with h or H (for Huge ala OGNL)) , eg 42.0H.

Natural literals - octal and hex support

Natural numbers (i.e. Integer, Long, BigInteger) can also be expressed as octal or hexadecimal using the same format as Java. i.e. prefix the number with 0 for octal, and prefix with 0x or 0X for hexadecimal. For example010 or 0x10.

Real literals - exponent support

Real numbers (i.e. Float, Double, BigDecimal) can also be expressed using standard Java exponent notation. i.e. suffix the number with e or E followed by the sign + or - followed by one or more decimal digits. For example42.0E-1D or 42.0E+3B.

String literals

Can start and end with either ' or " delimiters, e.g.

"Hello world"

and

'Hello world'

are equivalent.

The escape character is \ (backslash); it only escapes the string delimiter

Boolean literals

The literals true and false can be used, e.g.

val1 == true

Null literal

The null value is represented as in java using the literal null, e.g.

val1 == null

Functions

You can use functions on expressions such as

col.xyz.formula = math:round({price} * {discount} * {tax})

General functions

Function

Description

Function

Description

empty

Returns true if the expression following is either:

  1. null

  2. An empty string

  3. An array of length zero

  4. A collection of size zero

  5. An empty map

empty(var1)

size

Returns the information about the expression:

  1. Length of an array

  2. Size of a List

  3. Size of a Map

  4. Size of a Set

  5. Length of a string

size("Hello")

returns 5.

Math functions

The table grid editor expression language also exposes all math functions such as

abs(double a) 
          Returns the absolute value of a double value.

abs(float a) 
          Returns the absolute value of a float value.

abs(int a) 
          Returns the absolute value of an int value.

abs(long a) 
          Returns the absolute value of a long value.

acos(double a) 
          Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi.

asin(double a) 
          Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2.

atan(double a) 
          Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2.

atan2(double y, double x) 
          Returns the angle theta from the conversion of rectangular coordinates (xy) to polar coordinates (r, theta).

cbrt(double a) 
          Returns the cube root of a double value.

ceil(double a) 
          Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer.

copySign(double magnitude, double sign) 
          Returns the first floating-point argument with the sign of the second floating-point argument.

copySign(float magnitude, float sign) 
          Returns the first floating-point argument with the sign of the second floating-point argument.

cos(double a) 
          Returns the trigonometric cosine of an angle.

cosh(double x) 
          Returns the hyperbolic cosine of a double value.

exp(double a) 
          Returns Euler's number e raised to the power of a double value.

expm1(double x) 
          Returns ex -1.

floor(double a) 
          Returns the largest (closest to positive infinity) double value that is less than or equal to the argument and is equal to a mathematical integer.

getExponent(double d) 
          Returns the unbiased exponent used in the representation of a double.

getExponent(float f) 
          Returns the unbiased exponent used in the representation of a float.

hypot(double x, double y) 
          Returns sqrt(x2 +y2) without intermediate overflow or underflow.

IEEEremainder(double f1, double f2) 
          Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard.

log(double a) 
          Returns the natural logarithm (base e) of a double value.

log10(double a) 
          Returns the base 10 logarithm of a double value.

log1p(double x) 
          Returns the natural logarithm of the sum of the argument and 1.

max(double a, double b) 
          Returns the greater of two double values.

max(float a, float b) 
          Returns the greater of two float values.

max(int a, int b) 
          Returns the greater of two int values.

max(long a, long b) 
          Returns the greater of two long values.

min(double a, double b) 
          Returns the smaller of two double values.

min(float a, float b) 
          Returns the smaller of two float values.

min(int a, int b) 
          Returns the smaller of two int values.

min(long a, long b) 
          Returns the smaller of two long values.

nextAfter(double start, double direction) 
          Returns the floating-point number adjacent to the first argument in the direction of the second argument.

nextAfter(float start, double direction) 
          Returns the floating-point number adjacent to the first argument in the direction of the second argument.

nextUp(double d) 
          Returns the floating-point value adjacent to d in the direction of positive infinity.

nextUp(float f) 
          Returns the floating-point value adjacent to f in the direction of positive infinity.

pow(double a, double b) 
          Returns the value of the first argument raised to the power of the second argument.

random() 
          Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0.

rint(double a) 
          Returns the double value that is closest in value to the argument and is equal to a mathematical integer.

round(double a) 
          Returns the closest long to the argument.

round(float a) 
          Returns the closest int to the argument.

scalb(double d, int scaleFactor) 
          Return d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set.

scalb(float f, int scaleFactor) 
          Return f × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the float value set.

signum(double d) 
          Returns the signum function of the argument; zero if the argument is zero, 1.0 if the argument is greater than zero, -1.0 if the argument is less than zero.

signum(float f) 
          Returns the signum function of the argument; zero if the argument is zero, 1.0f if the argument is greater than zero, -1.0f if the argument is less than zero.

sin(double a) 
          Returns the trigonometric sine of an angle.

sinh(double x) 
          Returns the hyperbolic sine of a double value.

sqrt(double a) 
          Returns the correctly rounded positive square root of a double value.

tan(double a) 
          Returns the trigonometric tangent of an angle.

tanh(double x) 
          Returns the hyperbolic tangent of a double value.

toDegrees(double angrad) 
          Converts an angle measured in radians to an approximately equivalent angle measured in degrees.

toRadians(double angdeg) 
          Converts an angle measured in degrees to an approximately equivalent angle measured in radians.

ulp(double d) 
          Returns the size of an ulp of the argument.

ulp(float f) 
          Returns the size of an ulp of the argument.

 

String functions

These are the string functions as provided by the java.lang.String class.

  • When the containing column is of type 'string', the expression value will be truncated to 254 chars.

  • When applying a string function on a variable, you need to use the toString function to ensure that the variable is cast to a string.
    A variable can contain a number.
    For instance {product.name} .toString().toUpperCase() is required as {product.name} could resolve to a number

charAt(int index) 
          Returns the char value at the specified index.

 

compareTo(String anotherString) 
          Compares two strings lexicographically.

compareToIgnoreCase(String str) 
          Compares two strings lexicographically, ignoring case differences.

concat(String str) 
          Concatenates the specified string to the end of this string.

contains(CharSequence s) 
          Returns true if and only if this string contains the specified sequence of char values.

contentEquals(CharSequence cs) 
          Compares this string to the specified CharSequence.

contentEquals(StringBuffer sb) 
          Compares this string to the specified StringBuffer.

copyValueOf(char[] data) 
          Returns a String that represents the character sequence in the array specified.

copyValueOf(char[] data, int offset, int count) 
          Returns a String that represents the character sequence in the array specified.

endsWith(String suffix) 
          Tests if this string ends with the specified suffix.

equals(Object anObject) 
          Compares this string to the specified object.

equalsIgnoreCase(String anotherString) 
          Compares this String to another String, ignoring case considerations.

format(Locale l, String format, Object... args) 
          Returns a formatted string using the specified locale, format string, and arguments.

format(String format, Object... args) 
          Returns a formatted string using the specified format string and arguments.

getBytes() 
          Encodes this String into a sequence of bytes using the platform's default charset, storing the result into a new byte array.

getBytes(Charset charset) 
          Encodes this String into a sequence of bytes using the given charset, storing the result into a new byte array.

getBytes(String charsetName) 
          Encodes this String into a sequence of bytes using the named charset, storing the result into a new byte array.

getChars(int srcBegin, int srcEnd, char[] dst, int dstBegin) 
          Copies characters from this string into the destination character array.

hashCode() 
          Returns a hash code for this string.

indexOf(int ch) 
          Returns the index within this string of the first occurrence of the specified character.

indexOf(int ch, int fromIndex) 
          Returns the index within this string of the first occurrence of the specified character, starting the search at the specified index.

indexOf(String str)           Returns the index within this string of the first occurrence of the specified substring.