Formula Columns

Together with table columns containing simple values (for example, of the numeric or text type), TeamDesk allows a user to create columns with formula values. The value for such columns is set as a result of configuration or calculation by predefined formulas.

Formula-defined columns are created in the same way as columns of ordinary types (see the Creating new columns section). For columns that contain formulas, you may use the following data types:

Column Type

Formula Description

Formula – Text

This type of columns allows formulas that provide a text result. The formula may contain a text literal, variable or references to columns that store text values.

Formula – Number

This type allows formulas that provide a numeric result: integer, decimal, positive or negative. The formula may contain actual numbers (literals), references to columns that store numeric values or variables that should be converted first using the ToNumber() function. If you need to calculate the Age using the Birthdate value, insert the following formula into the formula-numeric column: Year(Today()) - Year([Birthdate]) - If(DayOfYear(Today()) < DayOfYear([Birthdate]), 1, 0)

Formula – Date

This type allows formulas that provide a date. If in the formula you use a literal or variable, convert it into a date first using the ToDate() function. To retrieve today’s date, use the Today() function.

Formula – Time

This type allows formulas that are used to display time of a day. The result may be created by retrieving time from a Timestamp column or by constructing time with the help of literal values or column references.

Formula – Duration

This type allows formulas that are used to calculate a certain period of time. To calculate a duration formula, you may choose from the list of Duration functions. In addition, a duration formula may be composed by subtracting one value from another:

  • Timestamp from Timestamp
  • Date from Date
  • TimeofDay from TimeofDay

The calculation result may be positive or negative.

Formula – Checkbox

Checkbox formula always provides the ‘Yes’ (check box is selected if the expression is true for the record) or ‘No’ response (check box is not selected if the expression is false).

For example, when tracking project deadlines, the [Project End Date] < Today() formula in the Deadline Expired column, shows whether the team fails to meet a deadline (Yes), or there is still time left for development (No).

Formula – Phone Number

This type allows formulas that are used to make up a phone number out of the area code, phone number and maybe extension. Use text values for formula arguments: literals, variables or column references.

For example, in the List(“-”, [Area Code], [Phone Number], [Ext.]) formula, area code, phone number and extension number values are retrieved from corresponding columns.

TeamDesk parses data from Phone and Formula-Phone columns. The system decorates them as Skype links on desktops and tablets and as links to invoke phone dialer on mobile phones.

Skype requires phone numbers to contain country code. If the column data is missing country code, TeamDesk infers the code from database Language and Locale settings.

When a text is entered and stored in a Phone Number column, it is decorated as a link. Also, there is the tooltip that displays the phone number formatted according to your country rules.

Formula – E-mail Address

This type allows formulas used to create an e-mail address either by entering a textual literal in double quotes directly (for example, info@mywebsite.com) or by drawing up various column values together (for example, List(“@“, [Local E-mail Part], [domain])). Use text values for formula arguments.

In the user mode, the calculated formula result will be represented as a mailto hyperlink used to create a new e-mail message that will be sent to the specified recipient.

Formula – URL

This type of formulas, like formulas for the E-mail Address field, can be made up either by entering a textual literal directly (www.teamdesk.net) or by composing the URL from constituent parts, like variables, literals, column references and other text values.

Additionally, the following functions may be used to construct a URL formula: URLRoot(), BackURL(), AppId(), TableId(), RecordId().

Formula – User

This type of columns allows to provide names of registered database users. The formula may contain column references or text literals converted into the required data format.

Formula-Barcode

Any text value can be displayed as a barcode. The formula-barcode column allows to generate barcodes that can be inserted into the document and scanned.

After a column of a required type has been created, it is necessary to specify the formula itself in the properties of a created column.

To set a new formula:

Click the Setup link at the top right corner of the window.

Select a table you need.

From the setup menu select Columns > Customize existing columns. The system will display the Columns form;

Click the Edit button next to a newly created formula defined column;

In the Formula field set a new formula according to which the value of a column will be set.

Click ‘[’ to list all columns of a table and insert column variables you need. Also you can type first letters of the column name after ‘[’ sign and the columns starting with these letters will be displayed in the dropdown.

Click Ctrl+Space to list function names and select functions you need.

If you need info about the Formula Language Reference, use the click here for help link:

To learn more about working with formulas, see Working with Formulas.

To view a full list of functions used for formula generating, see Formula Language Reference.

You can colorize values stored in the Formula-Text columns. Just use the Colorization checkbox displayed in the Advanced Options. Colorization bases on whether the value contains certain text. More details are described in the Column Value Colorization section.

Next: Formula-XHTML