How to use calculated fields and formulas

Using a Formula enables you to create a database field whose value is the result of a calculation.

The calculations in a formula can be simple (add two values) or complex (multiple nested if() functions that display a result based on various conditions). Click here to see the complete list of functions.

Working with formulas in TrackVia is very similar to working with formulas in other applications such as Excel. In this documentation, we assume that you have some familiarity with functions and formulas.  We focus primarily on providing multiple, diverse examples of applied formulas.

When designing your tables, you can add several 'calculated' field types: Calculated Text, Number, Date, Date Time, Percentage, Currency and Email. Calculated field types allow you to use formulas that reference other fields in the table or fields from a linked table to return a value.




Each formula contains one or a combination of the following elements:

General Guidelines

The calculated field type you use will depend upon the value being returned as a result of the formula.  For example, when using a date function, the result may be a number; in this case a Calculated Number field is recommended. 

You can follow these guidelines when referencing field names and text in a formula.

1.  In the table builder, after adding a calculated field, when clicking within the 'RESULT FORMULA' box for the first time you will be presented with a list of available functions: quotes for a static value, a field in the table, a field from a parent table (contains table and link name also) and the functions in the section below.

2.  Field Names always need to be surrounded by curly brackets.  For example, a field labeled Revenue needs to be referenced as {Revenue}.  You can display the list of available fields by typing the open curly bracket ( { ). 

3.  Static text, either as part of a logical condition or as a result of a formula, needs to be within quotation marks.  For example, when a calculated text field returns a phrase, the result is written as "This number is low."

4.  When returning both static text and data from a field in the table, separate them by an ampersand (&).  Here is an example to display the Revenue field along with static text:

"Our revenue of " & {Revenue} & " exceeded our expectations."

5.  When performing simple math operations - addition, subtraction, multiplication, and division - between fields in a table, if one of the fields in the formula is blank, that particular record's value will currently result in a blank or null value.  To avoid this from happening, you can use the coalesce() function to replace a blank value with a zero.  An example of adding two fields together using this function is below:

coalesce({Base Salary}, 0) + coalesce({Commission}, 0)

6.  With Calculated Text fields, values - either other fields within the table or static text - can be separated by a line break using the "\n" operator.  The line break will only work in Calculated Text Fields.  An example formula combining static text, existing fields, and the newline break is:

"First Day Attendance: "  & {Day1 Attendance} & "\n" & "Second day Attendance: "  & {Day2 Attendance}  

This will output:

First Day Attendance: ##

Second Day Attendance: ##

TrackVia Functions

There are six different types of functions available in a calculated field.  The first four categories below can be used in any table, while the Cross Table Functions are only available where you have tables linked to each other.  See the Cross Table Functions section below for more information. 

Below are the most commonly used text, date, logic and number functions. Click here to see the complete list, or select the category heading to find the description for each function:

Text Functions 

concatenate()
contains()
ischecked()
mid()
snip()
substitute()

Date Functions

now()
today()
date()
*year()
*month()
*day()
*weekday()
*datedif()
*week()
*weekdays()
dateadd()
datesub()

*Returns numbers when used alone; use in a calculated number (or text) field type.

Logic Functions

or()
and()
isblank()
if()

Number Functions

round()
sqrt()
power()
rand()

Cross Table Functions

These functions are used to retrieve data or perform calculations from linked tables.  See the article explaining how to link tables. 

When a table's records are linked to a single record in another table, you can retrieve that parent field's value into a calculated field in the child table.  A parent table's field is referenced in a calculated field using the format: {Table Name}.{Relationship Name}.{Field Name}. Note: These parent fields can be chosen from the available functions list.

When a table's records are linked to multiple records in another table (child table), you can use the Child Functions below:

Child Functions

count()
sum()
average()
max()
childconcatenatedistinct()


Once a calculated field has been added to the Included Fields pane of the Table Designer and named, you can manually enter the field name in the Result Formula box.

Formula Examples

Subtract one number from another

{NUMBER FIELD 1} - {NUMBER FIELD 2}

Concatenate text from two fields

{TEXT FIELD 1} & " " & {TEXT FIELD 2}

For example, you can combine two single-line fields that contain sony and US to create a web address:

"www." & {Company Name} & ".com/" & {Country}

The resulting text would be:

www.sony.com/US

Add or subtract time from a date using dateadd() or datesub()

The format for both functions is: function({DateField}, n, "unit") where

• DateField is a field of type Date or Date Time
• n is the number of units to add or subtract
• unit is one of the following:

'yyyy': Year

‘q’: Quarter

‘m’: Month

‘d’: Day

‘ww’: Week

‘h’: Hour

‘n’: Minute

‘s’: Second


Find the elapsed time between 2 dates using datedif()

datedif({End Date}, {Start Date}, "unit")

where End Date and Start Date are the names of Date or Date Time fields and unit is one of the 8 options listed above.

NOTE: The output is a Number, not a Date. You cannot use a Calculated Date field in this instance.

For example, if the input dates are March 30, 2013 and March 26, 2013, the formula will return 4.

Get the numerical value of the month in a date by using month()

month({DATE})

where DATE is a Date field

NOTE: The output is a Number, not a Date.

For example, If the content of the DATE field is March 26 2010, the month() formula will return a 3 because March is the third month of the year.

Return the current date and time using now()

The output is a Date in a calculated date field and a Date and Time in a calculated text field.

Mathematical Numeric Expressions - handling blank values

There is a difference between zero and null. Zero is a number identifying that no objects are present; null indicates the number of objects is undefined. If a formula uses a null value, the calculation will error because a number cannot be added/subtracted from nothing.

For example, consider two fields: {Number1} : has never had a value entered, and is therefore null
 {Number2} :10
 If we add these 2 values in a calculated number field, the result will be null, i.e. error: {Number1} + {Number2} = null (because null + 10 = null)
Solution: Use the coalesce() function to substitute null values with zero. This function takes two arguments and will return the first non-null argument in its list.

 E.g., coalesce( {Number1}, 0) + coalesce( {Number2}, 0) will now output 10 (using 0 + 10 = 10)

 Because {Number1} is null, the first coalesce () function substitutes 0 into the formula.


Here are some examples of calculated fields which use formulas and functions:

• Calculated Text:   ({Company Name}) & " Inc."
• Calculated Number: month({birthdate})
• Calculated Percentage:  ({2012 Revenue} / {2011 Revenue})-1
• Calculated Currency:  {2012 Revenue} - {2011 Revenue}
• Calculated Date:   dateadd({Purchase Date}, 1, "yyyy")
  
• Calculated Date Time: dateadd({Meeting Date Time}, 1, "h")
• Calculated Email:   "help@" & ({Company Name}) & ".com"