Formula Language Reference

Use these operators and functions when building formulas. All functions are available everywhere that you can include a formula unless otherwise specified.

Type Conversion Functions

Function Description
Format Convert Numeric, Date, Time, and Timestamp values to text using formatting options, user-specific locale, and timezone settings.
ToBoolean Converts a numeric or text value to a boolean.
ToDate Converts a timestamp or text value to a date.
ToNumber Converts a boolean or text value to a numeric.
ToText Returns a text value containing the printed representation of the input value.
ToTimeOfDay Converts a timestamp or text value into a time.
ToTimestamp Converts a date or a date and time value into a timestamp.

Text Functions

Function Description
All Returns true if all the values specified in its list argument exist in the values argument.
Any Returns true if at least one value from the list exists in the values.
Begins Returns true if the text u begins with the text v; otherwise, returns false.
Concat This function returns a string that results from the concatenation, or joining, of two or more string values in an end-to-end manner.
Contains Returns true if text u contains text v; otherwise, returns false.
Ends Returns true if the text u ends with the text v, otherwise returns false.
Guid Returns a unique string in the XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX format, where each X represents a character from the 0123456789ABCDEF character set.
Left Returns the left part of a text value.
Len Returns the number of characters in a text.
List Concatenates all value arguments starting with the second argument, using the first argument as a delimiter between them.
Lower Returns text converted to lowercase.
Mid Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
NotLeft Returns what remains after excluding the left part of a text value.
NotRight Returns what remains after excluding the right part of a text value.
PadLeft Right-aligns the characters in the string by padding them on the left to achieve a specified total length.
PadRight Left-aligns the characters in the string by padding them on the right to achieve a specified total length.
Part Returns the specified part of a text value. The parts are separated by the occurrence of any delimiter character.
Proper Capitalizes the first letter in the text and any other letters in the text that follow any character other than a letter. Converts all other letters to lowercase letters, except for words that are entirely in uppercase, which are considered to be acronyms.
Replace Replaces substrings containing the old text with the new text in the given text.
Right Returns the right part of a text value.
Trim Returns text with leading and trailing white space characters removed.
Upper Returns text converted to uppercase.
URLEncode Encodes a URL string.
URLDecode Converts a URL-encoded string into a decoded string.
URLParam Extracts and decodes the value of a parameter from a URL’s query string.

Date Functions

Function Description
AdjustMonth Returns the date that is a number of months after the given date, with the same day number.
AdjustYear Returns the date that is a number of years after the given date, with the same month and day.
Date Creates a date from a given year, month, and day.
Day Returns the day of the month of the given date.
DayOfWeek Returns the number of days by which the given date follows the first day of the week (Sunday returns 0).
DayOfYear Returns the number of days by which the given date follows the first day of the year.
FirstDayOfMonth Returns the first day of the month in which the date falls.
FirstDayOfQuarter Returns the first day of the quarter in which the date falls.
FirstDayOfPeriod Returns the first day of the period in which the given date falls.
FirstDayOfWeek Returns the date corresponding to the first day of the week containing the given date.
FirstDayOfYear Returns the first day of the year in which the date falls.
IsLeapDay Returns true if the date is February 29.
IsLeapYear Returns true if the date falls in a leap year.
ISOWeek Calculates an ISO week number for the given date.
LastDayOfMonth Returns the last day of the month in which the date falls.
LastDayOfQuarter Returns the last day of quarter in which the date falls.
LastDayOfPeriod Returns the last day of the period in which the given date falls.
LastDayOfWeek Returns the date corresponding to the last day of the week containing the given date.
LastDayOfYear Returns the last day of the year in which the date falls.
Month Returns the numeric month of the given date.
MonthsBetween Returns the number of boundaries (beginning of month) crossed between the specified dates.
NextDayOfWeek Returns the date of the next specified weekday following the given date.
PrevDayOfWeek Returns the date of the previous specified weekday before the given date.
Quarter Returns the numeric quarter of the given date.
QuartersBetween Returns the number of boundaries (beginning of quarter) crossed between the specified dates.
Today Returns the current date in the local time zone.
Week Calculates the week number for a given date.
Workday Adds a specified number of workdays to a date.
Workdays Calculates a duration in work days between start and end dates inclusive.
Year Returns a year number of date.
YearsBetween Returns the number of boundaries (beginning of year) crossed between the specified dates.

Time Functions

Function Description
Hour Returns the hour part of the time value.
Minute Returns the minute part of the time value.
Second Returns the second part of the time value.
Time Constructs a time value.

Numeric Functions

Function Description
Acos Arccosine, returns the angle, in radians, whose cosine is specified by the number.
Asin Arcsine, returns the angle, in radians, whose sine is specified by the number.
Atan Arctangent returns the angle in radians whose tangent is specified by a number.
Cos Returns the trigonometric cosine of the angle, specified in radians.
Cot Returns the trigonometric cotangent of the angle, specified in radians.
Degrees Returns the corresponding angle in degrees for an angle specified in radians.
Exp Returns e raised to the power of the given number.
Frac Returns the fractional part of the number.
Int Returns the integer part of a number.
Ln Returns the natural (base e) logarithm of a number.
Log Returns the base 10 logarithm of a number.
Pi Returns the constant value of π.
PV Calculates the Present Value.
Radians Returns the corresponding angle in radians for an angle specified in degrees.
Random Returns a random number.
Sin Returns the trigonometric sine of the angle, specified in radians.
Sqrt Returns the square root of a number.
Tan Returns the trigonometric tangent of the angle, specified in radians.

Rounding and Truncating Functions

Function Description
Ceil Returns the rounded-up value.
Floor Returns the rounded-down value.
Round Returns the rounded value.

Aggregation Functions

Function Description
Count Returns the number of non-null arguments.
Max Returns the maximum value.
Min Returns the minimum value.
Sum Returns the sum of the non-null arguments.

Null Handling Functions

Function Description
IsNull Returns true if value is empty or undefined, otherwise false.
Nz Returns non null value.

Duration Functions

Function Description
Abs Returns the absolute value.
Weeks Returns a duration representing number weeks.
Days Returns a duration representing number days.
Hours Returns a duration representing number hours.
Minutes Returns a duration representing the specified number of minutes.
Mod The function stands for modulus and returns the remainder after a value is divided by a divisor.
Rem The function stands for remainder and returns the remainder after a value is divided by a divisor.
Seconds Returns a duration representing number seconds.
ToWeeks Takes a duration and returns the number of weeks contained in it.
ToDays Takes a duration and returns the number of days contained in it.
ToHours Takes a duration and returns the number of hours contained in it.
ToMinutes Takes a duration and returns the number of minutes contained in it.
ToSeconds Takes a duration and returns the number of seconds contained in it.

Timestamp Functions

Function Description
Now Returns a timestamp representing the current moment.
Timestamp Returns a timestamp specified "as is" in UTC using the provided numeric values.

Location Functions

Function Description
Distance Calculates distance between two locations.
Latitude Extracts latitude.
Longitude Extracts longitude.
ToLocation Constructs the location.

Special Functions

Function Description
Between Checks if the value is greater than or equal to min and less than or equal to max.
Case This function checks if an expression is equal to multiple values and returns the corresponding result; otherwise, the else-result is returned.
DeviceLatitude This function records the GPS coordinates of the mobile device when the user clicks the "Send" button.
DeviceLongitude This function records the GPS coordinates of the mobile device when the user clicks the "Send" button.
DeviceLocation This function records the GPS coordinates of the mobile device when the user clicks the "Send" button.
DeviceTimestamp This function records the timestamp on the mobile device when the user clicks the "Send" button.
FileName This function returns a file name for the file stored in the file attachment column.
FileType This function returns the media type of a file stored in the file attachment column.
Hash Calculate Hash for third-party API calls.
HMAC Calculates HMAC for third-party API calls.
If This function checks multiple conditions and returns the corresponding result for the first one that is true; otherwise, the else-result is returned.
In Returns true if the value is equal to any of the values in the list.
ParentKey This pseudo-function returns a key value for populating a reference column.
Response Returns the data from the server response.
ResponseHeader Retrieve information kept in an HTTP header.
ResponseStatus Returns the response status code.

User/Session Specific Functions

Function Description
AppId Returns the ID of the current database.
BackURL Returns the current URL location information.
Browser Returns either "Desktop", "Tablet", "Mobile", or "TV".
ColumnId Returns a unique column ID number in text format.
Exists Returns true if the referred record selected in the reference column exists and is accessible to the user.
IsUserEmail Returns true if the email address belongs to a database user.
RecordId Returns the internal ID of the current record.
Role Returns the name of a role assigned to a user.
TableId Returns the ID of the current table.
ToUser Converts an email address into a user.
URL Returns the full URL of the current page.
URLRoot Returns the first part of the URL used to access TeamDesk, including the protocol and the site name.
User Returns the user currently accessing the database.
UserToEmail Returns the user’s email address.
UserToName Returns the user’s name.

Parameters for the Ask

Function Description
Ask Ask the user for input for the parameter and evaluate the condition. This function is used in conjunction with the Parameter function.
Parameter Create a parameter for user input. This function is used in conjunction with the Ask function.

Logical Operators

Operator Description
Conditional logical AND operator and The conditional logical AND operator and returns true if both the left-hand operand and the right-hand operand evaluate to true; otherwise, it returns false.
Conditional logical OR operator or The conditional logical OR operator or returns true if either the left-hand operand or the right-hand operand evaluates to true; otherwise, it returns false.
Conditional logical NOT operator not The conditional logical NOT operator not returns true if the operand evaluates to false, and false if the operand evaluates to true.

Comparison Operators

Operator Description
Equality operator = The equality operator = returns true if its operands are equal, false otherwise.
Greater than or equal operator >= The greater than or equal operator >= returns true if its left-hand operand is greater than or equal to its right-hand operand, false otherwise.
Greater than operator > The greater than operator > returns true if its left-hand operand is greater than its right-hand operand, false otherwise.
Less than or equal operator <= The less than or equal operator <= returns true if its left-hand operand is less than or equal to its right-hand operand, false otherwise.
Less than operator < The less than operator < returns true if its left-hand operand is less than its right-hand operand, false otherwise.
Inequality operator <> The inequality operator <> returns true if its operands aren’t equal, false otherwise.

Arithmetical Operators

Operator Description
Addition operator + The addition operator + computes the sum of its operands.
Subtraction operator - The subtraction operator - subtracts its right-hand operand from its left-hand operand.
Multiplication operator * The multiplication operator * computes the product of its operands.
Division operator / The division operator / divides its left-hand operand by its right-hand operand.
Concatenate operator & The concatenate operator & returns a text value that is the concatenation of its left-hand and right-hand operands.
Power operator ^ The power operator ^ raises its left-hand operand to the power of its right-hand operand.