Formulas allow for expanded functionality within lead, tier, channel and alias rules. Use formulas to create a custom rule, data field or alias value. Formulas can be nested (one formula within another) to create complex calculations. When using formulas in any rule, Boolean (True/False) formulas must be used. When the result is 'True', the rule will pass. When the formula results in 'False', the rule will fail.
Using a single Formula:
Formulas always begin with '[!' and end with '!]'. Formula names are located within these symbols and are case insensitive. The [! and !] only go around the outer part of the formula. This is to distinguish a formula block from a token block. So, in an alias formula, you can do stuff like: [=Token=] [!Formula!] [=token=] [=!Formula!=].
Nesting Formulas within a Formula:
When nesting a formula within another formula, only the outermost formula should have the [! and !] symbols. For example: [!GreaterThanOrEqual(GetAge([=DateOfBirth=]), 18)!]
Using Tokens within a formula:
Tokens are allowed within formulas so that dynamic lead values may be used within a formula. Tokens must begin with '[=' and end with '=]'. Click on the Tokens link provided on the page for available tokens.
Pre-Built Formulas
Here's a list of pre-built formula combinations for commonly used calculations. When using these, be sure to replace the tokens with the tokens in your account.
Split a Single Value into Two Values
This formula will split the first and last name into two values based on a space separating the first and last name.
Split off First Name: [!Substring([=fullname=], "0", IndexOf([=fullname=], " "))!]
Split off Last Name: [!Substring([=fullname=], Add(IndexOf([=fullname=], " "), 1))!]
Splitting a Date
This is the value being posted in. 01/01/2013 You want to separated this value into three (3) separate values. You can split the date up by using the string formulas below. You'll want to create three (3) calculated data fields. You'll also want to make sure you replace the token in the formula to match the data field in your system.
Split Month: [!Substring ([=Date=], 0, 2)!]
Split Day: [!Substring ([=Date=], 3, 2)!]
Split Year: [!Substring ([=Date=], 6, 4)!]
Splitting a Phone Number
This is the value being posted in. 8013316945 You want to separated this value into three (3) separate values. You can split the phone number up by using the string formulas below. You'll want to create three (3) calculated data fields. You'll also want to make sure you replace the token in the formula to match the data field in your system.
Area Code: [!Substring ([=Phone=], 0, 3)!]
Phone Prefix: [!Substring ([=Phone=], 3, 3)!]
Phone Suffix: [!Substring ([=Phone=], 6, 4)!]
Formatting a Date
This formula is used when you need to send a date but your buyer needs the date in a particular format and you don't collect the data in that format. This formula uses the current date and time from LinkTrust and formats the date to be Month/Day/Year Hours:minutes:seconds. (10/24/2014 12:34:25) The following formula was created to remove the AM/PM and use Military time when sending the lead to the buyer.
[!DateFormat("MM/dd/yyyy HH:mm:ss", [=DateTimeSubmitted=])!]
Compare two dates
This formula allows you to calculate whether or not a lead is valid by comparing two dates. In the example below, the lead is valid -90 days to the current date and 270 days post the current date.
[!And(GreaterThanOrEqual([=DateTimeSubmitted=], DateAdd(d, -90, "[=_month=]/[=_day=]/[=_year=]")), LessThanOrEqual([=DateTimeSubmitted=], DateAdd(d, 270, "[=_month=]/[=_day=]/[=_year=]")))!]
Scrubbing a Phone Number
This formula will remove all parentheses, dashes, and spaces from a phone number.
[!Replace("-", "", Replace("(", "", Replace(")", "", Replace(" ", "", [=_Phone=]))))!]
Available Formulas
All the formulas you can use are explained below, separated by category.
Math Formulas
Use math formulas to calculate mathematical equations where a numerical result is required. These formulas must contain numbers only, no letters or special characters are allowed.
[!Add(x, y)!]
x – Must be a number
y – Must be a number
Returns x + y (x plus y)
Example 1: I want to add two numbers being posted into LinkTrust. Number1={Value_Posted_In} + Number2={Value_Posted_In} = {New Value}
The following formula would look like this: [!Add([=Number1=], [=Number2=])!]
Example 2: You can also add a number being passed in to a static number as well. Number1={Value_Posted_In} + a static value of 3 = {New Value}
The formula would look like this: [!Add([=Number1=], 3)!].
[!Subtract(x, y)!]
x – Must be a number
y – Must be a number
Returns x - y (x minus y)
Example 1: I want to subtract two numbers being posted into LinkTrust. Number1={Value_Posted_In} - Number2={Value_Posted_In} = {New Value}
The following formula would look like this: [!Subtract([=Number1=], [=Number2=])!]
If the result is negative, LinkTrust will display the value in the following format: -1, -10, etc...
Example 2: You can also subtract a number being passed in to a static number as well. Number1={Value_Posted_In} - a static value of 3 = {New Value}
The formula would look like this: [!Subtract([=Number1=], 3)!].
[!Multiply(x, y)!]
x – Must be a number
y – Must be a number
Returns x * y (x multiplied by y)
Example 1: I want to multiply two numbers being posted into LinkTrust. Number1={Value_Posted_In} * Number2={Value_Posted_In} = {New Value}
The following formula would look like this: [!Multiply([=Number1=], [=Number2=])!]
If the result is negative, LinkTrust will display the value in the following format: -1, -10, etc...
Example 2: You can also multiply a number being passed in to a static number as well. Number1={Value_Posted_In} - a static value of 3 = {New Value}
The formula would look like this: [!Multiply([=Number1=], 3)!].
[!Divide(x, y)!]
x – Must be a number
y – Must be a number
Returns x / y (x divided by y)
NOTE: Dividing By Zero will produce an error when the formula is evaluated.
Example 1: I want to divide two numbers being posted into LinkTrust. Number1={Value_Posted_In} / Number2={Value_Posted_In} = {New Value}
The following formula would look like this: [!Divide([=Number1=], [=Number2=])!]
If the result is negative, LinkTrust will display the value in the following format: -1, -10, etc...
Example 2: You can also divide a number being passed in to a static number as well. Number1={Value_Posted_In} / a static value of 3 = {New Value}
The formula would look like this: [!Divide([=Number1=], 3)!].
[!Round(x, places)!]
x – Must be a number
places – Must be a number
Returns x rounded to the specified number of places
Example: My division formula is returning a value of 1.25477565121. I want my results to be rounded out to 3 places so the results look like this - 1.255.
The formula would look like this: [!Round([=Number1=], 3)!]
Formatting Formulas
Formatting formulas are used to change the format of values.
[!CurrencyFormat (money)!]
money – Must be a number
Returns money formatted as a USD currency value
[!DateFormat(format, date)!]
format – Must be a valid format string. See MSDN for details.
date – Must be a valid date
Returns date formatted as specified by format
Date Formulas
Date formulas must be used on date data fields or values. these functions allow for the customization of a date or a calculation based on a date.
[!DateAdd(unit, amount, date)!]
unit – Must be one of the following values:
--> "d" for days
--> "m" for months
--> "y" for years
--> "h" for hours
amount – Must be a whole number
date – Must be a date
Returns date + the specified amount of unit
[!GetNextBusinessDay(date)!]
date – Must be a valid date
Returns date but if date is Saturday or Sunday it returns the following Monday
Note: It returns the same date entered if it is Monday - Friday
[!GetAge(date)!]
date – Must be a valid date, usually a birth date
Returns the age in years. If the date is in the future then a 0 is returned. The age is based on today's date in the configured time zone of the partner's account.
[!LengthOfTime(fromdate, todate, format)!]
This formula may be used to calculate the length of time been between two dates. For instance, how much time has passed from one surgery date to another surgery date? You may format the response to be in days, months or years. If you wish to include hours, minutes or seconds in the value, follow Microsoft's custom time span formatting rules found here.
fromdate - Must be a valid date
todate - Must be a valid date
Examples:
[!LengthOfTime([=DateTimeSubmitted=], "10/15/15", "d")!] - Displays the number of days between two dates.
[!LengthOfTime([=DateTimeSubmitted=], "10/15/15", "m")!] - Displays the number of months between two dates.
[!LengthOfTime([=DateTimeSubmitted=], "10-15-15", "y")!] - Displays the number of years between two dates.
[!LengthOfTime([=DateTimeSubmitted=], "10-15-15", "d\:%h\:%m\:s")!] - Displays the number of days : number of hours : number of minutes : number of seconds between two dates.
String Formulas
String formulas can be used to modify or analyze alphanumeric values.
[!Substring(string, startIndex, [length])!]
string – Can be any value, if not a string it will be converted to one
startIndex – Must be a number from 0 to the length of the string
length – Must be a number from 1 to the length of the string, this is an optional parameter and if not included the entire length of the string from startIndex is taken
Returns the specified part of the passed in string
[!UpperCase(value)!]
value – Can be any value, if not a string it will be converted to one
Returns the specified string with every character converted to upper case
[!LowerCase(value)!]
value – Can be any value, if not a string it will be converted to one
Returns the specified string with every character converted to lower case
[!ProperCase(value)!]
value – Can be any value, if not a string it will be converted to one
Returns the specified string with the first letter of each word converted to upper case
[!Length(value)!]
value – Can be any value, if not a string it will be converted to one
Returns the number of characters in value
[!Concat(x, y)!]
x – Can be any value, if not a string it will be converted to one
y – Can be any value, if not a string it will be converted to one
Returns xy (concatenates both strings together)
[!Replace(old, new, value)!]
old – Can be any value, if not a string it will be converted to one
new – Can be any value, if not a string it will be converted to one
value – Can be any value, if not a string it will be converted to one
Returns a new string in which all instances of old in value are replaced with new.
NOTE: This is case sensitive
[!IIF(expression, "trueValue","falseValue")!]
expression – Must be a boolean (true/false) expression
trueValue – Can be anything
falseValue – Can be anything
Returns trueValue if the expression returns true.
[!IndexOf(string, " ")!]
This formula will find the position of a character in a string. The output for this formula will be a number. Example: If I wanted to fine the position of @ in an email of support@linktrust.com. The result would be 8.
You can also nest this formula into another formula to perform specific actions. If I wanted to split the first and last name that is posted in as one value, I would use the following formulas as calculated data fields.
Split off the first name only:
[!Substring([=fullname=], "0", IndexOf([=fullname=], " "))!]
If the value posted in is John Doe, this formula would return "John".
Split off the last name only:
[!Substring([=fullname=], Add(IndexOf([=fullname=], " "), 1))!]
If the value posted in is John Doe, this formula would return "Doe".
Boolean Formulas (True/False)
Boolean formulas are used within a rule to determine if the rule should pass or fail. When the result of the formula equates to 'True', the rule will pass. When the formula equates to 'False', the rule will fail. If a Boolean formula is used outside of a rule, it will return a 'True' or 'False' value. Only a single formula block is allowed when evaluating a rule.
[!Contains(lookIn, lookFor)!]
lookIn – string to look in.
lookFor – string to look for
Returns true if lookIn contains lookFor
NOTE: This is case insensitive
[!DoesNotContain(lookIn, lookFor)!]
lookIn – string to look in.
lookFor – string to look for
Returns true if lookIn does not contain lookFor
NOTE: This is case insensitive
[!And(x, y)!]
x – boolean (true/false) expression
y – boolean (true/false) expression
Returns true if x and y are both true
[!Or(x, y)!]
x – boolean (true/false) expression
y – boolean (true/false) expression
Returns true if either x or y is true
[!Not(value)!]
value – boolean (true/false) expression
Returns the opposite of value
[!RegExMatch(input, pattern)!]
input – Can be any value, if not a string it will be converted to one
pattern – Must be a valid regex pattern
Returns true if input matches the pattern
[!GreaterThan(x, y)!]
x – Must be a number
y – Must be a number
Returns true if x is > y
[!GreaterThanOrEqual(x, y)!]
x – Must be a number
y – Must be a number
Returns true if x is >= y
[!LessThan(x, y)!]
x – Must be a number
y – Must be a number
Returns true if x is < y
[!LessThanOrEqual(x, y)!]
x – Must be a number
y – Must be a number
Returns true if x is <= y
[!IsBlank(value)!]
value – Can be any value, if not a string it will be converted to one
Returns true if value is blank, empty or only contains white space
[!IsNotBlank(value)!]
value – Can be any value, if not a string it will be converted to one
Returns true if the value is not blank, empty or only contains white space
[!Equal(x, y)!]
x – Can be any value, if not a string it will be converted to one
y – Can be any value, if not a string it will be converted to one
Returns true if x is equal to y
NOTE: This is case insensitive
[!NotEqual(x, y)!]
x – Can be any value, if not a string it will be converted to one
y – Can be any value, if not a string it will be converted to one
Returns true if x is not equal to y
NOTE: This is case insensitive
[!IsInGlobalPickList(listID, value)!]
listID – The ID of the pick list in your account. If this is invalid it will error in the inbound
value – The value to check for
Returns true if the value is in the list
[!IsNotInGlobalPickList(listID, value)!]
listID – The ID of the pick list in your account. If this is invalid it will error in the inbound
value – The value to check for
Returns true if the value is not in the list
Special Formulas
[!GetMerchantReferenceId(channelID)!]
channelID – The ID of the channel to pull a merchant reference ID from.
The lead must have already been sent to the channel before this formula is called and that channel must be configured in its response to capture a merchant reference ID. If there is no ID available for the given channel, then an empty value is returned.
Use with Ping/Post Channels. This formula is primarily for when a Ping channel captures an ID from the merchant which must be submitted with the entire lead back to a merchant on a Post channel. Use this formula as a Channel Alias of the Post channel.
Note: Because 'Resend Lead' only deals with a single channel, this formula will not work when a lead is resent. Instead, a blank value will be returned.
You can request a demo to see LinkTrust up close and experience how it can help you, or start your free 14-day trial now.
Need help with this feature or have questions? You can contact our support team at support@linktrust.com