Custom Field Examples

In this article, you will find several Custom Field Examples for reference.

  • Operators, Functions, and Wildcard Characters
  • Converting Date Formats
  • Calculating Based Off of Fields
  • UK Custom Field Examples
  • Others/Misc.

Custom Fields may be used within the Lead Gen platform or within Reports when you are creating a custom column.

Custom Field Operators and Functions from the MSDN Page (http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx; C#)


 *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. 

Testing Link: http://demo.cakemarketing.com/ad.aspx#bc852 

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#)
  • ISODate (YYYY-MM-DD) => Real Date (MM/DD/YYYY)
  • MM/DD/YYYY to YYYY-MM-DD
  • Date Field with Dashes: 


Convert #date_now# to 24-hour format:

ampm - string - SUBSTRING('#date_now#',LEN('#date_now#')-1,2)

findspace - number - IIF(SUBSTRING('#date_now#',8,1)=' ',8,IIF(SUBSTRING('#date_now#',9,1)=' ',9,10))

pulltime - string - SUBSTRING('#date_now#',#findspace#+2,LEN('#date_now#')-4-#findspace#)


timelen - number - IIF(LEN('#pulltime#')=7,SUBSTRING('#pulltime#',1,1),SUBSTRING('#pulltime#',1,2))


maketime - string - IIF('#ampm#'='PM',#timelen#+12,#timelen#)+SUBSTRING('#pulltime#',LEN('#timelen#')+1,10)

makedatetime - string - SUBSTRING('#date_now#',1,#findspace#)+' '+'#maketime#'MM/DD/YYYY => YYYY/DD/MM:

 

Formula: SUBSTRING('10/24/1988',7,4)+'/'+SUBSTRING('10/24/1988',4,2)+'/'+SUBSTRING('10/24/1988',1,2)

Start:10/24/1988 Result : 1988/24/10

 

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

month = NUM = SUBSTRING('#dob#',1,2)

date = NUM = SUBSTRING('#dob#',4,2)

year = NUM = SUBSTRING('#dob#',7,4)

ISODate (YYYY-MM-DD) => Real Date (MM/DD/YYYY):

SUBSTRING('#ISODate#',6,2)+'/'+SUBSTRING('#ISODate#',9,2)+'/'+SUBSTRING('#ISODate#',1,4) 

MM/DD/YYYY to YYYY-MM-DD

SUBSTRING('#pay_date1#',7,4)+'-'+SUBSTRING('#pay_date1#',1,2)+'-'+SUBSTRING('#pay_date1#',4,2)

 

Month Abbrev (based on #DateOfBirth# dd-mm-yyyy)

Fields: DateOfBirth:24-10-1988

Formula: IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=1,'Jan',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=2,'Feb',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=3,'Mar',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=4,'Apr',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=5,'May',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=6,'Jun',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=7,'Jul',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=8,'Aug',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=9,'Sep',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=10,'Oct',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=11,'Nov','Dec')))))))))))

Result : Oct

Raw Formula:

IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=1,'Jan',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=2,'Feb',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=3,'Mar',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=4,'Apr',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=5,'May',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=6,'Jun',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=7,'Jul',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=8,'Aug',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=9,'Sep',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=10,'Oct',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=11,'Nov','Dec')))))))))))

Date Field with Dashes: 

Fields: any_date_field:MM/DD/YYYY

Formula: SUBSTRING('MM/DD/YYYY',1,2)+'-'+SUBSTRING('MM/DD/YYYY',4,2)+'-'+SUBSTRING('MM/DD/YYYY',7,4)

Result : MM-DD-YYYY

Complements of John:  Date Now 24hr.

/*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*/

12/11/2012 04:06:24 PM

IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2))

IIF(SUBSTRING(##,6,1)='/',##,SUBSTRING(##,1,3)+'0'+SUBSTRING(##,4,LEN(##)-3))

IIF(SUBSTRING(##,14,1)=':',##,SUBSTRING(##,1,11)+'0'+SUBSTRING(##,12,LEN(##)-11))

/*THIS IS THE FIRST VARIABLE.  Name this "first_set" and make it a string type.........................................

IIF(SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),14,1)=':',IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),1,11)+'0'+SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),12,LEN(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)))-11))

*/

12 hour to 24 hour and normalize the time.  Starting new variable because of IIF limit----------------------

 IIF(SUBSTRING(#first_set#,LEN(#first_set#)-1,2)='PM',SUBSTRING(#first_set#,1,11)+(CONVERT(SUBSTRING(#first_set#,12,2),'System.Int32')+12)+SUBSTRING(#first_set#,14,LEN(#first_set#)-12),#first_set#)

IIF(SUBSTRING(##,17,1)=':',##,SUBSTRING(##,1,14)+'0'+SUBSTRING(##,15,LEN(##)-14))

IIF(SUBSTRING(##,20,1)=' ',##,SUBSTRING(##,1,17)+'0'+SUBSTRING(##,18,LEN(##)-17))

SUBSTRING(##,1,19)

/*THIS IS THE SECOND VARIABLE.  Make this variable whatever you want to name it and give it a string type.........................................

SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),20,1)=' ',IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),1,17)+'0'+SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),18,LEN(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)))-17)),1,19)

*/

This is the output format

 MM/DD/YYYY HH:MM:SS

Calculating Based Off of Fields

  • Calculate age (based on #dob_month#, #dob_day#, #dob_year#)

  • Calculate age (based on #dob# MM/DD/YYYY)

  • Calculate age (based on #dob#YYYY/MM/DD)
  • Last Payday (based on #pay_frequency# [weekly,bi\weekly,twice_monthly,monthly], #payday\next#, #payday_following#)
  • Week Number (based on #month#, #date#, #year#; 2012-2013)
  • Date After Lead Submission (based on  #ISODate# output: MM/DD/YYYY)
  • Output the total number of months (based on YYYY-MM-DD Date)
  • Weekday Determination (based on #dob# - MM/DD/YYYY)
  • Total Months to Years+Months (based on #months#)
  • Employed Start Date (based on #months#)
  • Employed Start Date (based on #months#, #months_to_years#, #leftover_months#)
  • Employed Start Date (based on #months#, #months_to_years#, #leftover_months#; uses #date\now#_)

Calculate age (based on #dob_month#, #dob_day#, #dob_year#):

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-CONVERT('#dob_year#','System.Int32')+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')<CONVERT('#dob_month#','System.Int32'),-1, IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=CONVERT('#dob_month#','System.Int32')) AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=CONVERT('#dob_day#','System.Int32')),-1,0))

 

Calculate age (based on #dob# MM/DD/YYYY):

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')<CONVERT(SUBSTRING('#dob#',1,2),'System.Int32'),-1, IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')) AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',4,2),'System.Int32')),-1,0))

 

Calculate age (based on #dob#YYYY/MM/DD):

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-CONVERT(SUBSTRING('#dob#',1,4),'System.Int32')+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')<CONVERT(SUBSTRING('#dob#',6,2),'System.Int32'),-1, IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',6,2),'System.Int32')) AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')),-1,0))

Last Payday (based on #pay_frequency# [weekly,bi\weekly,twice_monthly,monthly], #payday\next#, #payday_following#):

  • For "weekly":

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))

Week Number (based on #month#, #date#, #year#; 2012-2013):

IIF(#year#=2012,IIF(#month#=1,IIF(#date#<2,52,IIF(#date#<9,1,IIF(#date#<16,2,IIF(#date#<23,3,IIF(#date#<30,4,5))))),IIF(#month#=2,IIF(#date#<6,5,IIF(#date#<13,6,IIF(#date#<20,7,IIF(#date#<27,8,9)))),IIF(#month#=3,IIF(#date#<5,9,IIF(#date#<12,10,IIF(#date#<19,11,IIF(#date#<26,12,13)))),IIF(#month#=4,IIF(#date#<2,13,IIF(#date#<9,14,IIF(#date#<16,15,IIF(#date#<23,16,IIF(#date#<30,17,18))))),IIF(#month#=5,IIF(#date#<7,18,IIF(#date#<14,19,IIF(#date#<21,20,IIF(#date#<28,21,22)))),IIF(#month#=6,IIF(#date#<4,22,IIF(#date#<11,23,IIF(#date#<18,24,IIF(#date#<25,25,26)))),IIF(#month#=7,IIF(#date#<2,26,IIF(#date#<9,27,IIF(#date#<16,28,IIF(#date#<23,29,IIF(#date#<30,30,31))))),IIF(#month#=8,IIF(#date#<6,31,IIF(#date#<13,32,IIF(#date#<20,33,IIF(#date#<27,34,35)))),IIF(#month#=9,IIF(#date#<3,35,IIF(#date#<10,36,IIF(#date#<17,37,IIF(#date#<24,38,39)))),IIF(#month#=10,IIF(#date#<1,39,IIF(#date#<8,40,IIF(#date#<15,41,IIF(#date#<22,42,IIF(#date#<29,43,44))))),IIF(#month#=11,IIF(#date#<5,44,IIF(#date#<12,45,IIF(#date#<19,46,IIF(#date#<26,47,48)))),IIF(#month#=12,IIF(#date#<3,48,IIF(#date#<10,49,IIF(#date#<17,50,IIF(#date#<24,51,IIF(#date#<31,52,1))))),'')))))))))))),IIF(#year#=2013,IIF(#month#=1,IIF(#date#<7,1,IIF(#date#<14,2,IIF(#date#<21,3,IIF(#date#<28,4,5)))),IIF(#month#=2,IIF(#date#<4,5,IIF(#date#<11,6,IIF(#date#<18,7,IIF(#date#<25,8,9)))),IIF(#month#=3,IIF(#date#<4,9,IIF(#date#<11,10,IIF(#date#<18,11,IIF(#date#<25,12,13)))),IIF(#month#=4,IIF(#date#<1,13,IIF(#date#<8,14,IIF(#date#<15,15,IIF(#date#<22,16,IIF(#date#<29,17,18))))),IIF(#month#=5,IIF(#date#<6,18,IIF(#date#<13,19,IIF(#date#<20,20,IIF(#date#<27,21,22)))),IIF(#month#=6,IIF(#date#<3,22,IIF(#date#<10,23,IIF(#date#<17,24,IIF(#date#<24,25,26)))),IIF(#month#=7,IIF(#date#<1,26,IIF(#date#<8,27,IIF(#date#<15,28,IIF(#date#<22,29,IIF(#date#<29,30,31))))),IIF(#month#=8,IIF(#date#<5,31,IIF(#date#<12,32,IIF(#date#<19,33,IIF(#date#<26,34,35)))),IIF(#month#=9,IIF(#date#<2,35,IIF(#date#<9,36,IIF(#date#<16,37,IIF(#date#<23,38,IIF(#date#<30,39,40))))),IIF(#month#=10,IIF(#date#<7,40,IIF(#date#<14,41,IIF(#date#<21,42,IIF(#date#<28,43,44)))),IIF(#month#=11,IIF(#date#<4,44,IIF(#date#<11,45,IIF(#date#<18,46,IIF(#date#<25,47,48)))),IIF(#month#=12,IIF(#date#<2,48,IIF(#date#<9,49,IIF(#date#<16,50,IIF(#date#<23,51,IIF(#date#<30,52,1))))),'')))))))))))),''))

Date After Lead Submission (based on  #ISODate# output: MM/DD/YYYY):

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.

CONVERT(SUBSTRING('#date_now#',6,4),'System.Int32')-CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',1,4),'System.Int32')+IIF(CONVERT(IIF(SUBSTRING('#date_now#',2,1)='/','0'+SUBSTRING('#date_now#',1,1),SUBSTRING('#date_now#',1,2)),'System.Int32')<CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',6,2),'System.Int32'),-1, IIF((CONVERT(IIF(SUBSTRING('#date_now#',2,1)='/','0'+SUBSTRING('#date_now#',1,1),SUBSTRING('#date_now#',1,2)),'System.Int32')=CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',6,2),'System.Int32')) AND (CONVERT(SUBSTRING('#date_now#',3,2),'System.Int32')=CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',9,2),'System.Int32')),-1,0))

 

Weekday Determination (based on #dob# - MM/DD/YYYY):

century_table = NUM = 2*(3-(CONVERT(SUBSTRING('#dob#',7,2),'System.Int32')%4))

last_2 = NUM = _CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')

 _

year_math = NUM =IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=24,24,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=23,23,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=22,22,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=21,21,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=20,20,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=19,19,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=18,18,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=17,17,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=16,16,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=15,15,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=14,14,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=13,13,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=12,12,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=11,11,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=10,10,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=9,9,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=8,8,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=7,7,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=6,6,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=5,5,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=4,4,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=3,3,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=2,2,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=1,1,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=0,0,0)))))))))))))))))))))))))

leap_year = NUM = IIF((CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')%100<>0)),1,0)

month_table = NUM =IIF(#leap\year#=1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=1,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=2,2,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=3,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=4,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=5,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=6,4,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=7,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=8,2,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=9,5,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=10,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=11,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=12,5,0)))))))))))),IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=1,0,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=2,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=3,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=4,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=5,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=6,4,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=7,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=8,2,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=9,5,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=10,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=11,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=12,5,0)))))))))))))_

date = NUM = CONVERT(SUBSTRING('#dob#',4,2),'System.Int32')

total = NUM = #century\table#+#last_2#+#year_math#+#month_table#+#date#_

day = STRING =IIF(#total#%7=0,'Sunday',IIF(#total#%7=1,'Monday',IIF(#total#%7=2,'Tuesday',IIF(#total#%7=3,'Wednesday',IIF(#total#%7=4,'Thursday',IIF(#total#%7=5,'Friday',IIF(#total#%7=6,'Saturday','Poop')))))))

  

Total Months to Years+Months (based on #months#); seems easy, and it is, BUT, it helps to build into much more complicated Custom Fields):

months_to_years = NUM = (#months#-(#months#%12))/12

leftover_months = NUM = #months#%12

 

Employed Start Date (based on #months#); This one will default the day to '01': 

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-((#months#-(#months#%12))/12)-IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,1,0)+'-'+IIF(IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+12)-(#months#%12),CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12))<10,'0'+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+12)-(#months#%12),CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)),IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+12)-(#months#%12),CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)))+'-01'

Employed Start Date (based on #months#, #months_to_years#, #leftover_months#); also, if the Day is > 28, then it makes it 28, so we don't get "illegal" dates like 2/31:

IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover_months#%12)<1,IIF(((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))+12)<10,'0'+((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))+12),(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))+12),IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))<10,'0'+(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12)),(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))))+'/'+IIF(CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')>28,'28',SUBSTRING('#ISODate#',9,2))+'/'+(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-#months_to_years#-IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover_months#%12)<1,1,0))

 

Employed Start Date (based on #months#, #months_to_years#, #leftover_months#; uses #date\now#_); also, if the Day is > 28, then it makes it 28, so we don't get "illegal" dates like 2/31:

NUM – months_to_years - (#months_employed#-(#months_employed#%12))/12

 NUM – lefover_months - #months_employed#%12

 NUM – andy1length - IIF(SUBSTRING('#date_now#',11,1)='',11,IIF(SUBSTRING('#date_now#',10,1)='',10,9))-5

 NUM – andy2month - IIF(SUBSTRING('#date_now#',3,1)='/',4,3)

 NUM – andy3dateparse - IIF(IIF(SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)>28,28,SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#))<10,'0'+IIF(SUBSTRING('#date\_now#',#andy2month#,#andy1length#-#andy2month#)>28,28,SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)),IIF(SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)>28,28,SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)))

 NUM – andy4yearparse - SUBSTRING('#date_now#',#andy1length#+1,4)

 NUM – andy5monthparse - SUBSTRING('#date_now#',1,#andy2month#-2)

 NUM – andy6monthtotal - IIF(IIF(#andy5monthparse#-#leftover_months#<1,12+(#andy5monthparse#-#leftover_months#),#andy5monthparse#-#leftover_months#)<10,'0'+IIF(#andy5monthparse#-#leftover_months#<1,12+(#andy5monthparse#-#leftover_months#),#andy5monthparse#-#leftover_months#),IIF(#andy5monthparse#-#leftover_months#<1,12+(#andy5monthparse#-#leftover_months#),#andy5monthparse#-#leftover_months#))

 NUM – andy7yeartotal - #andy4yearparse#-#months_to_years#-IIF(#andy5monthparse#-#leftover_months#<0,1,0)

 NUM – andy8build - '#andy7yeartotal#'+'-'+'#andy6monthtotal#'+'-'+'#andy3dateparse#'

UK Custom Field Examples

  • REGEX for UK Zip Codes

  • Determine What UK Post Codes Start With (used as filter for purchasing)

  • UK phone validation 


REGEX for UK Zip Codes: 

(((B|E|G|L|M|N|S|W)\d\d?)|((BF|BR|BX|FY|HA|HD|HG|HR|HS|HX|JE|LD|SM|SR|WC|WN|ZE)\d)|((AB|LL|SO)\d\d)|((AL|BA|BB|BD|BH|BL|BN|BS|BT|CA|CB|CF|CH|CM|CO|CR|CT|CV|CW|DA|DD|DE|DG|DH|DL|DN|DT|DY|EC|EH|EN|EX|FK|GL|GU|GY|HP|HU|IG|IM|IP|IV|KA|KT|KW|KY|LA|LE|LN|LS|LU|ME|MK|ML|NE|NG|NN|NP|NR|NW|OL|OX|PA|PE|PH|PL|PO|PR|RG|RH|RM|SA|SE|SG|SK|SL|SN|SP|SS|ST|SW|SY|TA|TD|TF|TN|TQ|TR|TS|TW|UB|WA|WD|WF|WR|WS|WV|YO)\d\d?)|((EC1|EC2|EC3|EC4|SW1|W1|WC1|WC2|E1|N1|NW1|SE1)(A|B|C|D|E|F|G|H|J|K|M|N|P|R|S|T|U|V|W|X|Y)))\s?\d(A|B|D|E|F|G|H|J|L|N|P|Q|R|S|T|U|W|X|Y|Z)(A|B|D|E|F|G|H|J|L|N|P|Q|R|S|T|U|W|X|Y|Z)

Determine What UK Post Codes Start With (used as filter for purchasing):

IIF(SUBSTRING('#postcode#',2,1)IN('0','1','2','3','4','5','6','7','8','9',''),SUBSTRING('#postcode#',1,1),SUBSTRING('#postcode#',1,2))

Example prefix of UK Post Codes:

AB AL B BA BB BD BF BH BL BN BR BS BT BX CA CB CF CH CM CO CR CT CV CW DA DD DE DG DH DL DN DT DY E EC EH EN EX FK FY G GL GU GY HA HD HG HP HR HS HU HX IG IM IP IV JE KA KT KW KY L (GIR) LA LD LE LL LN LS LU M ME MK ML N NE NG NN NP NR NW OL OX PA PE PH PL PO PR RG RH RM S SA SE SG SK SL SM SN SO SP SR SS ST SW SY TA TD TF TN TQ TR TS TW UB W WA WC WD WF WN WR WS WV YO ZE

The use case here is that they buyer only purchases in certain post codes. Filter= Custom Field Operator=List Contains

UK phone validation - based on valid phone Area code and #pri_phone# as the field:

for added functionality you could validate for the phone number lenght to be 10 or 11. with most of them being 11.

IIF(SUBSTRING('#pri_phone#',1,3)IN('020','023','024','028','029','070','074','075','076','077','078','079'),'TRUE',IIF(SUBSTRING('#pri_phone#',1,4)IN('0113','0114','0115','0116','0117','0118','0121','0131','0141','0151','0161','0191'),'TRUE',IIF(SUBSTRING('#pri_phone#',1,5)IN('01200','01202','01204','01205','01206','01207','01208','01209','01223','01224','01225','01226','01227','01228','01229','01233','01234','01235','01236','01237','01239','01241','01242','01243','01244','01245','01246','01248','01249','01250','01252','01253','01254','01255','01256','01257','01258','01259','01260','01261','01262','01263','01264','01267','01268','01269','01270','01271','01273','01274','01275','01276','01277','01278','01279','01280','01282','01283','01284','01285','01286','01287','01288','01289','01290','01291','01292','01293','01294','01295','01296','01297','01298','01299','01300','01301','01302','01303','01304','01305','01306','01307','01308','01309','01320','01322','01323','01324','01325','01326','01327','01328','01329','01330','01332','01333','01334','01335','01337','01339','01340','01341','01342','01343','01344','01346','01347','01348','01349','01350','01352','01353','01354','01355','01356','01357','01358','01359','01360','01361','01362','01363','01364','01366','01367','01368','01369','01371','01372','01373','01375','01376','01377','01379','01380','01381','01382','01383','01384','01386','01387','01388','01389','01392','01394','01395','01397','01398','01400','01403','01404','01405','01406','01407','01408','01409','01420','01422','01423','01424','01425','01427','01428','01429','01430','01431','01432','01433','01434','01435','01436','01437','01438','01439','01440','01442','01443','01444','01445','01446','01449','01450','01451','01452','01453','01454','01455','01456','01457','01458','01460','01461','01462','01463','01464','01465','01466','01467','01469','01470','01471','01472','01473','01474','01475','01476','01477','01478','01479','01480','01481','01482','01483','01484','01485','01487','01488','01489','01490','01491','01492','01493','01494','01495','01496','01497','01499','01501','01502','01503','01505','01506','01507','01508','01509','01520','01522','01524','01525','01526','01527','01528','01529','01530','01531','01534','01535','01536','01538','01539','01540','01542','01543','01544','01545','01546','01547','01548','01549','01550','01553','01554','01555','01556','01557','01558','01559','01560','01561','01562','01563','01564','01565','01566','01567','01568','01569','01570','01571','01572','01573','01575','01576','01577','01578','01579','01580','01581','01582','01583','01584','01586','01588','01590','01591','01592','01593','01594','01595','01597','01598','01599','01600','01603','01604','01606','01608','01609','01620','01621','01622','01623','01624','01625','01626','01628','01629','01630','01631','01633','01634','01635','01636','01637','01638','01639','01641','01642','01643','01644','01646','01647','01650','01651','01652','01653','01654','01655','01656','01659','01661','01663','01664','01665','01666','01667','01668','01669','01670','01671','01672','01673','01674','01675','01676','01677','01678','01680','01681','01683','01684','01685','01686','01687','01688','01689','01690','01691','01692','01694','01695','01697','01698','01700','01702','01704','01706','01707','01708','01709','01720','01721','01722','01723','01724','01725','01726','01727','01728','01729','01730','01732','01733','01736','01737','01738','01740','01743','01744','01745','01746','01747','01748','01749','01750','01751','01752','01753','01754','01756','01757','01758','01759','01760','01761','01763','01764','01765','01766','01767','01768','01769','01770','01771','01772','01773','01775','01776','01777','01778','01779','01780','01782','01784','01785','01786','01787','01788','01789','01790','01792','01793','01794','01795','01796','01797','01798','01799','01803','01805','01806','01807','01808','01809','01821','01822','01823','01824','01825','01827','01828','01829','01830','01832','01833','01834','01835','01837','01838','01840','01841','01842','01843','01844','01845','01847','01848','01851','01852','01854','01855','01856','01857','01858','01859','01862','01863','01864','01865','01866','01869','01870','01871','01872','01873','01874','01875','01876','01877','01878','01879','01880','01882','01883','01884','01885','01886','01887','01888','01889','01890','01892','01895','01896','01899','01900','01902','01903','01904','01905','01908','01909','01920','01922','01923','01924','01925','01926','01928','01929','01931','01932','01933','01934','01935','01937','01938','01939','01942','01943','01944','01945','01946','01947','01948','01949','01950','01951','01952','01953','01954','01955','01957','01959','01962','01963','01964','01967','01968','01969','01970','01971','01972','01974','01975','01977','01978','01980','01981','01982','01983','01984','01985','01986','01987','01988','01989','01992','01993','01994','01995','01997'),'TRUE',IIF(SUBSTRING('#pri_phone#',1,6)IN('013873','015242','015394','015395','015396','016973','016974','016977','017683','017684','017687','019467'),'TRUE','FALSE'))))

Others/Misc.

  • Removing The 1 From A Phone Number

  • Number to US Currency 999999 to 0

  • Bank Routing Number/ABA Validation (on "#aba#")

  • Convert Lower Case State to CAPS

  • SQL92 Errors

  • Date Field Without The Time Stamp

  • Loan To Value (LTV):

  • Faking A Calculation on "throttle %"

  • To Deal With 1 (or more) Numbers On Your Denominator That Will Attempt to Make Us Divide By 0


To remove the 1 from a phone number.

SUBSTRING('#fieldname#',2,10)

This will skip the first number being passed in (1), and will count the next 10 digits.

Turns: 19495482253 into 9495482253

Number to US currency 999999 to 0:

Example: 1500 to $1,500

Three fields

| Field 
 |  Type  
 | Formula 
 |
 | hundreds 
 | Num 
 | #amount# % 1000 
 |
 |  thousands  
 | Num 
 |  (#amount#-#hundreds#) / 1000 
 |
 | dollars 
 | Str 
 |  '$'+IIF(#thousands#>0,'#thousands#,'+IIF(#hundreds#<100,'0','')+IIF(#hundreds#<10,'0','')+'#hundreds#','#hundreds#') 
 |

 

Bank Routing Number/ABA Validation (on "#aba#") - Checks the checksum as well as makes sure the String is not all "0"s and is of LEN=9:

http://en.wikipedia.org/wiki/Routing_transit_number

 

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. 

IIF('#employer_state#'='al','AL',IIF('#employer_state#'='ak','AK',IIF('#employer_state#'='az','AZ',IIF('#employer_state#'='ar','AR',IIF('#employer_state#'='ca','CA',IIF('#employer_state#'='co','CO',IIF('#employer_state#'='ct','CT',IIF('#employer_state#'='de','DE',IIF('#employer_state#'='dc','DC',IIF('#employer_state#'='fl','FL',IIF('#employer_state#'='ga','GA',IIF('#employer_state#'='hi','HI',IIF('#employer_state#'='id','ID',IIF('#employer_state#'='il','IL',IIF('#employer_state#'='in','IN',IIF('#employer_state#'='ia','IA',IIF('#employer_state#'='ks','KS',IIF('#employer_state#'='ky','KY',IIF('#employer_state#'='la','LA',IIF('#employer_state#'='me','ME',IIF('#employer_state#'='md','MD',IIF('#employer_state#'='ma','MA',IIF('#employer_state#'='mi','MI',IIF('#employer_state#'='mn','MN',IIF('#employer_state#'='ms','MS',IIF('#employer_state#'='mo','MO',IIF('#employer_state#'='mt','MT',IIF('#employer_state#'=' ne',' NE',IIF('#employer_state#'='nv','NV',IIF('#employer_state#'='nh','NH',IIF('#employer_state#'='nj','NJ',IIF('#employer_state#'='nm','NM',IIF('#employer_state#'='ny','NY',IIF('#employer_state#'='nc','NC',IIF('#employer_state#'='nd','ND',IIF('#employer_state#'='oh','OH',IIF('#employer_state#'='ok','OK',IIF('#employer_state#'='or','OR',IIF('#employer_state#'='pa','PA',IIF('#employer_state#'='ri','RI',IIF('#employer_state#'='sc','SC',IIF('#employer_state#'='sd','SD',IIF('#employer_state#'='tn','TN',IIF('#employer_state#'='tx','TX',IIF('#employer_state#'='ut','UT',IIF('#employer_state#'='vt','VT',IIF('#employer_state#'='va','VA',IIF('#employer_state#'='wa','WA',IIF('#employer_state#'='wv','WV',IIF('#employer_state#'='wi','WI',IIF('#employer_state#'='wy','WY','')))))))))))))))))))))))))))))))))))))))))))))))))))

 

SQL92 Errors

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

 

Faking a calculation on "throttle %"

SUBSTRING(CONVERT(100*(1-(#Paid#/#Sellable#)),'System.String'),1,5)+'%'

OR

IIF(#Conversions# > 0, SUBSTRING(CONVERT(100*(1-(#Paid#/#Sellable#)),'System.String'),1,5)+'%','')

 

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).

IIF((#Revenue#)=(#Revenue#/(#Cost#+1)),0,#Revenue#/(#Cost#+(1-IIF(#Cost#=0,0,1))))

0 Comments

Add your comment

E-Mail me when someone replies to this comment