The list below contains all of the Functions that can be used with calculated and triggered fields in TrackVia.
Jump to a section:
Logic Functions
if() if(condition, true result, false result) |
Enter a condition, what should return in the field if that condition is true, followed by what should return if the condition is false. Example: If there are multiple conditions, you can nest if() functions within each other. Here's the syntax when nesting 3 if() functions (4 different results): Note: Do not forget the final false result needed when nesting if() functions. Also, a closed parenthesis is required at the end of the formula for each if() function used. Last, if() functions read, left to right, so it will assess the first condition, and if the condition is met, then it will opt directly to the True Result, and not meet the False Result. If you have a nested if() function where the False result is your second condition, you will want to make sure your conditions go in order from Left to Right. |
or() or(condition A, condition B, condition C) |
When writing a formula that requires "or" logic (only one or more condition needs to be met), you can enter each within the or() function. Example: or({Price}=10, {Price}=20, {Price}=30) |
and() and(condition A, condition B) |
When writing a formula that requires multiple conditions to be met, you can enter each within the and() function. Example: and({City}="Denver", {State}="CO") |
isblank() isblank({Field Name}) |
This function can be used in a logic formula when you need to determine if a field does not have a value (is blank or null). Example in an if() function: Note: An empty pair of quotation marks ("") indicates to leave the field blank |
not()
|
The not() function serves to invert any other function or logical condition in a formula. In other words, it reverses the argument. This function is commonly wrapped around the isblank() and ischecked() functions (above) to determine if a field is not blank. For example, the not() function is convenient if you want to quickly switch your logic around in an if() function: |
null() |
The null() function can be used on its own or in a logic formula. This function is commonly used with an if() function to return nothing in a field if a condition is not met. Note: null() is NOT the same as using the double quotations ("") as the false condition. Using "" will return a result to field that appears as empty, but using null() will return nothing, leaving the field blank. For example, if you wanted to check a product's price and output the word "Sell" if it is greater than 50, or leave the field blank if the price is 50 or below, you would use the formula: |
coalesce()
|
The coalesce() function serves to substitute null values with another value for arithmetic expressions. You will want to use the coalesce() function whenever you wish to add two values where one may contain a null value so you can replace it with a ‘0’ instead. For example, if you want to add Field1 and Field2 together, even if one of those two values is null, you could use the function below:
|
Text Functions
concatenate() 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: |
contains() 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: The contains() function can also be used in conjunction with the if() function to evaluate if a state is true. For example, the formula: |
ischecked() ischecked("Checkbox Option", {Checkbox Field Name}) |
This function can be used with the if() function when referencing a Checkbox field to determine whether a Checkbox Option is checked. For example, if you wanted to check whether Option 1 of a Checkbox field was checked, and if so, output "Yes", otherwise, "No", you would use the formula: |
{{OLD}} |
{{OLD}} allows you to display the previous value of a field. This function is particularly useful when you want to know the history of another field, or auto-capture the date something changes. See the Examples of the {{OLD}} function article for more information. |
mid() mid({Field A}, Number of start character, Number of |
The mid() function gives you the ability to return a specific set of characters from another field. The parameters require stating what number of characters to This is commonly used when creating a unique value (or key). For example, with So if you have a Single Line field labeled "Field A" and want to grab the first 3 letters, the formula would look like: 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() 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 For example, if you have a field that contains a person's full name and you need a 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. In this scenario, the end character is a space (the Note: this formula will only return the first word (two word first names will not bring over both words). There is currently not a way to specify the end of a string of text - an actual character is required. |
substitute() substitute({Field A}, "text1", "text2") |
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() 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: |
right() 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 beginning of a text string (the right side). For example, if you need to return the last 4 digits of a large number, you can use the following formula: right({Large Number}, 4) |
upper() 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 field labeled "Country" and someone enters "usa" in this field, you can use the following formula to return "USA": upper({Country}) |
lower() lower({Field A}) |
The lower() functions 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 in conjunction with another field that contains a number: lower({Code Prefix}) & {ID Number}) |
char_length() char_length({Field A}) |
The char_length() function is used to determine the number of characters in a field. For example, if you have a text field with the string "Working with functions!", you can use the following: char_length({Text Field}) |
length() length({Field A}) |
Similar to char_length(), the length() function displays the length of a field, measured in bytes, using the following syntax: length({Text Field}) In this case, 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 |
rpad() rpad({Field A}, N, "Character for padding") |
The rpad() function is used to ensure all results have the same number of characters, by adding or removing characters to the right of a field's value, where N specifies the number of characters to return. Below is an example of using rpad() on a text field in which the value is "TrackVia": Sample Formulas: |
lpad() lpad({Field A}, N, "Character for padding") |
Similar to rpad(), lpad() will add or remove characters to the left of a field's value. Using lpad() on a field containing the value of "TrackVia" would look like lpad({Text Field}, 10, "*") returns **TrackVia |
reverse() reverse({Field A}) |
Reverses the characters from a specified field. For a field with the value of "TrackVia", the formula would look like the following: reverse({Text Field}) returns aiVkcarT |
trim() trim({Field A}) |
Removes any leading or trailing spaces from the value of a field. trim({Text Field}) |
Number Functions
distance() distance({location1},{location2},”mi”) |
If you have two location fields or want to always capture the distance between one coordinate and another location in a record, you can use the following function in a Calculated or Triggered Number field: distance({location1},{location2},”mi”) In this example, the first two inputs to the distance() function are Location fields (latitude and longitude) in the table. The last input can be "mi" for miles or "km" for kilometers. |
abs() abs(number) |
Absolute value of a number. abs(-1) returns 1 |
exp() exp(x) |
Exponential of x (e to the power of x) exp(1) returns 2.718... |
ln() ln(x) |
Natural logarithm of x (base e) ln(2) returns 0.693... |
log() log(base_or_x, x) |
Logarithm of x (specified base) log(3, 2) returns 1.584... |
log10() log10(x) |
Common logarithm of x (base 10) log10(2) returns 0.301... |
mod() mod(number, modulus) |
Returns the remainder after dividing number by modulus mod(7, 2) returns 1 |
power() power(x,y) |
x to the power of y power(2, 2) returns 4 |
rand() |
Dynamically returns a random number between 0 and 1 |
round() round({Number}) |
Rounds a number to given digits after the decimal point. round(4/3, 3) returns 1.333 |
sign() sign(x) |
Returns 0 if x is blank or zero, -1 if x is negative, and 1 if x is positive. sign(1) returns "positive" |
sqrt() sqrt(x) |
Square root of x sqrt(64) returns 8 |
sin() sin(x) |
Sine of x sin(2) returns 0.909... |
cos() cos(x) |
Cosine of x cos(2) returns -0.416... |
tan() tan(x) |
Tangent of x tan(2) returns -2.185... |
asin() asin(number) |
Inverse Sine of a number asin(1) returns 1.570... |
acos() acos(number) |
Inverse Cosine of a number acos(1) returns 0 |
atan() atan(number) |
Inverse Tangent of a number atan(1) returns 0.785... |
atan2() atan2(x, y) |
Arc tangent of the two variables x and y atan2(1, 2) returns 0.463... |
degrees() degrees(radians) |
Number of degrees in an angle of x radians degrees(π/2) returns 90 |
radians() radians(degrees) |
Number of radians in an angle of x degrees radians(90) returns π/2 (1.570...) |
pi() |
The constant pi, 3.14159... (takes no inputs) |
ceiling() ceiling(x) |
Rounds up to the next integer. ceiling(6.1) returns 7. |
floor() floor(x) |
Rounds down to the closest integer. floor(6.1) returns 6. |
Child Aggregate Functions
childconcatenate() childconcatenate({child field reference}, "delimiter") |
The childconcatenate() function is used to combine multiple values from the linked records' field in a child table. After selecting the childconcatenate() function in the list of options presented in the Result Formula box, you will see there are two parts that need to be entered within the function: the reference to the child name (which you can also find in the list) and the delimiter (the character you would like to use to separate each child record's value), which needs to be entered within quotation marks.
childconcatenate({Orders}.{Customer Link}.{Product}, ", ") |
childconcatenateorderby() childconcatenateorderby({child field reference}, "delimiter", "Sort") |
childconcatenateorderby() works the same as the childconcatenate() function above, except it will sort all records in a specified order. There are two options for sort: ASC (ascending) and DESC (descending). For example, if childconcatenate({Item Type}.{Link to Item Type}.{Make},”, ") returns: "Samsung, HP, Dell, Mac, Dell", use childconcatenateorderby({Item Type}.{Link to Item Type}.{Make},”, ","ASC") to return the same values sorted from A-Z: "Dell, Dell, HP, Mac, Samsung" |
childconcatenatedistinct() childconcatenatedistinct({child field reference}, "delimiter") |
childconcatenatedistinct() works the same as the childconcatenate() function above, except it will only return the unique values from the child table's field. For example, if the results from childconcatenate() return "Dell, Mac, Samsung, Dell, HP", the childconcatenatedistinct() function will only return "Dell, Mac, Samsung, HP". |
childconcatenatedistinctorderby() childconcatenatedistinctorderby({child field reference}, "delimiter", "Sort") |
childconcatenatedistinctorderby() works the same as the childconcatenateorderby() function above, except it will only return the unique values from the child table's field, and it will sort all records in a specified order. For example, if childconcatenatedistinct({Item Type}.{Link to Item Type}.{Make},”, ") returns: "Samsung, HP, Dell, Mac", use childconcatenatedistinctorderby({Item Type}.{Link to Item Type}.{Make},”, ","ASC") to return the same values sorted from A-Z: "Dell, HP, Mac, Samsung" |
count() count({child field reference}) |
The count() function requires choosing a reference to a field in the child table. For each record in the parent table, it will return the number of records linked where the field chosen is not blank. In order to return a count for every child record linked to a parent record, you can utilize the field that is linking the two tables together. Here is an example: count({Orders}.{Customer Link}.{Customer Link}) |
countdistinct() countdistinct({child field reference}) |
This function works the same as the function above, except it will only return the unique values from the child table's field. |
sum() sum({child field reference}) |
The sum() function allows you to add values from linked child records where each value in the field referenced is a number, currency or percent. For example, if you would like to display the total amount a customer has spent from multiple orders, you can use the following: sum({Orders}.{Customer Link}.{Total Amount}) |
average() average({child field reference}) |
Similar to the sum() function, the average() function is used to return the average value from linked child records where each value in the field referenced is a number, currency, or percent. It sums the values and divides it by the number of linked child records. The following can be used to return the average amount a customer spends per order: average({Orders}.{Customer Link}.{Total Amount}) |
max() max({child field reference}) |
Returns the maximum value. max({Parent Table}.{Link to Parent}.{Child Field}) |
min() min({child field reference}) |
Returns the minimum value. min({Parent Table}.{Link to Parent}.{Child Field}) |
stdev() stdev({child field reference}) |
Returns the sample standard deviation. stdev({Parent Table}.{Link to Parent}.{Number}) |
stdevp() stdevp({child field reference}) |
Returns the population standard deviation. stdevp({Parent Table}.{Link to Parent}.{Number}) |
Date Functions
now() |
The now() function can be entered on its own in a Calculated Date (or Text) field to show the current date (and time in Text) or it can be used to reference the current date in a formula. In a Calculated Date field, it will display "Today". Note: You cannot use this function on its own in a Calculated Date field. The result is a number, which is not accepted on its own in a date field type. Note: The now() function is not that different from the today() function (see below). Depending on the field type, the now() function will return today’s date with the addition of the current time if you utilize this formula within a calculated or triggered date time field. |
today() |
Returns the current date.
Note: You can utilize the today() function to stamp today’s date within a field. The format of the date will vary depending on the field type, and can be extremely helpful when comparing two dates (see dateif() function below). |
date() |
Returns only the date from a date and time field. |
year() |
You can return the year of a date by placing the name of a date field (or a specific date in quotes) within the year() function. This will return a 4 digit number. For example, to return someone's birth year, you can write year({Birthdate) in a Calculated Number field. Note: You cannot use this function on its own in a Calculated Date field. The result is a number, which is not accepted on its own in a date field type. |
month() |
The month() and day() functions can be used in a similar manner. They will return numbers, i.e. "1" for "January" and "9" for the 9th day of the month. If you wish to stamp only the month’s value from a date field, you will use the month() function. Note: You cannot use this function on its own in a Calculated Date field. The result is a number, which is not accepted on its own in a date field type. |
day() |
See above. Note: You cannot use this function on its own in a Calculated Date field. The result is a number, which is not accepted on its own in a date field type. Note: You will want to use the day() function whenever you are looking for the number of the day within a certain month (varying between 1-31). You will use this function in conjunction with a date field so it can successfully pull the number of the day. |
weekday() |
The weekday() function will return a number between 1 through 7 where Sunday is "1" and Saturday is a "7". Note: You cannot use this function on its own in a Calculated Date field. The result is a number, which is not accepted on its own in a date field type. |
dateadd() |
You can use the dateadd() and datesub() functions in a Calculated Date field to add or subtract days, hours, etc. from another date in your table. Whenever you wish to add a specific number of days, weeks, months, etc. to a date, you will use a dateadd() field. Both functions require the syntax datesub({DateField}, n, "unit") where: - DateField is a field of type Date or Date and Time "yyyy": Year One example use case would be to determine an expected ship date for an order if you generally ship two days after the order date. |
datesub() | See above. Whenever you wish to subtract a specific number of days, weeks, months, etc. to a date, you will use a datesub() field. For example, if you are looking to see what date it was 6 months ago from today, you could use datesub({today},6,”m”) |
datedif() |
This function can be used to determine the number of months, days, hours, etc. between two dates or date and times. If you’d like to know how many months it’s been since a date from a few years ago, you would use the datedif() function and use “m” as the unit. You can reference date fields or static dates (i.e. "2013-01-01") using the syntax: datedif({End Date}, {Start Date}, "unit") Please see the dateadd() section above for the available units. 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. |
convert_tz() convert_tz({Date and Time}, "Desired Time Zone") |
This function will convert a date and time field from one time zone to another. Please reference the Available Time Zones article for a complete list of available time zones. 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. |
dayname() dayname({Date}) |
This function will display the day of the week (Sunday, Monday, etc.) for a specified date field. |
dayofweek() dayofweek({Date}) |
This function will show the numeric value (1-7) of the day of the week from a date field type. Sunday is assigned the default value of 1. |
dayofyear() dayofyear({Date})) |
Expanding on dayofweek(), the dayofyear() function outputs the numeric value of the date entered. The result will be between 1-366. |
last_day() last_day({Date}) |
Returns the last of the month of a given date field. When the Date selected is June 6, 2015, last_day({Date}) returns June 30th, 2015. |
hour() hour({Date and Time}) |
When applied to date and time field type, this function displays the hour of the day as a numeric value between 0 and 23. This is similar to how military time works with 0 being equal to 12AM. |
minute() | Returns the number (0-59) that corresponds to the minutes for a date and time field. Use in a Calculated Text or Calculated Number field. When {Date and Time} is 06/15/2017 04:15pm, minute({Date and Time}) returns 15. |
monthname() monthname({Date}) |
Displays the name of the month from a date field. When the Date selected is June 6, 2015, monthname({Date}) returns June. |
quarter() quarter({Date}) |
Returns the numeric value (1-4) of a date field. When the Date selected is June 6, 2015, quarter({Date}) returns 2 |
weekdays() weekdays({Start Date} , {End Date}) |
Returns the number of weekdays between a start and end date. weekdays(06/01/2015, 06/27/2015) returns 20. 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, itwill not be accepted into a date field type. |
week() week({Date}) |
This function returns the week number of a date. You can reference date fields or static dates (i.e. "2013-01-01") using the syntax: week({DateField}) This function can also specify the start date or day for the 1st week of the year. You can add a 2nd part to this function called the "mode"; i.e. week(date,[mode]). For more information on the various [mode] options refer to the table under the Date Functions section of the Additional Examples of Function Use Cases article. 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. |
The date a record is created or updated can be used in a formula by referencing the field name in all uppercase letters. Example: |
Comments
0 comments
Please sign in to leave a comment.