call (855) 387-8288
Trackdrive

Expressions & Functions

Expressions and functions allow for expanded functionality with leads, webhooks, postbacks, and calls. Functions can be nested (one formula within another) to create complex calculations.

Formula Example

Expressions

Expressions are combinations of functions that can be evaluated anywhere token replacement is available. The available expression types are String, Integer, Float, and Time. The function arguments for each type of expression will be coerced to that type when evaluated. EG: Integer type would coerce "15.24" to 15

[!String!  SOME_FUNCTION([first_name])  !!]
[!Integer!  SOME_FUNCTION([amount], [other_amount])  !!]
[!Float!  SOME_FUNCTION([currency_token])  !!]
[!Time!  SOME_FUNCTION([date_token], [another_date_token])  !!]

Example Data

Given the following example data, the expressions below would output the values as indicated by =>

{
  "first_name": "John",
  "last_name": "Smith",
  "birth_year": "1977",
  "yob": 2012,
  "mortgage_amount": 65000,
  "debt_amount": 8283.25,
  "started_at_utc": "2020-06-21 22:35:12 UTC"
}

String Example

Convert function arguments into strings and evaluate the expression.

[!String! ALPHANUMERIC_DASH(CONCAT([first_name], " ", [last_name])) !!]
=> john-smith

Integer Example

Convert function arguments into integers and evaluate the expression.

[!Integer! MIN([birth_year], [yob]) !!]
=> 1977

Decimal Example

Convert function arguments into decimals and evaluate the expression.

[!Float! SUM([debt_amount], [mortgage_amount]) !!]
=> 73283.25

Time Example

Convert function arguments into Time and evaluate the expression.

[!Time! DATE_FORMAT(DATE_ADD([started_at_utc], 86400), "%Y-%m-%d %M-%S") !!]
=> "2020-06-22T18:35:12"

Functions

case(statement)

logic

A CASE statement that allows you to evaluate complex conditional logic.

Argument Required Info
statement Required The statement that will be evaluated. Example:
CASE 'apple' WHEN 'apple' THEN 1 WHEN 'banana' THEN 2 ELSE 3 END
=> 1
CASE()
=> 20

if(statement, output_when_true, output_when_false)

logic

Evaluate a statement and output a value when true or false.

Argument Required Info
statement Required The statement that will be evaluated.
output_when_true Required The value that will be outputted if the statement evaluates to true.
output_when_false Required The value that will be outputted if the statement evaluates to false.
IF(15 < 10, 10, 20)
=> 20

avg(*values)

numeric

Get the average of the passed numeric values.

Argument Required Info
*values Required This function accepts an unlimited number of numeric values.
AVG(1,2,3,4)
=> 2.5

count(*values)

numeric

Count the passed values.

Argument Required Info
*values Required This function accepts an unlimited number of numeric values.
COUNT(1,2,3,4)
=> 4

max(*values)

numeric

Get the largest numeric value from the set of passed arguments.

Argument Required Info
*values Required This function accepts an unlimited number of numeric values.
MAX(1,2,3,4)
=> 4

min(*values)

numeric

Get the smallest numeric value from the set of passed arguments.

Argument Required Info
*values Required This function accepts an unlimited number of numeric values.
MIN(1,2,3,4)
=> 1

round(value, precision)

numeric

Returns float rounded to the nearest value.

Argument Required Info
value Required The value to be rounded. EG:
ROUND(8.8) => 9
ROUND(8.2) => 8
precision Optional The precision to be used. EG:
ROUND(8.75, 1) => 8.8
ROUND(8.2)
=> 8

rounddown(value, precision)

numeric

Returns float rounded down to the nearest value.

Argument Required Info
value Required The value to be rounded. EG:
ROUND(8.8) => 8
precision Optional The precision to be used. EG:
ROUND(1.234, 2) => 1.23
ROUNDDOWN(1.234)
=> 1

roundup(value, precision)

numeric

Returns float rounded up to the nearest value.

Argument Required Info
value Required The value to be rounded. EG:
ROUND(8.8) => 9
precision Optional The precision to be used. EG:
ROUND(1.234, 2) => 1.24
ROUNDUP(1.234)
=> 2

sum(*values)

numeric

Get the sum of the numeric values.

Argument Required Info
*values Required This function accepts an unlimited number of numeric values.
SUM(1,2,3,4)
=> 10

alphanumeric_dash(value)

string

Returns a copy of the receiver with only alphanumeric characters (0-9 and a-z A-Z) and spaces converted to dashes. Leading and trailing spaces are removed.

Argument Required Info
value Required
ALPHANUMERIC_DASH(" ./;!!!]  hello waffle world!@#$%^&*($)  ")
=> "hello-waffle-world"

alphanumeric_underscore(value)

string

Returns a copy of the receiver with only alphanumeric characters (0-9 and a-z A-Z) and spaces converted to underscores. Leading and trailing spaces are removed.

Argument Required Info
value Required
ALPHANUMERIC_UNDERSCORE(" ./;!!!]  hello waffle world!@#$%^&*($)  ")
=> "hello_waffle_world"

base64_decode(value)

string

Returns the Base64-decoded version of str. This method complies with RFC 2045. Characters outside the base alphabet are ignored.

Argument Required Info
value Required
BASE64_DECODE('dGVzdA==')
=> 'text'

base64_encode(value)

string

Returns the Base64-encoded version of bin. This method complies with RFC 2045. Line feeds are added to every 60 encoded characters.

Argument Required Info
value Required
BASE64_ENCODE('text')
=> 'dGVzdA=='

concat(*values)

string
Argument Required Info
*values Required This function accepts an unlimited number of string values.
CONCAT('AB', 'CD', 'EF')
=> "ABCDEF"

contains(search, value)

string
Argument Required Info
search Required Outputs true if [value] contains this string.
value Required The string that will be checked.
CONTAINS('ABCD', 'A')
=> true

date_add(value, seconds)

string

The date_add function adds seconds to a date.

Argument Required Info
value Required The date that will be modified.
seconds Required The seconds that will be added.
DATE_ADD([started_at_utc], 86400)
=> "2020-06-29 11:34:25 -0400"

date_format(value, format)

string

Format a date

Argument Required Info
value Required The date that will be modified. EG '2020-06-29 11:22:57 -0400'
format Required Specifies the format for the date. The following characters can be used.

Date (Year, Month, Day):
%Y - Year with century
%y - year % 100 (00..99)
%m - Month of the year, zero-padded (01..12)
%B - The full month name (``January'')
%b - The abbreviated month name (``Jan'')
%d - Day of the month, zero-padded (01..31)
%j - Day of the year (001..366)

Time
%H - Hour of the day, 24-hour clock, zero-padded (00..23)
%I - Hour of the day, 12-hour clock, zero-padded (01..12)
%P - Meridian indicator, lowercase (``am'' or ``pm'')
%p - Meridian indicator, uppercase (``AM'' or ``PM'')
%M - Minute of the hour (00..59)
%S - Second of the minute (00..59)
%L - Millisecond of the second (000..999)
%N - Fractional seconds digits, default is 9 digits (nanosecond)
%z - Time zone as hour and minute offset from UTC (e.g. +0900)

Weekday
%A - The full weekday name (``Sunday'')
%a - The abbreviated name (``Sun'')
%u - Day of the week (Monday is 1, 1..7)
%w - Day of the week (Sunday is 0, 0..6)

Seconds since the Unix Epoch
%s - Number of seconds since 1970-01-01 00:00:00 UTC.
%Q - Number of milliseconds since 1970-01-01 00:00:00 UTC.
DATE_FORMAT([started_at_utc], "%Y-%m-%d %M-%S")
=> "2020-06-22T18:35:12"

date_parse(value, format)

string

The date_parse function is a natural language date/time parser.

Argument Required Info
value Required The date or natural language expression.

Simple Examples
thursday
november
summer
friday 13:00
mon 2:35
4pm
yesterday
today
tomorrow
last week
next week

Complex Examples
3 years ago
a year ago
5 months from now
7 hours ago
7 days from now
in 3 hours

Specific Dates & Times
22nd of june at 8am
1979-05-27 05:00:00
03/01/2012 07:25:09.234567
2013-08-01T19:30:00.345-07:00
2013-08-01T19:30:00.34-07:00
format Optional
DATE_PARSE("30 days from now")
=> "2020-07-29 15:42:57 UTC"

date_subtract(time, time_or_decimal)

string

The date_subtract function subtracts another timestamp or decimal from the first argument.

Argument Required Info
time Required The timestamp to be manipulated.
time_or_decimal Required The timestamp or decimal that will be subtracted from the first argument time.
DATE_SUBTRACT([current_time_utc], [started_at_utc])
=> "2020-06-29 11:34:25 -0400"

date_to_time_zone(value, time_zone)

string

The date_to_time_zone returns a copy of the receiver in the given time zone.

Argument Required Info
value Required [lead_created_at]
time_zone Required
Examples Time Zones:
DATE_TO_TIME_ZONE([lead_created_at], "Eastern Time (US & Canada)")
=> "2020-07-29 15:42:57"

digest_md5(value)

string

A method for calculating message digests using the MD5 Message-Digest Algorithm by RSA Data Security, Inc., described in RFC1321. MD5 calculates a digest of 128 bits (16 bytes).

Argument Required Info
value Required
DIGEST_MD5('text')
=> 90015098...

digest_sha1(value)

string

A method for calculating message digests using the SHA-1 Secure Hash Algorithm by NIST (the US' National Institute of Standards and Technology), described in FIPS PUB 180-1.

Argument Required Info
value Required
DIGEST_SHA1('text')
=> a9993e36...

digest_sha2(value)

string

A method for calculating SHA256 which works on chunks of 512 bits and returns a 256-bit digest (SHA256)

Argument Required Info
value Required
DIGEST_SHA2('text')
=> ba7816bf8...

downcase(value)

string

Returns a copy of the receiver with all letters converted to lowercase.

Argument Required Info
value Required
DOWNCASE("HELLO World")
=> "hello world"

find(search, value)

string
Argument Required Info
search Required Finds the integer index of [search] in [value]. If [search] is missing in [value] it outputs nothing.
value Required The string that will be manipulated.
FIND('BC', 'ABCD')
=> 2

generate_uuid()

string

Returns a random UUID (Universally Unique Identifier).

GENERATE_UUID()
=> "518e8221-a29e-72c1-a716-486156481234"

left(value, length)

string
Argument Required Info
value Required The string that will be manipulated.
length Required The number of characters to extract starting from the left.
LEFT('ABCD', 2)
=> "AB"

len(value, length)

string
Argument Required Info
value Required The string that will be manipulated.
length Required Outputs the length of the string as an integer.
LEN('ABCD')
=> 4

oauth_access_token(oauth_connection_key)

string

Returns an Access Token for an OAuth Connection.

Argument Required Info
oauth_connection_key Required The key that you chose when creating the connection.
OAUTH_ACCESS_TOKEN('marchex_v2')
=> "sdjf9032fj239fj90sjf90wjf390"

random(min, max)

string

Returns a random number between min and max

Argument Required Info
min Required
max Required
RANDOM(0, 1000)
=> 50

right(value, length)

string
Argument Required Info
value Required The string that will be manipulated.
length Required The number of characters to extract starting from the right.
RIGHT('ABCD', 2)
=> "CD"

seconds_to_hms(seconds)

string

Returns a copy of the receiver formatted as HH:MM:SS

Argument Required Info
seconds Required
SECONDS_TO_HMS(3672)
=> "01:01:12"

split(value, pattern, index)

string

Divides value into substrings based on a delimiter, returning the substring at index.

Argument Required Info
value Required The text that will be modified.
pattern Required The pattern is a String. Its contents are used as the delimiter when splitting str. If pattern is a single space, str is split on whitespace, with leading and trailing whitespace and runs of contiguous whitespace characters ignored.
index Required Index is the index of the split substrings that will be returned. Examples:

SPLIT([full_name], ' ', 2) would return "Smith"
SPLIT("John Andrew Smith", ' ', 0) would return "John"
SPLIT("John Andrew Smith", ' ', 1) would return "Andrew"
SPLIT("2019-01-12", '-', 1) would return "01"
SPLIT("John Smith", " ", 1)
=> "Smith"

strip(value)

string

Returns a copy of the receiver with leading and trailing whitespace removed.

Whitespace is defined as any of the following characters: null, horizontal tab, line feed, vertical tab, form feed, carriage return, space.

Argument Required Info
value Required
STRIP("    hello world   ")
=> "hello world"

substitute(value, search, replacement)

string
Argument Required Info
value Required The string that will be manipulated.
search Required The text that will be replaced.
replacement Required The replacement text.
SUBSTITUTE('green cat, blue cat, yellow cat', 'cat', 'dog')
=> "green dog, blue cat, yellow cat"

substitute_all(value, search, replacement)

string

Replace all occurrences of search with replacement in value.

Argument Required Info
value Required The string that will be manipulated.
search Required The text that will be replaced.
replacement Required The replacement text.
SUBSTITUTE_ALL("green cat, blue cat, yellow cat", "cat", "dog")
=> "green dog, blue dog, yellow dog"

substring(value, start, length)

string

A substring is a range of characters within an existing string.

Argument Required Info
value Required The text that will be modified
start Required The position where to start the extraction. First character is at index 0.
length Required The number of characters to extract. Pass -1 to extract the rest of the string.
SUBSTRING([started_at_offer_time_zone], 0, 9)
=> "2020-09-20"

titleize(value)

string

Returns a copy of the receiver with the first letter of each word capitalized.

Argument Required Info
value Required
TITLEIZE("  hello world  ")
=> "  Hello World  "

to_formatted_number(value)

string

Returns a copy of the receiver as a phone number in the local format.

Argument Required Info
value Required
TO_FORMATTED_NUMBER(" + 1 719-852-2985 ")
=> "(719) 522-0377"

to_integer(value)

string

Returns a copy of the receiver as an integer.

Argument Required Info
value Required
TO_INTEGER(' 15.15 ')
=> 15

to_json(value)

string

Returns a copy of the receiver as a JSON string.

Argument Required Info
value Required
TO_JSON({example: "value"})
=> {"example": "value"}

to_phone_number(value)

string

Returns a copy of the receiver as a normalized international phone number.

Argument Required Info
value Required
TO_PHONE_NUMBER(" 1 (719) 852 2985 ")
=> "+17198522985"

upcase(value)

string

Returns a copy of the receiver with all letters converted to uppercase.

Argument Required Info
value Required
UPCASE("Hello World")
=> "HELLO WORLD"

url_decode(value)

string

Returns a copy of the receiver with all percent (%) signs followed by two hex digits replaced with the corresponding character.

Argument Required Info
value Required
URL_DECODE("hello%20world%2C%20how%20are%20you%3F")
=> "hello world, how are you?"

url_encode(value)

string

Returns a copy of the receiver with all non-alphanumeric characters replaced with a percent (%) sign followed by two hex digits.

Argument Required Info
value Required
URL_ENCODE("hello world, how are you?")
=> "hello%20world%2C%20how%20are%20you%3F"

usa_zip_code(value)

string

Returns a copy of the receiver with only the first 5 digits preserved.

Argument Required Info
value Required
USA_ZIP_CODE("USA 90210 OR")
=> "90210"
Waves
Contact TrackDrive

Questions ?

We’re here to help. Call us and speak with a Voice Marketing Cloud Specialist.

Call : (855) 387-8288
Request A Demo

Request A Demo.

See how you can improve your marketing and the customer experience with the Voice Marketing Cloud.

Request a demo