Text Functions
| Function | Output | Syntax |
| Replace | String, after replacing the old value with the new value in the BaseString. | Replace( BaseString, OldValue, NewValue ) |
| Substr | Substring of the input string starting from a specified position with a specified length. | Substr( String, From, Length ) |
| Split | String array of substrings from the input string which is delimited by a specified Delimiter. | Split( String, Delimiter ) |
| Trim | String, which results from the leading and trailing spaces being omitted from the input string. |
Trim( String ) |
| LeftTrim | String, which results from the leading spaces being omitted from the input string. |
LeftTrim( String ) |
| RightTrim | String, which results from the trailing spaces being omitted from the input string. | RightTrim( String ) |
| PadLeft | String, after adding a pad character added to the left of the input string. | PadLeft( String, WantedLength, PadChar ) |
| Format | String, formatted according to instructions contained in the style string. | Format( Expression, Style* ) |
| Lower | String, converted to lowercase. | Lower( String ) |
| Upper | String, converted to uppercase. | Upper( String ) |
| WCase | String, converted to wordcase, where the first character of the input string is in uppercase and all the others are in lowercase. | WCase( String ) |
| Index | Index position of the search text within the input string. | Index( String, SearchText, Delimiter ) |
| Len | Length of the the input string. | Len( String ) |
| Chr | The character associated with the specified integer character code. | Chr( IntValue ) |
| ChCR | A carriage-return character. | ChCR() |
| ChLF | A linefeed character. | ChLF() |
| ChCRLF | A carriage-return character combined with a linefeed character. | ChCRLF() |
| If | TrueValue object if the boolean condition is true, else FalseValue object. | If( Boolean_Condition, TrueValue, FalseValue ) |
| Inlist | True if search text is present in the list string delimited by a specified delimiter, else False. | Inlist( SearchText, ListString, Delimiter ) |
*Style argument for the Format function may be set as:
|
Style |
Description |
|
Currency |
Displays number with thousand separator, if appropriate; displays two digits to the right of the decimal separator. Output is based on system locale settings. For example, Format(1234567, "Currency") returns $1,234,567.00. |
|
Fixed |
Displays at least one digit to the left and two digits to the right of the decimal separator. For example, Format(1234567, "Fixed") returns 1234567.00. |
|
Standard |
Displays number with thousand separator, at least one digit to the left and two digits to the right of the decimal separator. For example, Format(1234567, "Standard") returns 1,234,567.00. |
|
Percent |
Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator. For example, Format(0.4744, "Percent") returns 47.44%. |
|
Scientific |
Uses standard scientific notation, providing two significant digits. For example, Format(1234567, "Scientific") returns 1.23E+06. |
|
Yes/No |
Displays No if number is 0; otherwise, displays Yes. For example, Format(0, "Yes/No") returns No. |
|
True/False |
Displays False if number is 0; otherwise, displays True. For example, Format(1, "True/False") returns True. |
|
On/Off |
Displays Off if number is 0; otherwise, displays On. For example, Format(1, "On/Off") returns On. |
Numeric Functions
| Function | Output | Syntax |
| Int | Converts the specified object to an Integer value. | Int( Object ) |
| Dec | Converts the specified object to a floating point decimal value. | Dec( Object ) |
| Rnd | A random number. | Rnd() |
| Round | A double precision floating-point value to the nearest integral value. | Round( Value ) |
| Abs | Unsigned absolute value of the specified object. | Abs( Value ) |
| Mod | The remainder after division of the two numbers. | Mod( Number1, Number2 ) |
| Min | Minimum of all the specified numbers. | Min( Number1, Number2, [...] ) |
| Max | Maximum of all the specified numbers. | Max( Number1, Number2, [...] ) |
| Money | Currency format of the specified value. | Money( Value ) |
Date Functions
| Function | Output | Syntax |
| Today | Date value containing the current date. | Today() |
| Now | Date value containing the current date and time. | Now() |
| Date | Date value created from the specified year, month and day. | Date( Year, Month, Day ) |
| Year | Year component of the specified date. | Year( Date ) |
| Month | Month component of the specified date. | Month( Date ) |
| Day | Day of the month represented by the specified date. | Day( Date ) |
| WeekDay | Day of the week represented by the specified date. | WeekDay( Date ) |
| Long_date | Long date string of the specified date (eg: 1st January 2016). | Long_date( Date ) |
| Format_date | Formatted date string of the specified date as per the specified format string. | Format_date( Date, Format* ) |
*Format argument for the Format_date function may be set as one or combination of:
|
Format |
Description |
|
w |
Displays day of the week, i.e., "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday". For example, Format_date( Date, "w" ) |
|
x |
Displays the short form of the day of the week, i.e., "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" For example, Format_date( Date, "x" ) |
|
d |
Displays the day of the month with two chars as "st", "nd", "rd" or "th" as per the day value. For example, Format_date( "1/1/2016", "d" ) returns 1st. |
|
e |
Displays the day of the month. For example, Format_date( "1/1/2016", "e" ) returns 1. |
|
f |
Displays the day of the month in two digits format. For example, Format_date( "1/1/2016", "f" ) returns 01. |
|
m |
Displays the month of the year, i.e., "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December". For example, Format_date( "1/1/2016", "m" ) returns January. |
|
n |
Displays the month of the year in digit format. For example, Format_date( "1/1/2016", "n" ) returns 1. |
|
o |
Displays the month of the year in two digits format. For example, Format_date( "1/1/2016", "o" ) returns 01. |
|
p |
Displays short form of the month of the year, i.e., "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec". For example, Format_date( "1/1/2016", "m" ) returns Jan. |
|
y |
Displays the year component of the date. For example, Format_date( "1/1/2016", "y" ) returns 2016. |
|
z |
Displays the year component of the date in two digits format. For example, Format_date( "1/1/2016", "z" ) returns 16. |
|
d m y |
A combination of day month and year. For example, Format_date( "1/1/2016", "d m y" ) returns 1st January 2016. |
User Functions
| Function | Output | Syntax |
| UserEmail | Email address of the selected user. | UserEmail( USERS_FIELD ) |
| UserCellPhone | Cell phone number of the selected user. | UserCellPhone( USERS_FIELD ) |
| UserHomePhone | Home phone number of the selected user. | UserHomePhone( USERS_FIELD ) |
| UserWorkPhone | Work phone number of the selected user. | UserWorkPhone( USERS_FIELD ) |
Here, USERS_FIELD is the field name of a Users type field. For the above four system functions the supported function argument can be a field of type Assignee or Users.
Math Functions
| Function | Output | Syntax |
| Sqrt | Square root of the specified number. | Sqrt( Number ) |
| Power | Specified number raised to the specified power. | Power( Number1, Number2 ) |
| Pi | The constant value of the ratio of the circumference of a circle to its diameter. | Pi() |
| Sin | The sine of the specified value. | Sin( Value ) |
| Cos | The cosine of the specified value. | Cos( Value ) |
Table Column Aggregate Functions
You can use particular table column’s aggregate value outside the definition of the table field as a part of calculated expression in following way:
| Function | Output | Syntax |
| Sum | Sum of all row specific values given for selected column | Sum( ColumnName ) |
| Average | Average of all row specific values given for selected column | Average( ColumnName ) |
| Minimum | Minimum of all row specific values given for selected column | Minimum( ColumnName ) |
| Maximum | Maximum of all row specific values given for selected column | Maximum( ColumnName ) |
| Count | Count of rows containing value for selected column | Count( ColumnName ) |
| First | First occurrence of all row specific values given for selected column | First( ColumnName ) |
| Last | Last occurrence of all row specific values given for selected column | Last( ColumnName ) |
Note: Only non-empty value(s) are considered while calculating table column aggregate value(s).
Have Questions?
If you have further questions, please feel free to contact us and we will be happy to assist you.
0 Comments