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 download a complete list of formulas.

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 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:
Item Examples
Field Name{First Name}  [text field]
{Revenue}  [currency field]
{Due Date}  [date field]
Constant 31  [days]
"Dear"  [text salutation]
"2017-10-01"  [fixed date]
Function sum()  [child function]
datedif()  [date function]
if()  [logic function]
Operator

Math:                                           +    -    /    *    ^
Number Comparison:                  =    >    <    >=    <=
Text:                                           &    [concatenate]
                                                  " "     [space]
                                                  ""     [blank]
                                                  "\n"   [line break]


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 zero value.  To avoid this from happening, you can use the if() function to replace a blank value with a zero.  An example with adding two fields together is below:

if( isblank({Base Salary}), 0, {Base Salary}) + if( isblank({Commission}), 0, {Commission})

6.  With Calculated Text fields, values - either other fields within the table, or static text - can be separated with 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 (click to see more)


concatenate()
contains()
mid()
substitute()

Date Functions (click to see more)


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

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

Logic Functions (click to see more)


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

Number Functions (click to see more)


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 (click to see more)


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 or choose one of the functions available in the Formula drop-down menu to utilize the helpful Formula Builder. 

***The IF() function is currently the only available function in the Formula Builder. 

  

1.  The first step is to determine if you are referencing a field name, a static value or both in your condition.  As part of the condition, you can also reference if a field is blank or not (top arrow in image above). 

2.  Once your condition is created and you establish the result if the condition is met and the result if the condition is not met, click the INSERT button to add the formula to the Result Formula box (bottom arrow in image above). 

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 cnn and US to create a web address: "www." & {Company Name} & ".com/" & {Country}. The resulting text would be www.cnn.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"

Blog Articles and Tips & Tricks

Formulas Don't Have to be Scary

Feedback and Knowledge Base