Interface FormulaFunction
DataSourceField formula functions
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. Note 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(y,x) | Returns the angle from the positive x-axis to the ray from the origin through (x, y), in radians from -PI to PI, inclusive. Counterclockwise angles are positive; clockwise angles are negative. The y-coordinate is passed first. | 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. start is 1-based: position 1 is the first character. | Y | N [available as substr()] | Y | Y |
substr(val,start,length) | Returns a substring from a value. start is 1-based: position 1 is the first character. | 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 |
Date-part functions and timezones
When evaluated on the client, the MathFunction functionsyear(), month(), day(), hour(), minute() and second() use the native JavaScript Date getters: getFullYear(), getMonth() + 1, getDate(), getHours(), getMinutes() and getSeconds(), respectively. For datetime values, these getters use the browser's local timezone, including the daylight saving offset applicable to the value's date. Calling String does not change these getters or the results of these functions. For example, hour() applied to a datetime representing 2012-06-28T15:15:00Z returns 15 in a browser using UTC, but 9 in a browser using America/Denver (UTC-6 on that date). A timezone difference can also change the year, month or day, and fractional-hour offsets can change the minute.
For SQL-backed DataSources, date-part formulas follow the database's rules for the input SQL type and any applicable session timezone settings. A timezone-free SQL datetime supplies its stored calendar and clock components; it is not automatically interpreted in the browser's timezone. Consequently, a formula can produce different results when evaluated in SQL and against the corresponding datetime in a clientOnly DataSource. Applications requiring matching results must explicitly use a common timezone for both evaluations. See DateFormatAndStorage for datetime storage and display behavior, and for the distinction between datetimes and timezone-independent logical dates and times.