# 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 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. |

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. |