Text Functions

Text Functions

Click here to download a complete list of formulas.

TrackVia has built-in text functions. The table below describes how these functions can be used in a calculated field's formula and the inputs that the functions require. In your formulas, these would be: 

  • Field name inside curly brackets
  • Text in quotes

Function Description

concatenate({Field A}, " - ", {Field B}, " ", {Field C})
The concatenate() function is used to combine multiple text values into one. You can reference field names or text inside quotes. For example, to combine First Name, a space and Last Name into a Full Name calculated text field, you can use the following formula:

concatenate({First Name}, " ", {Last Name})

Note: Alternatively, the ampersand character (&) can be used as a shortcut to concatenate fields.  For the example of Full Name: {First Name} & " " & {Last Name}

contains("text", {Field A})
The contains() function will return a 1 (one) if the "text" is found in Field A and a 0 (zero) if it does not contain the "text".  For example, to determine if an email address was part of a phrase: contains("@", "You can reach them at example@trackvia.com.") would return 1 because the 'at' symbol is within the phrase.

The contains() function can also be used in conjunction with the if() function to evaluate if a statement is true. For example, the formula if(contains("urgent", {Message}), "yes", "no") will return the word "yes" if the Message field contains the word 'urgent', and "no" if it does not.

ischecked("text", {Field A})
The ischecked() function can be used to identify whether a value is selected in a checkbox field.  It works in a similar manner as the contains() function (above).  It will return a 1 if the specified checkbox option is found in Field A and a 0 if it is not.

The ischecked() function is most commonly used in conjunction with the if() function to evaluate if a statement is true. For example, the formula if(ischecked("Completed", {Status}), "yes", "no") will return the word "yes" if the option Completed is checked in the Status field, and "no" if it is not.

{{OLD}}.{Field Name}
The {{OLD}} Function allows you to display the previous value of a field. You must use a triggered field to use this function and it can only reference the table the field exists in. Use cases for this field include last price an item was sold, previous QA standard, last Qty shipped, last User assigned to a record.

A more advanced use of this function is to record a history or log of every change of a field. This example has a field named Price, and a field named Last Price that uses the OLD function twice: {{OLD}}.{Price} & " " & {Last User} & "\n" & {{OLD}}.{Last Price}

mid({Field A}, Number of start character, Number of characters to return)
The mid() function gives you the ability to return a specific set of characters from another field.  The parameters require stating what number of character to start at (1 is the beginning) and how many characters you want to return.  This is commonly used when creating a unique value (or key).  For example, with this function you can create an ID that contains text from another field that appends to an auto-counter field (unique number).  In other words, you can combine a portion of text from a single line field along with a number field using the mid() function within the concatenate() function (see above). 

So if you have a Single Line field labeled "Field A" and want to grab the first 3 letters, the formula would look like:

mid([Field A}, 1, 3)

Then to combine that with an Auto-counter field labeled "ID Number", you can use the following:

concatenate(mid({Field A}, 1, 3), {ID Number})

snip({Field A}, "start text", "end text")
The snip() function is useful for extracting a section of text out of longer strings of text that have a predictable format.  This is done by specifying a character to start at within the text and then choosing a character to end the snip. 

For example, if you have a field that contains a person's full name - where the first and last names are separated by a space - and you need a field that only displays the first name, you can snip the first name into a Calculated Text field.  Here is an example:

snip({Full Name}, "", " ")

To start at the beginning of a field's value, you will need to utilize a pair of quotes with nothing in between.  The end character is a space, representing the space between a person's first and last name. 

Similarly, to snip only the last name, your formula would look like the following:
snip({Full Name}, " ", "")

This will snip from the space until the end of the text value by using two quotes next to each other. 

Note: this formula will only return the first word (two word first names will not bring over both words). 

substitute({Field A}, "find value", "replacement value")
The substitute() function allows you to identify a character (or multiple) in a value and substitute it with another value.  For example, if you have a paragraph field that contains the "&" symbol numerous times and you would like to change this to "and", you can use the following formula to solve for this:

substitute([Field A}, "&", "and")

left({Field A}, number of characters to return)
The left() function is similar to the mid() function, except you do not need to specify a starting point.  You can return a specific number of characters from the beginning of a text string (the left side).  This requires entering the field name and the number of characters to return in a calculated field.  One example would be to return the first 5 digits of a long number.  The formula would look like:

left({Long Number}, 5)

right({Field A}, number of characters to return)
Similar to the left() function, the right() function allows you to return a specific number of characters from the end of a text string (the right side).  For example, if you need to return the last 4 digits of a long number, you can use the following formula:

right({Long Number}, 4)

upper({Field A})
The upper() function is used to display text values in all uppercase letters in a calculated text field.  For example, if you have a field labeled "Country" and someone enters "usa" in this field, you can use the following formula to return "USA":


lower({Field A})
The lower() function allows you to display text in all lowercase letters.  This is great for making text uniform across all records or when concatenating into a 'code' that requires all lowercase letters.  Here is an example with the lower() function being used with another field that contains a number:

lower({Code Prefix}) & {ID Number}

Note: This example uses the concatenate shortcut - the ampersand character(&) - to combine two fields. 

The null() function can be used on its own or as a result in a logic formula. This function is commonly used in an if() function to return nothing in a field if a condition is not met.

Feedback and Knowledge Base