Formula Building Blocks

A formula can contain references to column values, operators, functions, literal values, or other formulas.

Use any or all of these elements to construct a formula.

• Literals
• Null
• Columns and Variables
• Operators
• Functions
• Conditions

Below is a description of factors that should be considered when creating formulas.

Data and Column Types

If the calculated result of a formula is intended to be stored in a table field, ensure that the resulting data aligns with the field/column type. For instance, a simple formula like 2 * 3 yields a numeric result of 6, which should be stored in a numeric field. It’s not appropriate to store the numeric result in a Date or Timestamp column.

When crafting formulas, strive to use data of the same (or related) type. For instance, you can’t subtract a numeric value from a date, as that lacks meaningful context. On the other hand, subtracting a project’s start date from its end date in the formula [Project End Date] - [Project Start Date] works well.

The same principle applies to the operations you use: not all available operations can be applied to all data types. For instance, you can’t multiply two text strings together or add any value to a valid email address, as these actions are inherently nonsensical.

Data Type Conversion

At times, when creating formulas, you might need to combine two values with different data types. Consider this scenario: to calculate an estimated project budget, you want to multiply the number of planned hours stored in the [Hours Planned] field by the hourly rate represented by the Var[Hourly Rate] variable. Since all variables are treated as text by default, you can’t simply write the formula as [Hours Planned] * Var[Hourly Rate]. You can’t multiply numbers by text.

To successfully perform this operation, you need to convert the variable into a numeric value. In TeamDesk, you achieve this using specific Type Conversion functions. You can find these functions in the Type Conversion section (or within specific data type sections) of the functions list.

The name of each conversion function usually indicates the type to which the data will be converted. Text within brackets specifies the type of source data the function works with. For example, the ToDate(timestamp) function converts timestamps to date format, and ToNumber(text) converts text values or literals to numeric format.

In the example described above, the formula should be written as follows to function correctly: [Hours Planned] * ToNumber(Var[Hourly Rate]).