Creating Formulas
Formulas can be inserted into the specific formula fields that are found in various forms of the database setup area. When you create formulas for certain columns, the result is calculated for each record in the table.
At the bottom of the formula entry field there are hints how to choose and insert building blocks into the entry field. Click ‘[’ to list columns, click Ctrl+Space for function names. If you need info about the Formula Language Reference, use the click here for help link.
Formula editor performs syntax highlighting, brace matching. Also it provides suggestions and auto-completion for column and function names.
Basic syntax validation and report malformed pieces of code are highleted in red. Comments are green, known function names are blue, constants are dark red.
Find below a description of aspects that should be taken into account when formulas are created.
Data and Column Types
If the calculated formula result is stored in a table field, make sure that resulted data correlate with the field/ column type: for example, a simple 2*3 formula produces a numeric result 6 which should be stored in a numeric field – you cannot have the numeric result stored in the Date or Timestamp column (though, in some cases data conversion may help).
When writing formulas, try to use data of the same (or correlated) type. That is, you cannot subtract a numeric value from a date, as that does not make sense, while subtracting project start date from the project end date in the [Project End Date] – [Project Start Date] formula will work well.
The same applies to operations that you use: not all of available operations may be applied to all types of data. For example, you will not be able to multiply two text strings or add any value to a valid e-mail address, as this is simply pointless.
Sometimes when creating formulas, you may need to pull together two values that have different data types. For example, to calculate an approximate project budget, you want to multiply the number of planned hours stored in the [Hours Planned] field by the hourly rate expressed by the [Hourly Rate] variable. Since all variables are treated as text, you cannot write the formula simply as [Hours Planned] * [Hourly Rate] – numbers cannot be multiplied by text.
To run this operation successfully, you will need to convert the variable to a numeric value. In TeamDesk this is done with the help of specific Type Conversion functions. These functions can be found under the Type Conversion section (or under specific data type sections) of the functions list.
The name of the conversion function usually says to what type the data will be converted; text in brackets shows with what source of data the function works. For example, ToDate(timestamp) function is used to convert timestamps to the date format, ToNumber(text) – to convert text values or literals to numeric format, and so on.
The example, described above, should be written in the following way to work properly: [Hours Planned] * ToNumber([Hourly Rate]).
Operation Sequence
If your formula contains more than one operation, for calculating the result a standard algebraic notation is used. Here you should take into account a standard priority of operations. To calculate the priority, you may use the following table:
|
Operator |
Priority |
|
Parenthesis |
Highest priority |
|
NOT logical operator |
|
|
Power operator |
|
|
Multiplication and division |
|
|
Addition, subtraction and concatenation |
|
|
Comparison operators |
|
|
AND logical operator |
|
|
OR logical operator |
Lowest priority |
The following rules should be taken into consideration as well:
- Unary operators, like -, + or not have the highest priority (for example, in the 10*-5 formula, first 5 will be converted to -5, and then the multiplication will be carried out).
- Simple operations of the same priority are performed from left to right. In the 2+3-4 formula, first 3 will be added to 2, and then 4 will be subtracted from the sum result.
- Operations of different priority will be performed in the order of their priority correspondingly. In the 3*5 – 4/2 formula, first 3 will be multiplied by 5, then 4 will be divided by 2, after which two resulting values will be summed.
- If you use parentheses, operations enclosed in parentheses will be performed first. If you use nested parentheses, for example, 10 - (2*(5+2)), then the operation in the deepest parentheses – (5+2) – will be performed first.
Formula Errors
Creating formulas, you will most probably come across various types of errors: like syntax errors when the formula is not arranged correctly or unaccepted characters are used; validation errors, like in cases when formula refers to incorrect fields; or evaluation problems.
Basic syntax validation and report malformed pieces of code are highlighted in red. Messages about errors that your formula may contain are displayed below the formula entry field: if the system detects a specific incorrect formula part, it marks such a part with the ^ character pointing to the erroneous part (see example below):
Some errors (like division by zero) cannot be tracked at the stage of formula creation. Such errors are revealed when formulas are calculated and should be eliminated as soon as errors are found.