Additional Examples of Function Use Cases – TrackVia

This article is an extension of the Complete List of Functions that provides additional examples of various use cases for functions.

Jump to a section:

Text Functions
Number Functions
Date Functions
Child Aggregate Functions

Text Functions

See the Text Functions section of the Complete List of Functions for examples of all Text Functions.

FUNCTION	EXAMPLE CASE	HOW TO GET IT DONE
concatenate()	Combine a ‘First Name’ field, a space, and a ‘Last Name’ field into a Full Name calculated text field.	Syntax: concatenate({First Name}, " ", {Last Name}) Considerations: You can only concatenate fields on the table you’re working on, you can’t concatenate child records fields. Reminder, to reference child records in a formula, you need to use a Child Aggregate Function. Whenever you want to combine data within two fields, you will use the concatenate() function. If you have two fields where one is ‘Track’ and the other is ‘Via’, you will want to use the concatenate() function to put them together in a separate calculated or triggered text field so the result is ‘TrackVia’. You may also want to concatenate multiple fields to create a Record ID. For example, if you have a patient name and you want to include their age in the Record ID, you would use concatenate({firstname}," ",{lastname}, " - ",{age}).
contains()	To determine if an email address is part of a phrase, use the contains function to search for an ‘@’.	Syntax: contains("@", "You can reach them at example@trackvia.com") The function above would return a 1 because the '@’ symbol is within the phrase.
mid()	You have a Single Line field labeled "Field A" and want to grab the first 3 letters.	Syntax: mid({Field A}, 1, 3) The above function would return the letters ‘Fie’.
snip()	You have a field that contains a person's full name and you need a field that only displays the person's first name, you can snip the first name into a Calculated Text field.	Syntax: snip({Full Name}, "", " ") Additional Info: To start at the beginning of a field's value, you will need to utilize a pair of quotes with nothing in between. In this scenario, the end character is a space (the space between a person's first and last name). Note: This formula will only return the first word (First names with 2 words will not bring over both words). There currently is not a way to specify the end of a string of text - an actual character is required. Think of the snip() function as the opposite of the concatenate () function. Concatenate() pulls data with fields together, while snip() stamps only a specific piece of text from another field into a calculated or triggered field.
substitute()	You have a paragraph field that contains the "&" symbol numerous times and you would like to change this to "and" instead.	Syntax: substitute({Field A}, "&", "and")
left()	Return the first 5 digits of a long number	Syntax: left({Long Number}, 5)
right()	Return the last 5 digits of a long number	Syntax: right({Long Number}, 5)
upper()	You have a field labeled "Country" and "usa" is entered into this field, use the below formula to return "USA".	Syntax: upper({Country})
lower()	You are creating item codes and need the Code Prefix in all lowercase letters to be combined with an ID Number.	Syntax: lower({Code Prefix}) & {ID Number})
char_length()	You want to know the number of characters entered into a Single Line field. The field contains the phrase "Working with formulas!"	Syntax: char_length({Single Line Field}) The function above returns "22". Note that the count of characters includes letters, numbers, special characters such as "!", and spaces. In this case, there are 19 letters plus 2 spaces, plus 1 special character.
length()	You want to know the length of text in a Single Line field measured in bytes. The text in the field reads "Español".	Syntax: length({Single Line Field}) If the value of the text field contains "Español", using the length() function would return 8. However, if you were to use char_length(), the result would be 7. Note: While standard English characters typically only take up one byte, non-English characters can take up multiple bytes.
rpad()	You want to ensure that all results in a Single Line field have the same number of characters by adding or removing characters to the right of the field's value. You use rpad() on a text field in which the value is "TrackVia".	Syntax:*rpad({Text Field}, 10, "") returns TrackVia whereas *rpad({Text Field}, 5, "")** returns ckVia
lpad()	Same use case as rpad() example above.	Syntax: *lpad({Text Field}, 10, "") returns TrackVia whereas *lpad({Text Field}, 5, "")** returns Track
reverse()	You want to reverse the characters from a Single Line field with the value "TrackVia".	Syntax: reverse({Single Line Field}) returns aiVkcarT

Number Functions

See the Number Functions section of the Complete List of Functions for examples of all Number Functions.

FUNCTION	EXAMPLE CASE	HOW TO GET IT DONE
ceiling()	You want to round up to the nearest whole number. If you are attempting to determine how many seats you need present at an event, you may use ceiling() to round up to the next whole number if the average number of attendees includes decimals.	Syntax: ceiling(6.1) returns 7
floor()	You want to round down to the nearest whole number. In an instance where you are averaging a number of people, you may use the floor() function to round down from the next highest integer since you cannot count only part of a person!	Syntax: floor(6.1) returns 6

Date Functions

See the Date Functionssection of the Complete List of Functions for examples of all Date Functions.

FUNCTION

EXAMPLE CASE

HOW TO GET IT DONE

convert_tz()

If you are looking to standardize your globally-dispersed employee’s clock in/clock out hours, you may use this function for a better view. In this use case, your desired time zone is "America/Chicago".

Syntax: convert_tz({Start Time}, "America/Chicago")

Note: If using this function alone, you would insert the function into a calculated text or calculated number field type. Because it returns a whole number, it will not be accepted into a date field type.

Child Aggregate Functions

See the Child Aggregate Functions section of the Complete List of Functions for examples of all Child Aggregate Functions.

FUNCTION	EXAMPLE CASE	HOW TO GET IT DONE
childconcatenate()	You would like to see a comma-separated list of products that one of your clients has purchased where the ‘Clients’ table is a parent of the ‘Orders’ table.	Syntax: childconcatenate({Orders}.{Customer Link}.{Product}, ", ")