(Date d)

Text

Returns the Text value represented by the Date value d. If column reference is provided, TeamDesk derives formatting options from the column properties. Also the Format() function respects user-specific locale settings and supports a number of additional standard and custom formatting patterns.

Format([Date Column]) takes the date value stored in the Date Column and returns it as a text type value.

(Date d, Text fmt)

Text

You can use two-argument version of Format() function. The first argument returns the Text value represented by the Date value d. The second argument is a text literal specifying the format. For the Date type you can specify one of the following Text fmt values to return the meaning:

This function supports a number of additional standard and custom formatting patterns.

Format(#2012-02-14#, "MM/yy") takes the date 2012-02-14 value and returns the text value "02/12".

(Date d, "pattern", "culture")

Text

You can use three-argument version of Format() function. The first argument returns the Text value represented by the Date value d. The second argument is a pattern. The third – culture. Please, review additional format patterns and supported cultures.

Format(#2009-06-15#, "D", "en-US") returns the text value "Monday, June 15, 2009".

Format(#2009-06-15#, "D", "es-ES") returns the text value "lunes, 15 de junio de 2009".

Format(#2009-06-15#, "D", "el-GR") returns the text value "Δευτέρα, 15 Ιουνίου 2009".

(Number n)

Text

Returns the Text value represented by the Number value n. If column reference is provided, TeamDesk derives formatting options from the column properties.

Format([Numeric Column]) takes the numeric value stored in the Numeric Column and returns it as the text type value.

(Number n, Text fmt)

Text

You can use two-argument version of Format() function. The first argument returns the Text value represented by the Number value n. The second argument is a text literal specifying the format. For the Number type you can specify one of the following Text fmt values to return the meaning:

Format(1500.63,"$#") returns the text value "$15001".

Format(0.3,"$#") returns a blank text value with the unit sign "$".

Format(1500.63,"#,#") returns the text value "1,501".

Format(0.3,"#,#") returns a blank text value.

Format(1500.63,"#,0") returns the text value "1,501".

Format(0.3,"#,0") returns the text value "0".

Format(1500.63,"0") returns the text value "1501".

Format(0.3,"0") returns the text value "0".

Format(1.63,"0%") returns the text value "163%".

Format(0.0003,"0%") returns the text value "0%".

Format(2.0039900, "$#,#.####") returns the text value "$2.004".

Format(0.000002, "$#,#.####") returns a blank text value.

Format(-5.204, "-$#,0.00") returns the text value "-$5.20".

Format(0.003, "-$#,0.00") returns the text value "$0.00".

(Number n, Text fmt, Text unit)

Text

You can use three-argument version of Format() function to provide the format AND unit, if it is different from "$" or "%".

The first argument returns the Text value represented by the Number value n. The second argument is a text literal specifying the format. For the Number type you can specify one of the following Text fmt values to return the meaning:

The third argument is a text literal specifying the unit value to be inserted instead of the Text fmt value "$" or "%".

Format(1500.63,"$#", "£") returns the text value "£15001".

Format(0.3,"$#","£") returns a blank text value with the unit sign "£".

Format(1500.63,"#,#","£") returns the text value "1,501£".

Format(0.3,"#,#","£") returns a blank text value with the unit sign "£".

Format(1500.63,"#,0","£") returns the text value "1,501£".

Format(0.3,"#,0","£") returns the text value "0£".

Format(1500.63,"0","£") returns the text value "1501£".

Format(0.3,"0","£") returns the text value "0£".

Format(1.63,"0%","£") returns the text value "163£".

Format(0.0003,"0%","£") returns the text value "0£".

Format(2.0039900, "$#,#.####","£") returns the text value "£2.004".

Format(0.000002, "$#,#.####","£") returns a blank text value with the unit sign "£".

Format(-5.204, "-$#,0.00","£") returns the text value "-£5.20".

Format(0.003, "-$#,0.00","£") returns the text value "£0.00".

(Number n, "pattern", "unit", "culture")

Text

You can use four-argument version of Format() function. The first argument returns the Text value represented by the Number value n. The second argument is a pattern. The third – unit. The forth – culture. Please, review additional format patterns and supported cultures.

Format(123456.789, "C", "$", "en-US") returns the text value "$123,456.79".

Format(123456.789, "C", "€", "fr-FR") returns the text value "€123 456,79".

Format(123456.789, "C", "¥", "ja-JP") returns the text value "¥123,456.79"

(Time t)

Text

Returns the Text value represented by the TimeOfDay value t. If column reference is provided, TeamDesk derives formatting options from the column properties. Also the Format() function respects user-specific locale settings and supports a number of additional standard and custom formatting patterns.

Format([Time Column]) takes the time value stored in the Time Column and returns it as the text type value.

(Time t, Text fmt)

Text

You can use two-argument version of Format() function. The first argument returns the Text value represented by the TimeOfDay value t. The second argument is a text literal specifying the format. For the Date type you can specify one of the following Text fmt values to return the meaning:

Format([Time Column], "t") takes the time value "10:15 AM" stored in the Time Column and returns it as the text type value.

Format([Time Column], "T") takes the time value "10:15 AM" stored in the Time Column and returns it as the text type value "10:15:00 AM".

(Time t, "pattern", "culture")

Text

You can use three-argument version of Format() function. The first argument returns the Text value represented by the TimeOfDay value t. The second argument is a pattern. The third – culture. Please, review additional format patterns and supported cultures.

Format(#13:45:30#, "t", "en-US") returns the text value "1:45 PM".

Format(#13:45:30#, "t", "hr-HR") returns the text value "13:45".

Format(#13:45:30#, "t", "ar-EG") returns the text value "01:45م".

(Timestamp t)

Text

Returns the Text value represented by the Timestamp value t. If column reference is provided, TeamDesk derives formatting options from the column properties. Also the Format() function respects user-specific locale settings and time zone. This function supports a number of additional standard and custom formatting patterns.

Format([Timestamp Column]) takes the timesyamp value stored in the Timestamp Column and returns it as the text type value.

(Timestamp t, Text fmt)

Text

You can use two-argument version of Format() function. The first argument returns the Text value represented by the Timestamp value t. The second argument is a text literal specifying the format. For the Date type you can specify one of the following Text fmt values to return the meaning:

This function supports a number of additional standard and custom formatting patterns.

Format([Timestamp Column], "g") takes the timestamp value "11/5/2012 9:18:06 AM" stored in the Timestamp Column and returns it as the text type value "11/5/2012 9:18 AM".

Format([Timestamp Column], "G") takes the timestamp value "11/5/2012 9:18:06 AM" stored in the Timestamp Column and returns it as the text type value "11/5/2012 9:18:06 AM".

Format([Timestamp Column], "f") takes the timestamp value "11/5/2012 9:18:06 AM" stored in the Timestamp Column and returns it as the text type value "Monday, November 05, 2012 9:18 AM".

Format([Timestamp Column], "F") takes the timestamp value "11/5/2012 9:18:06 AM" stored in the Timestamp Column and returns it as the text type value "Monday, November 05, 2012 9:18:06 AM".

(Timestamp t, "pattern", "culture")

Text

You can use three-argument version of Format() function. The first argument returns the Text value represented by the Timestamp value t. The second argument is a pattern. The third – culture. Please, review additional format patterns and supported cultures.

Format(#2009-06-15 13:45:30#, "G", "en-US") returns the text value "6/15/2009 4:45:30 PM".

Format(#2009-06-15 13:45:30#, "G", "da-DK") returns the text value "15-06-2009 16:45:30".

Format(#2009-06-15 13:45:30#, "G", "zh-CN") returns the text value "2009/6/15 16:45:30".

(Number n)

Boolean

Returns true if the Number n is a non-zero value, otherwise returns false.

ToBoolean(1) returns true value "Yes".

ToBoolean(0) returns false value "No".

(Text x)

Boolean

Converts the values "1", "true" or "yes" to true, other values to false. Case is ignored.

ToBoolean("1") returns true value "Yes".

ToBoolean("text") returns false value "No".

(Text x)

Date

Converts the text value x into a Date. x can be several of the popular date formats, including "January 15, 2012", "1/15/12", "2012/1/15", "1-15-2012".

ToDate("January 15, 2012") returns the date value "1/15/2012".

ToDate("1/15/12") returns the date value "1/15/2012".

ToDate("2012/1/15") returns the date value "1/15/2012".

ToDate("1-15-2012") returns the date value "1/15/2012".

(Timestamp x)

Date

Timestamps have a date, time and time zone associated with them, while Dates do not have time and time zone. This function converts the Timestamp x into a Date by returning the Date, in the local time zone, in which Timestamp x falls.

ToDate("3/25/2013 11:28:57 AM") returns the date value " 3/25/2013".

(Boolean b)

Number

Returns 0 if b is false, 1 if b is true.

ToNumber(true) returns 1

ToNumber(false) returns 0

(Text x)

Number

Returns the number represented by the Text value x.

ToNumber("123.56") returns number 123.56

(<any> x)

Text

Returns a Text value containing the print representation of the argument x.

ToText([Date Column]) takes the date value "25/3/2013" stored in the Date Column and returns it as the text type value "2013-03-25"

ToText([Timestamp Column]) takes the timestamp value "25/03/2013 10:51:34 AM" stored in the Timestamp Column and returns it as the text type value "2013-03-25 10:51:34".

ToText([Time Column]) takes the time value "10:15 AM" stored in the Time Column and returns it as the text type value "10:15:00".

ToText([Duration Column]) takes the duration value "30 minutes" stored in the Duration Column and returns it as the text type value "00:30:00".

ToText([Record Owner]) takes the user type value and returns the email address of the user stored in the Record Owner column.

ToText(true) returns "1"

ToText(false) returns "0"

To convert the Numeric data, use the Format function.

(Text x)

Time

Converts the text value x into a TimeOfDay. x can be several of the popular time formats, including "3 pm", "3:04 pm", "22:00", "2:03:29 am", "12:03:29.345".

ToTimeOfDay("3 pm") returns the time value "3:00 PM".

ToTimeOfDay("3:04 pm") returns the time value "3:04 PM".

ToTimeOfDay("22:00") returns the time value "10:00 PM".

ToTimeOfDay("2:03:29 am") returns the time value "2:03 AM".

ToTimeOfDay ("12:03:29.345") returns the time value "12:03 PM".

(Timestamp t)

Time

Returns the TimeOfDay on which the Timestamp t falls in the local time zone.

ToTimeOfDay([Timestamp Column]) takes the timestamp value "11/5/2012 9:21:23 AM" stored in the Timestamp column and returns the time value "9:21 AM".

(Date d)

Timestamp

Returns a Timestamp which is 12:00 am of the given Date d in the local time zone (midnight at the beginning of the Date).

ToTimestamp([Date Column]) takes the date value "4/30/2013" stored in the date column and returns the timestamp value "4/30/2013 12:00:00 AM"

(Date d, TimeOfDay t)

Timestamp

Returns a Timestamp which is at the given TimeOfDay t, on the given Date d in the local time zone.

ToTimestamp([Date Column], [Time Column]) takes the date value "4/30/2013" stored in the Date Column, the time value "10:20:00 AM" stored in the Time Column and returns the timestamp value "4/30/2013 10:20:00 AM"

(values, list)

Boolean

Helps you dealing with text columns with Choices option set to checkboxes. For these columns, the value is a comma-separated concatenated string of checked values.

This function accepts comma separated lists of values in both values and list arguments. It checks that all values specified in its list argument exist in a values argument. That's, All("A,B,C,D", "D,A") evaluates to true, while All("A,B,C,D", "D,E") evaluates to false because of E is not present in A,B,C,D.

You may use this function in the filter of the view that asks the user for a checkbox column. For example there is the [Multiple Choices] text colum in a table. The user should be able to find the records including an exact combination of choices asked by this user. The following formula can be written in the filter of the view, if you want to use such exact match: Ask(All([Multiple Choices], [?Multiple Choices])).

(values, list)

Boolean

Helps you dealing with text columns with Choices option set to checkboxes. For these columns, the value is a comma-separated concatenated string of checked values.

This function accepts comma separated lists of values in both values and list arguments. It ensures that at least one value from list exists in values.

For example, both Any("A,B,C,D", "D,A") and Any("A,B,C,D", "D,E") evaluate to true because there is at least D that is present in A,B,C,D.

You may use this function in the filter of the view that asks the user for a checkbox column. For example, there is the [Multiple Choices] text column in a table. The user should be able to filter the records including any value among the choices combination asked by this user. The following formula can be written in the filter of the view, if you want to use such broad match: Ask(Any([Multiple Choices], [?Multiple Choices])).

(Text u, Text v)

Boolean

Returns true if the text u begins with the text v, otherwise returns false.

Begins("test", "tes") returns true

Begins("test", "st") returns false

([Column])

Text

Returns a unique column Id number in a text format.

ColumnId([Column Name]) returns text value, for example, 12345678

(Text u, Text v)

Boolean

Returns true if v is contained in u, otherwise returns false.

Contains("test", "es") returns true

Contains([Type], "new") returns true if the Type text column contains the value "new"

Contains("test", "et") returns false

(Text u, Text v)

Boolean

Returns true if the text u ends with the text v, otherwise returns false.

Ends("test", "st") returns true

Ends("test", "te") returns false

()

Text

Returns a text string in the XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX format, where X is a character from the 0123456789ABCDEF character set.

If you want to generate a unique identifier for each record, create a Formula-Text column with the Guid() function.

(Text t, Number n)

Text

Returns the leftmost n characters from the Text argument t.

Left("invoice",3) returns "inv"

(Text t, Text d)

Text

Returns the left part of a text value up to but not including the first occurrence of a delimiter value.  The first argument, t, is the value to be searched. The second argument, d, is a delimiter text value. If the text t does not include the delimiter d, the entire text is returned.

Left("John Smith"," ") returns "John"

Left("test/123","/") returns "test"

Left("test/123",";") returns "test/123"

(Text t)

Number

Returns the number of characters in t.

Len("xxx") returns 3

(Text d, Text t1, Text t2, ...)

Text

Concatenates all arguments starting with the second argument, using the first argument as a delimiter between them. If one of the arguments is blank, this argument and the corresponding delimiter are omitted.

List("-","model","a" "2012") returns "model-a-2012"

List(" ",[First Name], [Last Name]) takes the value "John" stored in the First Name Column and the value "Smith" stored in the Last Name Column, returns the text value "John Smith". If the Last Name column value is blank, returns the value "John". If the First Name column value is blank, returns "Smith".

(Text t)

Text

Returns t converted to lower case.

Lower("XXX") returns "xxx"

(Text t, Number p, Number n)

Text

Returns n characters from the middle of t, starting at position p.  The first character is position 1.

Mid("type20A",5,2) returns the text value "20"

Mid("type20A",7,3) returns the text value "A"

(Text t, Text d)

Text

Returns what remains after excluding the left part of a text value up to and including the first occurrence of a delimiter value.  The first argument, t, is the value to be searched. The second argument, d, is a delimiter text value. If the text t does not include the delimiter d, the blank value is returned.

NotLeft("Jane Smith", " ") returns the text value "Smith"

NotLeft("type-20A", "-") returns the text value "20A"

NotLeft("type-20A", "/") returns the blank value.

NotLeft("type-20A", "/-") returns the blank value.

(Text t, Number n)

Text

Returns what remains after excluding the leftmost n characters from the Text argument t.

NotLeft("typeABC",4) returns "ABC"

(Text t, Number n)

Text

Returns what remains after excluding the rightmost n characters from the Text argument t.

NotRight("typeABC",3) returns "type"

(Text t, Text d)

Text

Returns what remains after excluding the right part of a text value starting at the last occurrence of a delimiter value.  The first argument, t, is the value to be searched. The second argument, d, is a delimiter text value. If the text t does not include the delimiter d, the blank value is returned.

NotRight("Jane Smith", " ") returns the text value "Jane"

NotRight("type-20A", "-") returns the text value "type"

NotRight("type-20A", "/") returns the blank value.

NotRight("type-20A", "/-") returns the blank value.

(text, length)

Text

Right-aligns the characters in the string by padding them with leading white space on the left for a specified total length. If the width of the string exceeds specified width the string is not padded.

PadLeft("ABC", 10) returns

" ABC"

(text, length, filler)

Text

Right-aligns the characters in the string by padding them with specified filler character on the left, for a specified total width. The “filler” parameter is optional and defaults to space. If the width of the string exceeds specified width the string is not padded.

PadLeft("ABC", 10, "-") returns "-------ABC"

(text, length)

Text

Left-aligns the characters in the string by padding them with trailing white space on the right, for a specified total length. If the width of the string exceeds specified width the string is not padded.

PadRight("XYZ", 10) returns "XYZ "

(text, length, filler)

Text

Left-aligns the characters in the string by padding them on the right with specified filler character, for a specified total width. The “filler” parameter is optional and defaults to space. If the width of the string exceeds specified width the string is not padded.

PadRight("XYZ", 10, "-") returns "XYZ-------"

(Text t, Number p, Text d)

Text

Returns the specified part of a text value. The parts are separated by the occurrence of any delimiter character. The first argument, t, is the value to be searched. The second argument, p, is the position of the part in the argument t. The first part starting on the left is position 1.  Negative part numbers can be used to start from the right. The third argument, d, is a text value containing all the possible delimiter characters.

If space is included in the delimiter list it is handled specially. It acts as a delimiter, but contiguous spaces surrounding a delimiter are ignored rather than each acting as a separate delimiter.

Part("model/year/car",1, "/") returns "model"

Part("model/year/car",-1, "/") returns "car"

Part("model/year/car",3, "/") returns "car"

Part("model/year/car",4, "/") returns blank value.

Part("David Neil Evans",2, " ") returns "Neil"

(Text t)

Text

Capitalizes the first letter in the text string t and any other letters in 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.

Proper("Quick bROWn FOX") returns "Quick Brown FOX"

Proper("Quick,bROWn,FOX") returns "Quick,Brown,FOX"

Proper("Quick/bROWn/FOX") returns "Quick/Brown/FOX"

Proper(Lower("Quick bROWn FOX")) returns "Quick Brown Fox"

(Text t, Text old, Text new)

Text

In the string t replaces substrings containing old with the text supplies in new.

Replace("TypeAA28", "AA", "BB") returns "TypeBB28"

Replace("Linda May", "May", "Evans") returns "Linda Evans"

(Text t, Number n)

Text

Returns the rightmost n characters from the Text argument t.

Right("typeAA1",3) returns "AA1"

(Text t, Text d)

Text

Returns the right part of a text value starting at the character after the last occurrence of a delimiter value.  The first argument, t, is the value to be searched. The second argument, d, is a delimiter text value. If the text t does not include the delimiter d, the entire text is returned.

Right("John Smith"," ") returns "Smith"

Right("test/AA1","/") returns "AA1"

Right("test/AA1",";") returns "test/AA1"

(Text t)

Text

Returns t with leading and trailing white space characters removed. White space characters are space, tab, CR and LF.

Trim(" NY City ") returns "NY City"

(Text t)

Text

Returns t converted to upper case.

Upper("xxx") returns "XXX"

(text)

Text

Encodes the text so that it can be used in a URL, by substituting special character combinations for certain reserved characters, like '&' and '=' and space. The function is aware of international characters and escapes them using UTF-8 most sites expect in the URL.

URLEncode("test@email.com") returns the text value "test%40email.com"

(text)

Text

Performs decoding of URL parameters containing UTF-8 characters. This function is a counterpart of URLEncode()

URLDecode("test%40email.com") returns the text value "test@email.com"

(url, param)

Text

Extracts and decodes the value of the parameter specified by name from URL's query string. If there is no such parameter, NULL is returned. If there are multiple parameters under the same name, their values are concatenated in a comma-separated string.

URLParam ("google.com?q=teamdesk", "q") returns "teamdesk"

(url, param, index)

Text

Function helps dealing with identically named parameters. If there are many, you can retrieve parameter's value by its name and 1-based index.

URLParam ("google.com?q=teamdesk&q=dbflex", "q", 2) returns "dbflex"

(Date d, Number m)

Date

Returns the date which is m months after the given date d, with the same day number. If the day doesn't exist in that month, the last day of that month is returned.

Example 1: AdjustMonth([Date Column],5)

So, if [Date Column] is the Date type column and it keeps the date value 3/20/2013, the function returns the date value 8/20/2013

Example 2: AdjustMonth([Date Column],1)

So, if [Date Column] is the Date type column and it keeps the date value 1/30/2013, the function returns the date value 2/28/2013

(Date d, Number y)

Date

Returns the date which is y years after the given date d, with the same month and day. If the day doesn't exist in that month, the last day of that month is returned.

Example 1: AdjustYear([Date Column],2)

So, if [Date Column] is the Date type column and it keeps the date value 1/30/2012, the function returns the date value 1/30/2014

Example 2: AdjustYear([Date Column],2)

So, if [Date Column] is the Date type column and it keeps the date value 2/29/2012, the function returns the date value 2/28/2014

(Number year, Number month, Number day)

Date

Creates a date from a year, month and day.

Date(2012,8,20) returns the date value 8/20/2012

(Date d)

Number

Returns the day of the month of the given Date d.

Day() function works with columns of the Date or Formula-Date type.

Day([Date Column])

If [Date Column] is the Date type column and it keeps the date value 03/20/2013, the function returns the numeric value 20

(Date d)

Number

Returns the number of days by which the given date d follows the first day of the week (Sunday returns 0).

DayOfWeek([Date])

If [Date] is the Date type column and it keeps the date value 3/21/2013, the function returns numeric value 4 that means Thursday.

If [Date] is the Date type column and it keeps the date value 3/24/2013, the function returns numeric value 0 that means Sunday.

If the date value is blank, the system returns the numeric value 0

Below is the formula that calculates week number according to ISO 8601 standard:

Int(ToDays([Date] - Date(Year([Date] - Days(DayOfWeek([Date] - 1d)) + 3d), 1, 3) + Days(DayOfWeek(Date(Year([Date] - Days(DayOfWeek([Date] - 1d)) + 3d), 1, 3)) + 6)) / 7)

(Date d)

Number

Returns the number of days by which the given date d follows the first day of the year.

DayOfYear([Date Column])

If [Date Column] is the Date type column and it keeps the date value 01/01/2013, the function returns the numeric value 1

If [Date Column] is the Date type column and it keeps the date value 03/20/2013, the function returns the numeric value 83

If the date value is blank, the system returns the numeric value 0

(Date d)

Date

Returns the first day of the month in which the date falls.

FirstDayOfMonth (#2013-03-20#) returns 3/1/2013

If a date is blank, the system returns a blank value.

(Date d)

Date

Returns the first day of quarter in which the date falls.

FirstDayOfQuarter (#2013-03-20#) returns 1/1/2013

If a date is blank, the system returns a blank value.

(Date d, Duration p, Date r)

Date

Returns the first day of the period in which the date falls. 

The cycle of periods is defined by the given Duration p, repeated in sequence starting at the given reference date r. If the period p is not a whole number of days, the fractional part is ignored.

FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1)) finds the first day of this week assuming the week starts on Sunday, since 1/1/2006 is Sunday. For weeks starting on Monday you can use 1/1/2007, for example: FirstDayOfPeriod(Today(), Days(7), Date(2007,1,1)).

When setting up views which include specific dates or date ranges, you can select "This Week", "Last Week", and "Next Week".

So, having all that and assuming you are checking this/next/last week for a column named Date, here are formulas for:

This Week:
FirstDayOfPeriod([Date], Days(7), Date(2006,1,1)) = FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1))
Last Week:
FirstDayOfPeriod([Date], Days(7), Date(2006,1,1)) = FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1)) - Days(7)
Next Week:
FirstDayOfPeriod([Date], Days(7), Date(2006,1,1)) = FirstDayOfPeriod(Today(), Days(7), Date(2006,1,1)) + Days(7)

(Date d)

Date

Returns the date passed into the first day of week according to database location settings -- that's to preceding Sunday for the US, Canada, Japan and some other country, Saturday for much of the Middle East countries and Monday for the rest of the world.

Example for US: FirstDayOfWeek(#2021-11-23#) returns 11/21/2021

Example for Europe: FirstDayOfWeek(#2021-11-23#) returns 22/11/2021

(Date d)

Date

Returns the first day of the year in which the date falls.

FirstDayOfYear(Today()) returns the first day of the current year.

(Date d)

Boolean

Returns true if d is February 29.

IsLeapDay(#2012-02-29#)returns true

IsLeapDay(#2013-02-28#) returns false

(Date d)

Boolean

Returns true if the date d falls in a leap year.

IsLeapYear(#2012-01-01#) returns true

IsLeapYear(#2013-03-03#) returns false

(Number y)

Boolean

Returns true if the year y is a leap year.

IsLeapYear(2012) returns true

IsLeapYear(2013) returns false

(Date d)

Number

Calculates a week number of date d. All weeks start with Monday. Week one starts on Monday of the first week of the calendar year with Thursday. Used by the most of European countries, the most of Asia and Oceania.

ISOWeek(#2016-07-25#) returns the week number 30

(Date d)

Date

Returns the last day of the month in which the date falls.

LastDayOfMonth (#2013-02-20#) returns 2/28/2013

If a date is blank, the system returns a blank value.

(Date d)

Date

Returns the last day of quarter in which the date falls.

LastDayOfQuarter(#2013-03-20#) returns 3/31/2013

If a date is blank, the system returns a blank value.

(Date d, Duration p, Date r)

Date

Returns the last day of the period in which the date falls. 

The cycle of periods is defined by the given Duration p, repeated in sequence starting at the given reference date r. If the period p is not a whole number of days, the fractional part is ignored.

LastDayOfPeriod(Today(), Days(7), Date(2006,1,1)) finds the last day of this week assuming the week starts on Sunday, since 1/1/2006 is Sunday. For weeks starting on Monday you can use 1/1/2007, for example: LastDayOfPeriod(Today(), Days(7), Date(2007,1,1)).

(Date d)

Date

Returns the date passed into the last day of week according to database location settings -- that's to preceding Saturday for the US, Canada, Japan and some other country, Friday for much of the Middle East countries and Sunday for the rest of the world.

Example for US: LastDayOfWeek(#2021-11-23#) returns 11/27/2021

Example for Europe: LastDayOfWeek(#2021-11-23#) returns 28/11/2021

(Date d)

Date

Returns the last day of the year in which the date falls.

LastDayOfYear(Today()) returns the last day of the current year.

(Date d)

Number

Returns the month number of the Date d. January is month 1.

Month(#2013-03-20#) returns 3.

(date, date)

Number

Returns the number of boundaries [beginning of month] crossed between the specified dates.

MonthsBetween(#2012-01-01#, #2011-12-31#) returns 1

MonthsBetween(#2013-08-10#, #2013-01-14#) returns 7

(date, number)

Date

Returns the weekday next to the specified date. Weekday is defined by the parameter numeric values (e.g. 0=Sunday, 1=Monday, etc.).

NextDayOfWeek([Date Received], 3) returns the first Wednesday that follows the Date in the Date Received column.

NextDayOfWeek(#2013-03-22#, 3) returns the first Wednesday date 03/27/2013

(date, number)

Date

Returns the weekday previous to the specified date. Weekday is defined by the parameter numeric values (e.g. 0=Sunday, 1=Monday, etc.).

PrevDayOfWeek([Date Shipped], 5) returns the last Friday that occurse before the Date in the Date Shipped column.

PrevDayOfWeek(#2013-03-20#, 5) returns the last Friday date 03/15/2013

PrevDayOfWeek(Date([Year], 1, 4), 1) + ([ISO Week Number] - 1) * 7 converts an ISOWeek number into 1st day of week starting with Monday. The [Year Number] and [ISO Week Number] are columns of the Numeric type.

(Date d)

Number

Returns quarter number of the Date d

Quarter(#2013-03-20#) returns 1

(date, date)

Number

Returns the number of boundaries [beginning of quarter] crossed between the specified dates.

QuartersBetween(#2012-01-01#, #2011-12-31#) returns 1

()

Date

Returns the current date in the local time zone.

Today() returns the current date in the local time zone.

(Date d)

Number

Calculates week number as follows: week one begins on January 1st; week two begins on the following first day of week specific for each location specified in “Language and location” database property. For Canada, USA, China, Korea, Japan, Israel, South Africa, most of Latin America it’s Sunday.

Week(#2016-07-25#) returns the week number 31

(date, number)

Number

Calculates week number as described above using a specified first day of week (e.g. 0=Sunday, 1=Monday, 6 = Saturday etc.)

Week(#2016-07-25#, 3) returns the week number 30

(start date, days)

Date

This function is an equivalent of WORKDAY function in Excel. It adds the number of work days to a start date.

The number of days can be a numeric value or a duration value.

The weekends are calculated and subtracted according to the Weekends database property (Saturday, Sunday is the default).

If the number of day is a numeric value, the formula can be the following:

Workday(#2015-08-12#, 5) It results to #2015-08-19#.

If the number of day is a duration value, the formula can be the following:

Workday(#2015-08-12#, 5d) It results to #2015-08-19#.

(start date, days, weekends)

Date

Adds the number of work days to a start date, given the weekends specified by third argument. This function is an equivalent of WORKDAY function in Excel.

The number of days can be a numeric value or a duration value.

The Weekends argument is a string specifying either one or two digits in an 1..7 range to specify first and second weekend starting from Monday (1) to Sunday (7) or a "bitmap" consisting of exactly seven zeroes and ones, indicating whether the day is weekend (1) or not (0). Thus, classic Saturday-Sunday pair can be represented either as "67" or "0000011".

If the number of day is a numeric value, the formula can be the following:

Workday(#2015-08-12#, 5, "67") It results to #2015-08-19#.

If the number of day is a duration value, the formula can be the following:

Workday(#2015-08-12#, 5d, "67") It results to #2015-08-19#.

(start date, end date)

Duration

Calculates a number of work days between Start and End dates inclusive.

The resulting value is the duration value calculated in days.

The weekends are calculated and subtracted according to the Weekends database property (Saturday, Sunday is the default).

Workdays(#2015-08-12#, #2015-08-19#) results to 6 days.

(start date, end date, weekends)

Duration

Calculates a number of work days between Start and End dates inclusive, given the weekends specified by third argument.

The resulting value is the duration value calculated in days.

The Weekends argument is a string specifying either one or two digits in an 1..7 range to specify first and second weekend starting from Monday (1) to Sunday (7) or a "bitmap" consisting of exactly seven zeroes and ones, indicating whether the day is weekend (1) or not (0). Thus, classic Saturday-Sunday pair can be represented either as "67" or "0000011".

Workdays(#2015-08-12#, #2015-08-19#, "67") results to 6 days.

(Date d)

Number

Returns a year number of Date d.

Year(#2013-03-22#) returns the year number 2013

(date, date)

Number

Returns the number of boundaries [beginning of quarter] crossed between the specified dates.

YearsBetween(#2012-01-01#, #2011-12-31#) returns 1

(Time t)

Number

Returns the hour part of the argument t. The hour is in the range 0 to 23.

Hour(ToTimeOfDay("2:03:29 pm")) returns 14

(Time t)

Number

Returns the minute part of the argument t. The minute is in the range 0 to 59.

Minute(ToTimeOfDay("2:03:29 pm")) returns 3

(Time t)

Number

Returns the second part of the argument t.  The second is in the range 0 to 59.

Second(ToTimeOfDay("2:03:29 pm")) returns 29

(Hour t, Minute t)

Time

Constructs time from the hour and minute components.

Time(22,15) returns 22:15

(Hour t, Minute t, Second t)

Time

Constructs time from the hour, minute and second components.

Time(22,15,5) returns 22:15:05

(Seconds t)

Time

Constructs time from the number of seconds since midnight.

Time(246) returns 00:04:06

(<any> x, ...)

Number

Counts the number of non-null arguments. For Text arguments, non-blanks are counted. For Boolean arguments, trues are counted. The exeption is the zero value for Numeric arguments, it is counted.

Count("","xyz","zzz") returns 2

Count(28,50,0) returns 3

Count(true,false) returns 1

Count("",28,"xyz", true, false) returns 3

(<any> x, <any> y, ...)

<same type as x and y>

This function can take 2 or more arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments. Null values are ignored.

For Numbers, returns the argument that is greatest.
For Text, returns the argument that sorts last alphabetically.
For Durations, returns the argument that is longest.
For Dates returns the argument that is latest.
For Timestamps, returns the argument that is latest.
For TimeOfDays, returns the argument that is latest.
For Booleans returns the argument that is largest, treating false as less than true.

Max(50,60,70,100) returns 100

Max("Jane", "Rita", "Linda") returns "Rita"

Max(3d, 22h) returns 3d (tree days)

Max(#2013-03-22#, #2013-05-15#) returns 2013-05-15 date value.

Max(ToTimestamp(#2013-03-22#), ToTimestamp(#2013-05-15#)) returns 05/15/2013 00:00

Max(ToTimeOfDay("3 pm"), ToTimeOfDay("8 pm") returns 20:00

Max(false,true,false) returns true

(<any> x, <any> y, ...)

<same type as x and y>

This function can take 2 or more arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments.  Null values are ignored, the exception is zero value for Numeric columns.

For Numbers, returns the argument that is least.
For Text, returns the argument that sorts first alphabetically.
For Durations, returns the argument that is shortest.
For Dates returns the argument that is earliest.
For Timestamps, returns the argument that is earliest.
For TimeOfDays, returns the argument that is earliest.
For Booleans returns the argument that is smallest, treating false as less than true.

Min(50,60,70,100) returns 50

Min(50,60,70,100,0) returns 0

Min("Jane", "Rita", "Linda") returns "Jane"

Min(3d, 22h) returns 22hrs (twenty two hours)

Min(#2013-03-22#, #2013-05-15#) returns 2013-03-22 date value.

Min(ToTimestamp(#2013-03-22#), ToTimestamp(#2013-05-15#)) returns 03/22/2013 00:00

Min(ToTimeOfDay("3 pm"), ToTimeOfDay("8 pm") returns 15:00

Min(false,true,false) returns false

(Duration d, ...)

Duration

Returns the sum of the non-null arguments.

Sum(3d,22h) returns 94 hours (or 3.92 days )

(Number n, ...)

Number

Returns the arguments.

Sum(20,0,30) returns 50

Sum(0,0,0) returns 0

(column)

Boolean

This function represents the contents of an empty field. The column may be of any data type except formulas. The result is true if argument is null, otherwise false.

IsBlank([Assigned User]) returns true if the column named Assigned User is empty or undefined, otherwise returns false.

IsBlank([Price]) returns true if the Numeric column named Price is empty or undefined, otherwise returns false, even if zero value is stored, it returns false.

(<any> x)

Boolean

Null represents the contents of an empty field. The result is true if x is empty or undefined, otherwise false. The argument x may be of any data type. The exceptions are the Numeric and Duration types, only Numeric column or Duration column can be used as the argument x . Also the exception is zero value stored in the Numeric column. This zero value differs from the empty value and works like any other numeric value. So, when a Numeric column comprises zero value, the function result is false. The Boolean data types do not have an undefined value, so for them this function will always return false.

IsNull([Date]) returns true if the column named Date is empty or undefined, otherwise returns false.

IsNull([Price]) returns true if the Numeric column named Price is empty or undefined, otherwise returns false, even if zero value is stored, it returns false.

(<any> x, <any> y, …)

This function can take 2 or more arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments.  It returns x if x is not null. If it is null, it returns the next alternate value, if this value is not null.

Nz([Delivery Date], [Shipment Date]) returns the value in the Delivery Date column, if it exists. If there is no value in the Delivery Date column, this formula returns the value in the Shipment Date column.

(text)

Text

This function returns text value if this value is not null.  If it is null, it returns an empty string instead.

Nz("TEST") returns TEST value.

Nz("") returns an empty string.

(Value, From, To)

Boolean

Checks if the Value is greater or equal than From and less or equal than To

Between([Check Date], [From Date],[To Date] ) returns true if the Check Date value is greater or equal than From Date and less or equal than To Date value.

(<any> e, <any> val1, <any> result1, ..., <any> else-result)

<same type as result1>

Case() is a variation of the If() function, designed to avoid duplication of an expression when it is to be checked against a series of values.  The expression e is evaluated once, and compared to each of the values sequentially. If the expression compares equal to any value, the corresponding result is returned. If it is not equal to any of the values, the else-result is returned.

The else-result is optional. If omitted, it is assumed to be null.

The expression may be of any type, but all of the values must be of the same type as the expression.  The results may be of any type, but they must all be the same.

This function is different from most functions in that the arguments don't get evaluated before the function is called. This allows one to avoid error conditions by testing for the appropriate conditions. 

Case([Priority], "High", 100, "Medium", 70, "Low", 40)

This formula says: if the value in the Priority column is "High", then return 100. If the value in the Priority column is "Medium", then return 70. If the value in the Priority column is "Low", then return 40. Otherwise, return nothing (null).

Also you can use this function for View Colorization. For example: Case([Status], "Overdue", "red", "Completed", "green")

This formula says: if the value in the Status column is "Overdue", then color in red. If the value in the Status column is "Completed", then color in green. Otherwise, don’t apply any specific colorization.

(algorithm, data, encoding)

Text

It is needed for some API.

algorithm: text constant, "MD5", "SHA", "SHA1", "SHA2_256" or "SHA2_512"

data: column or constant of the text type, data for encoding

encoding: text constant, "base64" or "binhex"

(algorithm, key, data, encoding)

Text

It is needed for some API and counts HMAC [ https://en.wikipedia.org/wiki/Hash-based_message_authentication_code ]

algorithm: text constant, "MD5", "SHA", "SHA1", "SHA2_256" or "SHA2_512"

key: column or constant of the text type, key for encoding

data: column or constant of the text type, data for encoding

encoding: text constant, "base64" or "binhex"

(Boolean condition1, <any> result1, ..., <any> else-result)

<same type as result1>

If condition1 is true, evaluates and returns result1, otherwise evaluates and returns else-result. 

Optionally, any number of condition/result pairs may be added before the final else-result.  The conditions are evaluated in sequence until one is found to be true, and then the corresponding result is returned.

The else-result is optional. If omitted, it is assumed to be null.

All conditions must be of type Boolean, and results may be of any type, but they must all be the same type.

This function is different from most functions in that the arguments don't get evaluated before the function is called. This allows one to avoid error conditions by testing for them in an If function.

If([Priority]= "High", 100, [Priority]="Medium", 70)

This formula says: if the value in the Priority column is "High", then return 100. If the value in the Priority column is "Medium", then return 70.

If([Discount]=true, [SubTotal]-[Discount Value], [SubTotal])

This formula says: If the Discount checkbox is on, then subtract the Discount Value from the SubTotal column value and display it. If not, then display the SubTotal value as it is.

(Value, Item1, Item2, … ItemN)

(Single reference column value, Multi-reference column values)

Boolean

Checks if the Value equals one of the values listed in Item1…ItemN.

Also you can find the Single Reference column value among the Multi-reference column values.

In([State], "CA", "IL")

This formula will return true if the "CA" or "IL" value is specified in the State column.

In([Single reference column], [Multi-reference column])

For example you created the "Best Supplier" single reference column keeping best supplier variant and the "Possible Suppliers" multi-reference column, where many suppliers can be selected. If you want to check the presence of the Best Supplier among the Possible Suppliers, use the following formula:

In([Best Supplier], [Possible Suppliers])

This formula will return true if the Best Supplier value is listed among the Possible Suppliers.

(), (Text x), (Text x, Type)

any

This function is used in Call URL action only.

Response() with no arguments returns the "raw" content of the server response - you can use it if the server provides the result in a plain text format.
Response("text") - extracts named data from the response in the XML or JSON format. In case of XML the text should be a valid XPath expression, in case of JSON it's a string in form of "property.property[index].property". The function returns the value as text.
Response("text", Type) - performs as previous one but also tries to convert the result to TeamDesk-specific type.

(name)

Text

This function is used in Call URL action only. It allows to retrieve information kept in a header.

Let’s consider the following header:

X-UA-Compatible: IE=edge

Connection: close

Content-Length: 122

Cache-Control: private

Content-Type: text/html; charset=utf-8

Date: Wed, 02 Dec 2020 09:02:17 GMT

Server: Microsoft-IIS/8.5

X-Powered-By: ARR/2.5

If you use the formula ResponseHeader("Server") , the result is Microsoft-IIS/8.5

()

Number

This function is used in Call URL action for the Error Message formula only.

The Error Message formula has a chance to evaluate response status using ResponseStatus() function. For example: If(ResponseStatus() = 200, “OK”, “FAILED!”) // to Status

(condition)

Boolean

Works in filter formulas only. Calculates the condition if parameter values were supplied or evaluates to true, if not.

Ask([State]=[?State])

This formula says: A user should enter a State value that will be used as a filter criterion.

Formula is used in the Ask the User option.

([Column])

Creates the parameterized search over the column. The parameter is used in combination with the Ask function.

Ask([State]=Parameter([State]))

This formula says: A user should enter a State value that will be used as a filter criterion.

The Parameter([Column]) function can be used instead of the [?Column] condition.

([Column], required)

Creates the required parameterized search over the column. The parameter is used in combination with the Ask function.

Ask([State]=Parameter([State], true))

This formula says: A user must enter a State value that will be used as a required filter criterion.

([Column], "Label")

Creates the parameterized search over the column and allows using the Label instead of the column name. The parameter is used in combination with the Ask function.

Ask([Date of Shipment]=Parameter([Date of Shipment],"Date"))

The "Date" label is displayed instead of the Date of Shipment column name. A user should enter a value in the "Date" field that will be used as a filter criterion.

([Column], “Label”, required)

Creates the required parameterized search over the column and allows using the Label instead of the column name. The parameter is used in combination with the Ask function.

Ask([Date of Shipment]=Parameter([Date of Shipment],"Date", true))

The "Date" label is displayed instead of the Date of Shipment column name. A user must enter a value in the "Date" field that will be used as a required filter criterion.

( “Label”, type)

Creates the parameter of specified name and type without referencing the column. The parameter is used in combination with the Ask function.

Ask(Month([Date])=Parameter("Month", Numeric))

This formula says: A user should enter a Month numeric value. It will be used as a filter criterion and will be searched in the Date column.

( “Label”, type, required)

Creates the required parameter of specified name and type without referencing the column. The parameter is used in combination with the Ask function.

Ask(Month([Date])=Parameter("Month", Numeric, true))

This formula says: A user must enter a required Month numeric value. It will be used as a filter criterion and will be searched in the Date column.

(Duration d)

Duration

Returns the absolute value of d.

Abs(Hours(2.5)) returns 2.5 hrs

Abs(Hours(-2.5)) returns 2.5 hrs

(Number n)

Duration

Returns a Duration representing n days.

Days(5) returns 5 days

(Number n)

Duration

Returns a Duration representing n hours.

Hours(2.5) returns the duration value 2.5 hrs

(Number n)

Duration

Returns a Duration representing n minutes.

Minutes(37) returns the duration value 37 mins

(Duration n, Duration m)

Duration

Returns n modulo m.  (Mod is the same as Rem for positive numbers, but different for negative numbers.)

Mod(Hours(7),Hours(5)) returns 2 hrs

Mod(Hours(-7),Hours(5)) returns 3 hrs

Mod(Hours(7),Hours(-5)) returns -3 hrs

Mod(Hours(-7),Hours(-5)) returns -2 hrs

(Duration n, Duration d)

Duration

Returns a Duration that is the remainder after n is divided by d an integer number of times. (Mod is the same as Rem for positive numbers, but different for negative numbers.)

Rem(Hours(7),Hours(5)) returns 2 hrs

Rem(Hours(-7),Hours(5)) returns -2 hrs

Rem(Hours(7),Hours(-5)) returns 2 hrs

Rem(Hours(-7),Hours(-5)) returns -2hrs

(Number n)

Duration

Returns a Duration representing n seconds.

Seconds(25) returns the duration value 25 secs

(Duration d)

Number

Takes a Duration d, and returns the number of days contained in it.

ToDays([Duration]) takes the duration value "5 days" stored in the duration column and returns the numeric value 5

(Duration d)

Number

Takes a Duration d, and returns the number of hours contained in it.

ToHours([Duration]) takes the duration value "2.5 hrs" stored in the duration column and returns the numeric value 2.5

(Duration d)

Number

Takes a Duration d, and returns the number of minutes contained in it.

ToMinutes([Duration]) takes the duration value "45 mins" stored in the duration column and returns the numeric value 45

(Duration d)

Number

Takes a Duration d, and returns the number of seconds contained in it.

ToSeconds([Duration]) takes the duration value "25 mins" stored in the duration column and returns the numeric value 25

()

Timestamp

Returns a Timestamp representing the current moment.

You may use this function in formula-duration, for example:

Now()-[Date Modified] calculates the duration between the current moment and the moment the record was modified.

(year,month,day,

hour,minute,second)

Timestamp

Returns a Timestamp specified “as is” with the help of parameter numeric values. This timestamp result is converted according to UTC time format.

Timestamp(2013, 03, 25, 16, 10, 11) returns 3/25/2013 4:10:11 PM if the UTC timezone is set and 3/25/2013 9:10:11 AM, if the (UTC-08:00) summer time is set.

()

Timestamp

This function is used in Mobile Device Create/Update action assignments. Timestamp at the moment of using the “Send” button.

Read more in Mobile client application for form filling request.

(Duration x, Duration y)

Duration

Returns the smallest multiple of the duration y which is greater than or equal to the duration x.

Ceil(Hours(7),Hours(5)) returns 10 hrs

Ceil(Hours(-7),Hours(5)) returns -5 hrs

Note that if y is a negative number, the result is closer to negative infinity than x is.

Ceil(Hours(7),Hours(-5)) returns 5 hrs

Ceil(Hours(-7),Hours(-5)) returns -10 hrs

(Number x)

Number

Returns the smallest integer greater than or equal to the number x.

Ceil(4.9) returns 5

Ceil(-4.9) returns -4

Ceil(-0.9) returns 0

(Number x, Number y)

Number

Returns the smallest multiple of y which is greater than or equal to x.

Ceil(7,5) returns 10

Ceil(-7,5) returns -5

Note that if y is a negative number, the result is closer to negative infinity than x is.

Ceil(7,-5) returns 5

Ceil(-7,-5) returns -10

(Time t, Duration x)

Time

Returns the smallest multiple of the duration x which is greater than or equal to the time t.

Ceil(Time(21,15),Hours(2)) returns 10:00 PM (or 22:00)

22:00 is the smallest multiple of 2, which is greater than 21:15

(Duration x, Duration y)

Duration

Returns the largest multiple of the duration y which is less than or equal to the duration x.

Floor(Hours(7),Hours(5)) returns 5 hrs

Floor(Hours(-7),Hours(5)) returns -10 hrs

Note that if y is a negative number, the result is closer to positive infinity than x is.

Floor(Hours(7),Hours(-5)) returns 10 hrs

Floor(Hours(-7),Hours(-5)) returns -5 hrs

(Number x)

Number

Returns the largest integer less than or equal to the number x.  

Note that if x is a negative fraction, the result is closer to negative infinity than x is (compare to function Int).

Floor(4.9) returns 4

Floor(0.9) returns 0

Floor(-4.9) returns -5

Floor(-0.9) returns -1

(Number x, Number y)

Number

Returns the largest multiple of y which is less than or equal to x.

Floor(7,5) returns 5

Floor(-7,5) returns -10

Note that if y is a negative number, the result is closer to positive infinity than x is.

Floor(7,-5) returns 10

Floor(-7,-5) returns -5

(Time t, Duration x)

Time

Returns the largest multiple of the duration x which is less than or equal to the time t.

Floor(Time(21,15),Hours(2)) returns 8:00 PM (or 20:00)

20:00 is the largest multiple of 2, which is less than 21:15

(Duration x, Duration y)

Duration

Returns the multiple of the duration y which is nearest the duration x.

Round(Hours(7.3),Hours(5)) returns 5 hrs

Round(Hours(7.5),Hours(5)) returns 10 hrs

Round (Hours(-7.3),Hours(5)) returns -5 hrs

Round(Hours(-7.5),Hours(5)) returns -10 hrs

Round (Hours(7.3),Hours(-5)) returns 5 hrs

Round (Hours(-7.3),Hours(-5)) returns -5 hrs

(Number x)

Number

Returns the nearest integer to the number x. The fraction .5 rounds up to the next greater integer.

Round(4.9) returns 5

Round(5.5) returns 6

Round(0.4) returns 0

Round(-4.9) returns -5

Round(-0.9) returns -1

(Number x, Number y)

Number

Returns the multiple of y which is nearest to x.

Round(7.63456, 0.01) returns 7.63

Round(0.86590, 0.01) returns 0.87

Round(7.63 , 5) returns 10

Round(-7.63 , 5) returns -10

Round(7.63 , -5) returns 10

Round(-7.63 , -5) returns -10

(Time t, Duration x)

Time

Returns the multiple of the duration x which is nearest the time t.

Round(Time(21,15),Hours(2)) returns 10:00 PM (or 22:00)

22:00 is the multiple of 2, which is nearest the time 21:15

()

Text

Returns ID of the current database; used to construct URLs to application sections.

If you’ll write the following value in the Formula-URL column "https://www.teamdesk.net/secure/db/" & AppId() & "/overview.aspx?t=185594"

The database ID will be inserted in the URL, where the AppId function is indicated.

()

Text

Returns base64 encoded current URL location information.

For example the formula-URL:

URLRoot() & "/edit.aspx?t=" & TableId() & "&back=" & BackURL()

will refer to the Add New Record page of current table in current database

()

Text

Returns either "Desktop", "Tablet", "Mobile" or "TV". The function detects the browser based on user-agent string sent with each request. The string contains only basic information such as platform and browser code and NO information on device's screen size or orientation - thus, the result is one of 4 categories.

Browser() returns either "Desktop", "Tablet", "Mobile" or "TV".

([xxx])

Boolean

[xxx] is a single reference column. The formula returns true if the reference record (selected in the [xxx] reference column) exists and if this record is accessible to a user.

Exists([Project])

[Project] is a single reference column. The formula will return true if the [Project] value refers to the existing record and this record is accessible to a user.

(Text x)

Boolean

Returns true if the argument x is the email address of the database user.

IsUserEmail("linda.smith@abc.com")

This formula will return true if the linda.smith@abc.com value is the email address of the database user.

()

Text

Returns ID of the current record (do not mix with the ID table column); used to construct URLs to database pages.

For example you can create the formula-url working like the "Edit" button. In this case the formula will comprise the RecordId function:

URLRoot() & "/edit.aspx?t=" & TableId() & "&id=" & RecordId()

()

Text

Returns the role name for the user specified as its argument. Called with no arguments, current user is assumed.

For example the Manager role is assigned to Linda Smith.

Role(ToUser("linda.smith@abc.com")) returns the Manager role value.

Role() returns the role of a current user.

()

Text

Returns ID of the current table; used to construct URLs to database pages.

For example you can create the formula-url working like the "Edit" button. In this case the formula will comprise the TableId function:

URLRoot() & "/edit.aspx?t=" & TableId() & "&id=" & RecordId()

(Text t)

User

Converts an email address to a user.

ToUser("linda.smith@abc.com") returns Linda Smith user value. If a user with such an email does not exist, the function returns a blank value.

()

Text

Returns current page's URL in full.

For example URL() returns /secure/db/26134/overview.aspx?t=148688

()

Text

Returns the first part of the URL used to access TeamDesk, including the protocol and the site name.

For example URLRoot() returns https://www.teamdesk.net/secure/db/26134

()

User

Returns the user currently accessing the database. 

You can use the User() function in the view that displays only the records created by the user who is currently viewing the database.

For example [Created By]=User()

(User u)

Text

Returns the user's e-mail address.

UserToEmail([Last Modified By]) returns the email address of the user who last modified a record.

(User u)

Text

Returns the user's full name, first name first for a given user u.

UserToName([Created By]) returns the full name of the user listed in the Created By column. 

(User u, Text fmt)

Text

Returns a given user u's full name. If fmt is "FF" the full name is returned first name first. If fmt is "LF" the full name is returned last name first.

UserToName([Record Owner], "FF"), returns "John, Doe" where the record owner is the user John Doe.

UserToName([Record Owner], "LF"), returns "Doe, John" where the record owner is the user John Doe.

(Number n)

Number

Returns the absolute value of the Number n.

Abs(9.8) returns 9.8

Abs(-9.8) returns 9.8

(Number n)

Number

Arccosine, returns the angle, in radians, whose cosine is the specified by n.

Acos(0) returns 1.57

1.57 ≈ Pi/2

Acos(1) returns 0

Acos(-1) returns 3.14

3.14 ≈ Pi

(Number n)

Number

Arcsine, returns the angle, in radians, whose sine is the specified by n.

Asin(0) returns 0

Asin(1) returns 1.57

1.57≈ Pi/2

Asin(-1) returns -1.57

-1.57≈ - Pi/2

(Number n)

Number

Arctangent, returns the angle in radians whose tangent is a specified by n.

Atan(0) returns 0

Atan(1) returns 0.79

0.79 ≈ Pi/4

(Number x, Number y)

Number

Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point specified by x and y.

Atan(1,2) returns 0.46

(Number n)

Number

Returns the trigonometric cosine of the angle n, specified in radians.

Cos(0) returns 1

Cos(3.14) returns -1

(Number n)

Number

Returns the trigonometric cotangent of the angle n, specified in radians.

Cot(0.785) returns 1

(Number n)

Number

Returns the corresponding angle in degrees for an angle n specified in radians

Degrees(Pi()) returns 180

Degrees(3.14) returns 180

()

Number

This function is used in Mobile Device Create/Update action assignments. It registers GPS coordinates of mobile device when the user clicks “Send” button.

Read more in Mobile client application for form filling request.

()

Number

This function is used in Mobile Device Create/Update action assignments. It registers GPS coordinates of mobile device when the user clicks “Send” button.

Read more in Mobile client application for form filling request.

()

Number

This function is used in Mobile Device Create/Update action assignments. It registers GPS coordinates of mobile device when the user clicks “Send” button.

Read more in Mobile client application for form filling request.

(Number n)

Number

Returns e raised to the nth power, where e is approximately 2.71828182845905 .

Exp(0) returns 1

Exp(2) returns 7.39

Exp(-2) returns 0.14

(Number n)

Number

Returns the fractional part of the Number n.  The result is the same sign as n.  For any Number n, Int(n) + Frac(n) is the same as n.

Frac(2.148) returns 0.148

Frac(-2.148) returns - 0.148

(Number n)

Number

Returns the integer part of Number n.  

Note that if n is a negative fraction, the result is closer to 0 than n is (compare to function Floor).

Int(2.148) returns 2

Int(0.999) returns 0

Int(-2.148) returns -2

Int(-0.999) returns 0

(Number n)

Number

Returns the natural (base e) logarithm of n.

Ln(1) returns 0

Ln(3) returns 1.1

(Number n)

Number

Returns the base 10 logarithm of n.

Log(100) returns 2

(Number n, Number m)

Number

Returns n modulo m.  Mod implements 'clock' arithmetic; it models movement around a clock that is labeled with the numbers 0 to m-1. (Mod is the same as Rem for positive numbers, but different for negative numbers.)

Mod(7,3) returns 1

Mod(7,-3) returns -2

Mod(-7,3) returns 2

Mod(-7,-3) returns -1

()

Number

returns the constant value of PI.

Pi() returns 3.14

Sin(Pi()) returns 0

(Number rate, Number nskip, Number amt)

Number

Calculates the Present Value of a future payment. Rate is the discount rate for one time period. Nskip is the number of time periods before the payment occurs. Amt is the amount of the payment.

PV(0.10,2,222) returns 183.47

(Number rate, Number nskip, Number amt, Number npay)

Number

Calculates the Present Value of a series of future payments. Rate is the discount rate for one time period. Nskip is the number of time periods before the first payment occurs. Amt is the amount of each payment. Npay is the number of payments in the series, spaced one time period apart.

PV(0.10,1,222,2) returns 385.29

(Number n)

Number

Returns the corresponding angle in radians for an angle n specified in degrees.

Radians(180) returns 3.14

Radians(-90) returns -1.57

()

Number

Function without argument returns random number between 0 and 1

Random() returns 0.92 or 0.12 or 0.4 …

(from,to)

Number

Returns random number between "from" and "to" where "to" is not included.

For example you want to create the Formula-Text allowing to assign random colors:

Case(Floor(Random(0,6)),0,"red",1,

"orange",2,"yellow",3,"green",4,"blue",5,"indigo")

(Number n, Number d)

Number

Returns a number that is the remainder after n is divided by d an integer number of times. (Mod is the same as Rem for positive numbers, but different for negative numbers.)

Rem(7,3) returns 1

Rem(7,-3) returns 1

Rem(-7,3) returns -1

Rem(-7,-3) returns -1

(Number n)

Number

Returns the trigonometric sine of the angle n, specified in radians.

Sin(0) returns 0

Sin(1.57) returns 1

(Number n)

Number

Returns the square root of n.

Sqrt(4) returns 2

(Number n)

Number

Returns the trigonometric tangent of the angle n, specified in radians.

Tan(0.785) returns 1

( location, location, [unit] )

Number

Calculates distance between two locations on elliptic Earth. Result is returned in meters, by default or according to the unit provided: "m" for meters, "km" for kilometers or "mi" for miles.

Distance(ToLocation(35.658068, 139.751599),ToLocation(41.836944, -87.684722), "km") returns the distance number 10162

(location)

Number

Extracts latitude.

Latitude(ToLocation(35.658068, 139.751599)) returns number 35.658068

(location)

Number

Extracts longitude

Longitude(ToLocation(35.658068, 139.751599)) returns number 139.751599

(latitude,
longitude)

Location

Constructs the location from a pair of coordinates.

ToLocation(35.658068, 139.751599) returns

(Text x)

Location

Constructs the location from text (comma separated pair of decimal numbers).

ToLocation("35.658068, 139.751599") returns

Boolean a,
Boolean b

Boolean

Returns true if a and b are both true, otherwise returns false.

(true and false) returns false

[Approved by Manager]="Yes" and

[Approved by Technical Specialist]="Yes" and

[Approved by Director]="Yes"

returns true if all three fields contain the value "Yes"

Boolean a,
Boolean b

Boolean

Returns true if either a or b is true, otherwise returns false.

(true and false) returns false

Boolean b

Boolean

Returns the logical negation of b (if b is true, returns false, otherwise returns true).

(not true) returns false
(not not false) returns false
not IsNull([Attachment]) returns true if the Attachment field is not null or empty.

Duration d

Duration

Returns d.

+Days(7) returns 7 days

+Days(-3) returns -3 days

Number n

Number

Returns n.

+5 returns 5

+ - 10 returns -10

Date d,
Duration x

Date

Returns the date that is after date d by the duration x. x is truncated to a whole number of days.

[Registered Date]+[Duration] returns the date you get by adding the Duration column value to the date in the Registered Date column.

ToDate("03/20/2013")+Days(3) returns the date value 3/23/2013

Duration x,
Date d

Date

Returns the date that is after date d by the duration x. x is truncated to a whole number of days.

Days(3)+ ToDate("03/20/2013") returns the date value 3/23/2013

Duration x,
Duration y

Duration

Returns the duration that is the sum of x and y.

Days(7)+Days(3) returns 10 days

Days(7)+Days(-3) returns 4 days

Number m,
Number n

Number

Returns the sum of m and n.

5+2 returns 7

5+( - 10) returns -5

[Cost]+[Margin] returns [Price] value

Duration d,
Time t

Time

Returns the Time that is after Time t by the duration x.

Hours(3)+ ToTimeOfDay("2:03:29 pm") returns time value 5:03:29 PM

Time t,
Duration d

Time

Returns the Time that is after Time t by the duration x.

ToTimeOfDay("2:03:29 pm")+ Hours(3) returns time value 5:03:29 PM

Timestamp t,
Duration x

Timestamp

Returns the Timestamp that is after Timestamp t by the duration x.

[Start Timestamp] + Hours(3) returns the timestamp resulting from adding 3 hours to the value stored in the Start Timestamp column.

Duration x,
Timestamp t

Timestamp

Returns the Timestamp that is after Timestamp t by the duration x.

Hours(3)+[Start Timestamp] returns the timestamp resulting from adding 3 hours to the value stored in the Start Timestamp column.

Duration d

Duration

Returns the arithmetic negative of d.

-Days(7) returns -7 days

-Days(-3) returns 3 days

Number n

Number

Returns the arithmetic negative of n.

-4 returns -4

- -5 returns 5

Date d,
Duration x

Date

Returns the date that is before date d by the duration x. x is truncated to a whole number of days.

#2013-10-01#-Days(4) returns 9/27/2013

#2013-10-01#-Hours(24) returns 9/30/2013

#2013-10-01#-Hours(20) returns 10/01/2013

Number m,
Number n

Number

Returns the difference of m and n.

30-5 returns 25

-5-40 returns -45

0-6 returns -6

[Price]-[Margin] returns [Cost]

Duration x,
Duration y

Duration

Returns the difference of x and y.

Days(5)-Days(2) returns 3 days

Days(1)-Hours(20) returns 4 hrs

Date d,
Date e

Duration

Returns the duration between dates d and e.

[End Date]-[Start Date] returns the duration between the end date and start date values.

ToDate("3/23/2013")-ToDate("3/18/2013") returns 5 days

Time t,
Time u

Duration

Returns the duration between Time t and Time u.

The result may be positive or negative depending on whether t is after or before u.

[End Time]-[Start Time] returns the duration between the end time and start time values.

ToTimeOfDay("2:03:29 pm") - ToTimeOfDay("1:15:20 pm") returns 48.15 mins

Timestamp t,
Timestamp u

Duration

Returns the duration between Timestamps t and u.

[End Timestamp]-[Start Timestamp] returns the duration between the end timestamp and start timestamp values.

ToTimestamp(#2013-09-26#,ToTimeOfDay("8:02:00 AM"))- ToTimestamp(#2013-09-20#,ToTimeOfDay("7:01:00 AM")) returns 6.04 days

Time t,
Duration d

Time

Returns the Time that is before Time t by the duration d.

[Start Time] – [Hours of Preparation] returns the time that precedes the Start Time by the number of hours stored in the Hours of Preparation column.

ToTimeOfDay("10:02:00 AM")-Hours(2) returns 8:02:00 AM

Timestamp t,
Duration x

Timestamp

Returns the Timestamp that is before Timestamp t by the duration x.

[Timestamp of Publication] – [Days for Creation] returns the timestamp that precedes the Timestamp of Publication by the days number stored in the Days of Creation.

ToTimestamp(#2013-09-26#,ToTimeOfDay("8:02:00 AM"))-Days(2) returns the timestamp value 9/24/2013 8:02:00 AM

Number n,
Duration d

Duration

Returns a new Duration that is the given Duration d repeated n times.

3*Days(2) returns 6 days

Duration d,
Number n

Duration

Returns a new Duration that is the given Duration d repeated n times.

Days(2)*3 returns 6 days

Number m,
Number n

Number

Returns the product of m and n.

[Price]*[Quantity] multiplies the Price column by the value in the Quantity column.

3.20*5 returns 16

-2*4 returns -8

-5*-3 returns 15

Duration x,
Number n

Duration

Returns a new Duration that is x divided by n.

Days(6)/3 returns 2 days

Number m,
Number n

Number

Returns m divided by n.

30/2 returns 15

Duration x,
Duration y

Number

Returns the number of times that Duration y divides into x.

Days(1)/Hours(2) returns 12

<any> u,
<any> v

Text

Returns a text value that is the concatenation of u and v. If u or v is not a text value, the function ToText() is automatically used to convert it to text before concatenating it.

[Salutation] & " " & [Last Name] concatenates the values in the Salutation and Last Name column with a space in between them.

Please note, if one of column values concatenated by & is blank, the result will be blank.

"Mr." & " " &"Smith" returns Mr. Smith

"Conference"&"2014" returns Conference2014

Number m,
Number n

Number

Returns m raised to the n-th power.

3^2 returns 9

16^0.5 returns 4

4^-2 returns 0.0625

<any> x,
<any> y

Boolean

Returns true if x is equal to y, otherwise returns false.

x and y must be the same type. 

As with most other functions, null argument values produce a null result, so it is not possible to test for null with this operator. To test for null, use IsNull() instead.

7=5 returns false

5=5 returns true

Days(3)=Days(3) returns true

"ab"="ba" returns false

#2013-09-01#=#2013-01-01# returns false

<any> x,
<any> y

Boolean

Returns true if x is greater than or equal to y, otherwise returns false.  

x and y must be the same type.

For Numbers, the comparison is numerical.
For Durations, the comparison is done using length of time represented.
For Text, the comparison is done by alphabetical sort order.
For Dates and Timestamps, the comparison is done by chronological order.
For Booleans, false is less than true.

7>=5 returns true

Days(9)>=Days(3) returns true

"ef">="abcd" returns true

#2013-09-01#>=#2013-01-01# returns true

If(ToDate([Date Created])>=#2013-09-01#, "New", "Old")

<any> x,
<any> y

Boolean

Returns true if x is greater than y, otherwise returns false.  

x and y must be the same type.

For Numbers, the comparison is numerical.
For Durations, the comparison is done using length of time represented.
For Text, the comparison is done by alphabetical sort order.
For Dates and Timestamps, the comparison is done by chronological order.
For Booleans, false is less than true.

7>5 returns true

Days(9)>Days(3) returns true

"ef">"abcd" returns true

#2013-09-01#>#2013-01-01# returns true

If(ToDate([Date Created])>#2013-09-01#, "New", "Old")

<any> x, <any> y

Boolean

Returns true if x is less than or equal to y, otherwise returns false.  

x and y must be the same type.

For Numbers, the comparison is numerical.
For Durations, the comparison is done using length of time represented.
For Text, the comparison is done by alphabetical sort order.
For Dates and Timestamps, the comparison is done by chronological order.
For Booleans, false is less than true.

4<=6 returns true

Days(4)<=Days(6) returns true

"abcd"<="ef" returns true

#2013-01-01#<=#2013-09-01# returns true

If(ToDate([Date Created])<=#2013-09-01#, "Old", "New")

<any> x,
<any> y

Boolean

Returns true if x is less than y, otherwise returns false.  

x and y must be the same type.

For Numbers, the comparison is numerical.
For Durations, the comparison is done using length of time represented.
For Text, the comparison is done by alphabetical sort order.
For Dates and Timestamps, the comparison is done by chronological order.
For Booleans, false is less than true.

4<6 returns true

Days(4)<Days(6) returns true

"abcd"<"ef" returns true

#2013-01-01#<#2013-09-01# returns true

If(ToDate([Date Created])<#2013-09-01#, "Old", "New")

<any> x,
<any> y

Boolean

Returns true if x is not equal to y, otherwise returns false.  

x and y must be the same type.

As with most other functions, null argument values produce a null result, so it is not possible to test for null with this operator. To test for null, use IsNull() instead.

7<>5 returns true

5<>5 returns false

Days(9)<>Days(3) returns true

"ab"<>"ba" returns true

#2013-09-01#<>#2013-01-01# returns true