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 | Returns current system date time upto the ten millionths of a second precision i.e. 06/12/18 6:43:22.6175400 PM | Now() |
| UTCNow | Returns current UTC date time upto the ten millionths of a second precision i.e. 06/12/18 1:13:22.6175400 PM | UTCNow() |
| 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 i.e. "MMddyyyyHHmmssfffffff" or "MMddyyyyhhmmsstt" or "dd MMMM yyyy" etc.
|
Format |
Description |
|
d |
Short date pattern i.e. 06/12/18 For example, Format_date( Date, "d" ) |
|
D |
Long date pattern i.e. Tuesday, June 12, 2018 For example, Format_date( Date, "D" ) |
|
f |
Full date/time pattern (short time) i.e. Tuesday, June 12, 2018 6:43 PM For example, Format_date( Date, "f" ) |
|
F |
Full date/time pattern (long time) i.e. Tuesday, June 12, 2018 6:43:22 PM For example, Format_date( Date, "F" ) |
|
g |
General date/time pattern (short time) i.e. 06/12/18 6:43 PM For example, Format_date( Date, "g" ) |
|
G |
General date/time pattern (long time) i.e. 06/12/18 6:43:22 PM For example, Format_date( Date, "G" ) |
|
s |
Sortable date/time pattern i.e. 2018-06-12T18:43:22 For example, Format_date( Date, "s" ) |
|
u |
Universal sortable date/time pattern i.e. 2018-06-12 18:43:22Z For example, Format_date( Date, "u" ) |
|
U |
Universal full date/time pattern i.e. Tuesday, June 12, 2018 1:13:22 PM For example, Format_date( Date, "U" ) |
|
O,o |
Round-trip date/time pattern i.e. 2018-06-12T18:43:22.6175425 For example, Format_date( Date, "O" ) or Format_date( Date, "o" ) |
|
M,m |
Month/day pattern i.e. 06/12/18 6:43:22 PM -> June 12 For example, Format_date( Date, "M" ) or Format_date( Date, "m" ) |
|
Y,y |
Year month pattern i.e. 06/12/18 6:43:22 PM -> June, 2018 For example, Format_date( Date, "Y" ) or Format_date( Date, "y" ) |
|
t |
Short time pattern i.e. 06/12/18 6:43:22 PM -> 6:43 PM For example, Format_date( Date, "t" ) |
|
T |
Long time pattern i.e. 06/12/18 6:43:22 PM -> 6:43:22 PM For example, Format_date( Date, "T" ) |
|
dd |
The day of the month, from 01 through 31 i.e. 06/12/18 6:43:22 PM -> 12 For example, Format_date( Date, "dd" ) |
|
ddd |
The abbreviated name of the day of the week i.e. 06/12/18 6:43:22 PM -> Tue For example, Format_date( Date, "ddd" ) |
|
dddd |
The full name of the day of the week i.e. 06/12/18 6:43:22 PM -> Tuesday For example, Format_date( Date, "dddd" ) |
|
MM |
The month, from 01 through 12 i.e. 06/12/18 6:43:22 PM -> 06 For example, Format_date( Date, "MM" ) |
|
MMM |
The abbreviated name of the month i.e. 06/12/18 6:43:22 PM -> Jun For example, Format_date( Date, "MMM" ) |
|
MMMM |
The full name of the month i.e. 06/12/18 6:43:22 PM -> June For example, Format_date( Date, "MMMM" ) |
|
yy |
The year, from 00 to 99 i.e. 06/12/18 6:43:22 PM -> 18 For example, Format_date( Date, "yy" ) |
|
yyyy |
The year as a four-digit number i.e. 06/12/18 6:43:22 PM -> 2018 For example, Format_date( Date, "yyyy" ) |
|
hh |
The hour, using a 12-hour clock from 01 to 12 i.e. 06/12/18 6:43:22 PM -> 06 For example, Format_date( Date, "hh" ) |
|
HH |
The hour, using a 24-hour clock from 00 to 23 i.e. 06/12/18 6:43:22 PM -> 18 For example, Format_date( Date, "HH" ) |
|
mm |
The minute, from 00 through 59 i.e. 06/12/18 6:43:22 PM -> 43 For example, Format_date( Date, "mm" ) |
|
ss |
The second, from 00 through 59 i.e. 06/12/18 6:43:22 PM -> 22 For example, Format_date( Date, "ss" ) |
|
tt |
The AM/PM designator i.e. 06/12/18 6:43:22 PM -> PM For example, Format_date( Date, "tt" ) |
|
ff |
The hundredths of a second in a date and time value i.e. 06/12/18 6:43:22.6170000 PM -> 61 For example, Format_date( Date, "ff" ) |
|
fff |
The milliseconds in a date and time value i.e. 06/12/18 6:43:22.6170000 PM -> 617 For example, Format_date( Date, "fff" ) |
|
ffff |
The ten thousandths of a second in a date and time value i.e. 06/12/18 6:43:22.6175000 PM -> 6175 For example, Format_date( Date, "ffff" ) |
|
fffff |
The hundred thousandths of a second in a date and time value i.e. 06/12/18 6:43:22.6175400 PM -> 61754 For example, Format_date( Date, "fffff" ) |
|
ffffff |
The millionths of a second in a date and time value i.e. 06/12/18 6:43:22.6175420 PM -> 617542 For example, Format_date( Date, "ffffff" ) |
|
fffffff |
The ten millionths of a second in a date and time value i.e. 06/12/18 6:43:22.6175425 PM -> 6175425 For example, Format_date( Date, "fffffff" ) |
|
zz |
Hours offset from UTC, with a leading zero for a single-digit value i.e. 06/12/18 6:43:22 PM -> +05 For example, Format_date( Date, "zz" ) |
|
zzz |
Hours and minutes offset from UTC i.e. 06/12/18 6:43:22 PM -> +05:30 For example, Format_date( Date, "zzz" ) |
| DateAdd | Returns a date value containing a date and time value to which a specified time interval has been added. | DateAdd( Interval,Number, DateValue) |
|
Interval |
Description |
|
d |
DateInterval.Day; truncated to integral value i.e. 6/12/2018 6:43:22 PM --> 6/17/2018 6:43:22 PM For example, DateAdd("d",5,Date) |
|
y |
DateInterval.DayOfYear; truncated to integral value i.e. i.e. 6/12/2018 6:43:22 PM --> 6/17/2018 6:43:22 PM For example, DateAdd("y",5,Date) |
|
h |
DateInterval.Hour; rounded to nearest millisecond i.e. 6/12/2018 6:43:22 PM --> 6/12/2018 11:43:22 PM For example, DateAdd("h",5,Date) |
|
n |
DateInterval.Minute; rounded to nearest millisecond i.e. 6/12/2018 6:43:22 PM --> 6/12/2018 6:48:22 PM For example, DateAdd("n",5,Date) |
|
s |
DateInterval.Second; rounded to nearest millisecond i.e. 6/12/2018 6:43:22 PM --> 6/12/2018 6:43:27 PM For example, DateAdd("s",5,Date) |
|
m |
DateInterval.Month ; truncated to integral value i.e. 6/12/2018 6:43:22 PM --> 11/12/2018 6:43:22 PM For example, DateAdd("m",5,Date) |
|
q |
DateInterval.Quarter ; truncated to integral value i.e. 6/12/2018 6:43:22 PM --> 9/12/2019 6:43:22 PM For example, DateAdd("q",5,Date) |
|
w |
DateInterval.Weekday; truncated to integral value i.e. 6/12/2018 6:43:22 PM --> 6/17/2018 6:43:22 PM For example, DateAdd("w",5,Date) |
|
ww |
DateInterval.WeekOfYear; truncated to integral value i.e. 6/12/2018 6:43:22 PM --> 7/17/2018 6:43:22 PM For example, DateAdd("ww",5,Date) |
|
yyyy |
DateInterval.Year; truncated to integral value i.e. 6/12/2018 6:43:22 PM --> 6/12/2023 6:43:22 PM For example, DateAdd("yyyy",5,Date) |
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