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.
On this page
Literals
(Copied from JEXL Syntax)
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 |
---|---|
empty | Returns true if the expression following is either:
empty(var1) |
size | Returns the information about the expression:
size("Hello") returns 5. |
Math functions
The table grid editor expression language also exposes all math functions such as
abs |
abs |
abs |
abs |
acos |
asin |
atan |
atan2 |
cbrt |
ceil |
copySign |
copySign |
cos |
cosh |
exp |
expm1 |
floor |
getExponent |
getExponent |
hypot |
IEEEremainder |
log |
log10 |
log1p |
max |
max |
max |
max |
min |
min |
min |
min |
nextAfter |
nextAfter |
nextUp |
nextUp |
pow |
random |
rint |
round |
round |
scalb |
scalb |
signum |
signum |
sin |
sinh |
sqrt |
tan |
tanh |
toDegrees |
toRadians |
ulp |
ulp |
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 |
|
compareTo |
compareToIgnoreCase |
concat |
contains |
contentEquals |
contentEquals |
copyValueOf |
copyValueOf |
endsWith |
equals |
equalsIgnoreCase |
format |
format |
getBytes |
getBytes |
getBytes |
getChars |
hashCode |
indexOf |
indexOf |
indexOf |
indexOf |
intern |
lastIndexOf |
lastIndexOf |
lastIndexOf |
lastIndexOf |
length |
matches |
offsetByCodePoints |
regionMatches |
regionMatches |
replace |
replace |
replaceAll |
replaceFirst |
split |
split |
startsWith |
startsWith |
subSequence |
substring |
substring |
toCharArray |
toLowerCase |
toLowerCase |
toString |
toUpperCase |
toUpperCase |
trim |
valueOf |
valueOf |
valueOf |
valueOf |
valueOf |
valueOf |
valueOf |
valueOf |
valueOf |
Query Functions
Query functions allow to perform SQL queries to the registered datasources using namespace "queries" like that: "queries:value('jira', 'select pname from issuetype')".
value(String dataSourceName, String query) Returns the result of execution of the query against the datasource with the specified name as a plain value of the first column of the first row of the returned result set |
list(String dataSourceName, String query) Returns the result of execution of the query against the datasource with the specified name as a list of values in the first column of the returned result set |
Please check examples of usage here.
Operators
Operator | Description |
---|---|
Boolean and | The usual && operator can be used as well as the word and, e.g. cond1 and cond2 and cond1 && cond2 are equivalent |
Boolean or | The usual || operator can be used as well as the word or, e.g. cond1 or cond2 and cond1 || cond2 are equivalent |
Boolean not | The usual ! operator can be used as well as the word not, e.g. !cond1 and not cond1 are equivalent |
Bitwise and | The usual & operator is used, e.g. 33 & 4 , 0010 0001 & 0000 0100 = 0. |
Bitwise or | The usual | operator is used, e.g. 33 | 4 , 0010 0001 | 0000 0100 = 0010 0101 = 37. |
Bitwise xor | The usual ^ operator is used, e.g. 33 ^ 4 , 0010 0001 ^ 0000 0100 = 0010 0100 = 37. |
Bitwise complement | The usual ~ operator is used, e.g. ~33 , ~0010 0001 = 1101 1110 = -34. |
Ternary conditional ?: | The usual ternary conditional operator condition ? if_true : if_false operator can be used as well as the abbreviation value ?: if_false which returns the value if its evaluation is defined, non-null and non-false, e.g. val1 ? val1 : val2 and val1 ?: val2 are equivalent. NOTE: The condition will evaluate to false when it refers to an undefined variable or null for all JexlEngine flag combinations. This allows explicit syntactic leniency and treats the condition 'if undefined or null or false' the same way in all cases.
Following construct means - if iSubtotal is set, return iSubtotal else return 0 {iSubtotal} ?: 0
|
Equality | The usual == operator can be used as well as the abbreviation eq. For example val1 == val2 and val1 eq val2 are equivalent.
|
Inequality | The usual != operator can be used as well as the abbreviation ne. For example val1 != val2 and val1 ne val2 are equivalent. |
Less Than | The usual < operator can be used as well as the abbreviation lt. For example val1 < val2 and val1 lt val2 are equivalent. |
Less Than Or Equal To | The usual <= operator can be used as well as the abbreviation le. For example val1 <= val2 and val1 le val2 are equivalent. |
Greater Than | The usual > operator can be used as well as the abbreviation gt. For example val1 > val2 and val1 gt val2 are equivalent. |
Greater Than Or Equal To | The usual >= operator can be used as well as the abbreviation ge. For example val1 >= val2 and val1 ge val2 are equivalent. |
In or Match=~ | The syntactically Perl inspired =~ operator can be used to check that a string matches a regular expression (expressed either a Java String or a java.util.regex.Pattern). For example "abcdef" =~ "abc.* returns true. It also checks whether any collection, set or map (on keys) contains a value or not; in that case, it behaves as an "in" operator. Note that it also applies to arrays as well as "duck-typed" collection, ie classes exposing a "contains" method. "a" =~ ["a","b","c","d","e",f"] returns true. |
Not-In or Not-Match!~ | The syntactically Perl inspired !~ operator can be used to check that a string does not match a regular expression (expressed either a Java String or a java.util.regex.Pattern). For example "abcdef" !~ "abc.* returnsfalse. It also checks whether any collection, set or map (on keys) does not contain a value; in that case, it behaves as "not in" operator. Note that it also applies to arrays as well as "duck-typed" collection, ie classes exposing a "contains" method. "a" !~ ["a","b","c","d","e",f"] returns true. |
Addition | The usual + operator is used. For example val1 + val2 |
Subtraction | The usual - operator is used. For example val1 - val2 |
Multiplication | The usual * operator is used. For example val1 * val2 |
Division | The usual / operator is used, or one can use the div operator. For example val1 / val2 or val1 div val2 |
Modulus (or remainder) | The % operator is used. An alternative is the mod operator. For example 5 mod 2 gives 1 and is equivalent to 5 % 2 |
Negation | The unary - operator is used. For example -12 |
Conditionals
Operator | Description |
---|---|
if | Classic, if/else statement, e.g. if ((x * 2) == 5) { |
Notes
jqGrid permits to put numbers in exponential form into the number fields (i.e. 1.0E+3), and JEXL permits values such as 1.0E+3 to be input, and assumes them to be decimals so 1.0E+3 * 2 = 2000