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.
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:
ampm - string - SUBSTRING('#date_now#',LEN('#date_now#')-1,2)
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
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# DD/MM/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#’,4,2),‘System.Int32’),-1,IIF((CONVERT(SUBSTRING(‘#ISODate#’,6,2),‘System.Int32’)=CONVERT(SUBSTRING(‘#dob#’,4,2),‘System.Int32’))AND(CONVERT(SUBSTRING(‘#ISODate#’,9,2),‘System.Int32’)=CONVERT(SUBSTRING(‘#dob#’,1,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#'
- 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):
- 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)
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
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))))
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'