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
- Text Functions
- Date Functions
- Time Functions
- Numeric Functions
- Rounding and Truncating Functions
- Aggregation Functions
- Null Handling Functions
- Duration Functions
- Timestamp Functions
- Location Functions
- Special Functions
- User-/Session Specific Functions
- Parameters For The Ask
- Logical Operators
- Comparison Operators
- Arithmetic Operators
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 snd 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. |
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. |
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 | Calculates the condition if parameter values were supplied or evaluates to true if not. |
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. |