public interface FormulaFunction
DataSourceField formulas may make
use of functions to derive values from other fields in the record being accessed. For an
SQL-backed dataSource, the specified dataSourceField.formula will be included in
the SQL statements generated by the Smart GWT server. These can make use of the scalar
functions supported by the database engine. See the documentation for your database for a full
list of available functions.
The following functions are available to provide equivalent
functionality for clientOnly
dataSources. Nte that the database support information is for reference only - we recommend
consulting your database's official documentation for definitive details of the functions
available to you.
| Function | Description | Database support | |||
|---|---|---|---|---|---|
| MySQL / MariaDB | PostgreSQL | SQL Server | SQLite | ||
| Math / Numeric functions | |||||
round(val1, val2) | Round a numeric value up or down to the specified precision | Y | Y | N | N |
ceil(val1) | Round a numeric value up to a whole number | Y | Y | N [available as ceiling()] | Y |
ceiling(val1) | Round a numeric value up to a whole number | Y | Y | Y | N [available as
ceil()] |
floor(val1) | Round a numeric value down to a whole number | Y | Y | Y | Y |
mod(val1,val2) | Modulus operator, also available
asval1 % val2 | Y | Y | N [use val1 %
val2 instead] | N [use val1 % val2 instead] |
greatest(val1,val2,...) | Maximum of a series of numeric values | Y | Y | N | N |
least(val1,val2,...) | Minimum of a series of numeric values | Y | Y | N | N |
sin(val1) |
Returns the sine of the number | Y | Y | Y | Y |
cos(val1) | Returns the cosine of the number | Y | Y | Y | Y |
tan(val1) | Returns the tangent of the number | Y | Y | Y | Y |
log(value) or log(base, value) | Returns the log of the
numeric value. If one argument is passed, this will be the natural log. If two arguments are
passed returns the log in the specified base. | Y | Differs: In PostgreSQL if
log() is passed a single value it will return the base 10 log rather than natural log. If passed 2 values, behavior matches the client. | Differs: In SQL Server if log() is
passed two arguments, the first argument is the value and the second is the base If passed 1 value, behavior matches the client. | Differs: In SQLite if log() is passed a
single value it will return the base 10 log rather than the natural log. If passed 2 values, behavior matches the client. |
ln(val1) |
Returns the natural log of the numeric value | Y | Y | Y | Y |
log10(val1) | Returns the base-10 log of the numeric value | Y | Y | Y | Y |
exp(val1) | Returns exponent of the numeric value | Y | Y | Y | Y |
abs(val1) | Returns the absolute value of the numeric value | Y | Y | Y | Y |
power(val1,val2) | Returns val1 raised to the power of val2 | Y | Y | Y | Y |
asin(val1) |
Returns arcsin or inverse sine of the number | Y | Y | Y | Y |
acos(val1) | Returns arccos or inverse cosine of the number | Y | Y | Y | Y |
atan(val1) | Returns arctan or inverse tangent of the number | Y | Y | Y | Y |
atan2(val1,val2) | Returns two argument arctan | Y | Y | Y | Y |
random() | Returns a random float value from 0 through 1, exclusive | N [available as
rand()] | Y | N [available as rand()] |
N [Note: Instead of a float between 0 and 1, SQLite's random function returns pseudo-random integer between -9223372036854775808 and +9223372036854775807] |
rand() | Returns a random float value from 0 through 1, exclusive | Y | N [available as random()] | Y | N |
| String functions | |||||
concat(val1,val2,...) | Join multiple values together as a string | Y | Y | Y | Y |
substring(val,start,length) | Returns a substring from a value | Y | N [available as substr()] | Y | Y |
substr(val,start,length) | Returns a substring from a value | Y | Y | N [available as substring()] |
Y |
trim(value) | Removes leading and trailing space characters from a string | Y | Y | Y | Y |
length(value) | Returns the length of a string value | Y | Y | N [available as len()] | Y |
len(value) | Returns the length of a string value | N
[available as length()] | N [available as
length()] | Y | N [available as
length()] |
replace(value,fromText,toText) | Replaces all occurences of
fromText with toText | Y | Y | Y | Y |