Formulas

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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.