*BOOL Fields. Be advised that BOOL fields can fail to evaluate if NO values are passed. By simply adding the string handlers around the token and a Capitalized True will suffice.

Operators

Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

When you create comparison expressions, the following operators are allowed:

<

<=

<>

IN

LIKE

The following arithmetic operators are also supported in expressions:

(addition)

(subtraction)

* (multiplication)

/ (division)

% (modulus)

Functions

The following functions are also supported:

CONVERT

Description

Converts particular expression to a specified .NET Framework Type.

Syntax

Convert(expression, type)

Arguments

expression -- The expression to convert.

type -- The .NET Framework type to which the value will be converted.

Example: Convert('#year#','System.Int32')

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

LEN

Description

Gets the length of a string

Syntax

LEN(expression)

Arguments

expression -- The string to be evaluated.

Example: LEN('#phone_work#')

ISNULL

Description

Checks an expression and either returns the checked expression or a replacement value.

Syntax

ISNULL(expression, replacementvalue)

Arguments

expression -- The expression to check.

replacementvalue -- If expression is Nothing, replacementvalue is returned.

Example: ISNULL('#price#','-1')

IIF

Description

Gets one of two values depending on the result of a logical expression.

Syntax

IIF(expr, truepart, falsepart)

Arguments

expr -- The expression to evaluate.

truepart -- The value to return if the expression is true.

falsepart -- The value to return if the expression is false.

Example: IIF('#total#'>1000, 'expensive', 'you are cheap')

TRIM

Description

Removes all leading and trailing blank characters like \r, \n, \t, ' '

Syntax

TRIM(expression)

Arguments

expression -- The expression to trim.

SUBSTRING

Description

Gets a sub-string of a specified length, starting at a specified point in the string.

Syntax

SUBSTRING(expression, start, length)

Arguments

expression -- The source string for the substring.

start -- Integer that specifies where the substring starts.

length -- Integer that specifies the length of the substring.

Example: SUBSTRING('#phone#', 7, 8)

Wildcard Characters

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:

"ItemName LIKE '*product*'"

"ItemName LIKE '*product'"

"ItemName LIKE 'product*'"

Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.

This URL links you directly to a Buyer Contract in the "Custom Field" Vertical with a LeadID in the Contract Name that you can use test Custom Fields. The Vertical is in the Breadcrumb, so you can get to that very quickly to work on your Custom Fields for Testing.

*Please leave it neat like you found it :)

Converting Date Format Examples

Convert #date_now# to 24-hour Format

Standard Date (MM/DD/YYYY) to 3 Fields (month/day/year; based on #dob#)

This shows the process taken to get date_now normalized and in 24-hour format without the AM/PM at the end. There's two variables that should be used here. They are labeled on top of the code pieces. View in SQL mode to see better and make sure to take off the star and slash at the ends. The layout shows the first part and is then replacing the ## in the next part and then that replaces the next ## and so on till the variable is listed*/

IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7<1 AND CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')=1,12,IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-7<1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')-1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')))+'/'+IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-7>0,IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7<10,'0'+(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7),CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7),IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1 IN (1,3,5,7,8,10,12),31,IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1=2,28,30))+CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7)+'/'+(CONVERT(SUBSTRING('#payday_next#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7<1 AND CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')=1,1,0))

For "bi_weekly":

IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14<1 AND CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')=1,12,IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-14<1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')-1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')))+'/'+IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-14>0,IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14<10,'0'+(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14),CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14),IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1 IN (1,3,5,7,8,10,12),31,IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1=2,28,30))+CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14)+'/'+(CONVERT(SUBSTRING('#payday_next#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14<1 AND CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')=1,1,0))

For "twice_monthly":

IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1)+'/'+IIF(CONVERT(SUBSTRING('#payday_following#',4,2),'System.Int32')<29,SUBSTRING('#payday_following#',4,2),IIF(IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1) IN (1,3,5,7,8,10,12),31,IIF(IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1)=2,28,30)))+'/'+(CONVERT(SUBSTRING('#payday_following#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,1,0))

For "monthly":

IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1)+'/'+IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')<29,SUBSTRING('#payday_next#',4,2),IIF(IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1) IN (1,3,5,7,8,10,12),31,IIF(IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1)=2,28,30)))+'/'+(CONVERT(SUBSTRING('#payday_next#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,1,0))

IIF(IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')))))<10,'0'+IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32'))))),IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32'))))))+'/'+IIF(IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=28,1,CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')+1))))<10,'0'+IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=28,1,CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')+1)))),IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=28,1,CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')+1)))))+'/'+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=12 AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',1,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32'))

Output the total number of months (based on YYYY-MM-DD Date). Calculating Against Date Now.

IIF((((3*CONVERT(SUBSTRING('#aba#',1,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',4,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',7,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',2,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',5,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',8,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',3,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',6,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',9,1),'System.Int32')))%10=0) AND (((3*CONVERT(SUBSTRING('#aba#',1,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',4,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',7,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',2,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',5,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',8,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',3,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',6,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',9,1),'System.Int32')))<>0) AND LEN('#aba#')=9,TRUE,FALSE)

Convert Lower Case State to CAPS

Description: Most likely will not need this because most people know that all states are CAPS. But just in case.

If a field is null and we do a substring on it, the system will break if we substring starting at the #1 character. CONVERT(SUBSTRING('#DateOfBirth#',1,2),'System.Int32') will not work if #DateOfBirth# is null. This is the fix CONVERT(SUBSTRING(IIF('#DateOfBirth#'='','00','#DateOfBirth#'),1,2),'System.Int32')

Date field without the time stamp:

Example: SUBSTRING('#any_date_field#',1,10)

To read this, the 1 is indicating which position to start the substring. The 10 is the number of characters to return.

Mm/dd/yyyy=10 characters.

Loan To Value (LTV):

Formula: #mortgage_balance#/#home_value#*100

Fields: mortgage_balance:130000|home_value:150000

Formula: 130000/150000*100

Result : 86.6666666666667

To deal with 1 (or more) numbers on your Denominator that will attempt to make us Divide by 0:

We want to set up a Formula that is (#Revenue#-#Cost#)/#Cost#. Because #Cost# could be 0, we get #Revenue#/0, which is impossible to calculate (ROI=0 for no initial Investment, or infinity for 100% Profit, your choice).

Assign an integer based on state (map each state to a number):IIF('#state#'='CA','13',IIF('#state#'='AZ',’2’,IIF(‘#state#’=‘PA’,’3’,...'’))) *(remember to close out with an else option such as: ,'ERROR'