Variable

Action or Value

Example

[IF(val1|operator|val2|true|false)]


Exception for BETWEEN (B):

[IF(val1|B|val2|val3|true|false)]

Compares val1 to val2 using the operator and if true returns the true value else returns the false value. 


for BETWEEN (B) operations you must supply 6 parameters with the 4th one being the TO value for the FROM (3rd) and TO (4th) combination.


Available Operators:

=   Equal

<>  Not Equal

!=  Not Equal

<   Less than

>   Greater than

<=  Less than or equal to

=>  Less than or equal to

>=  Greater than or equal to

=>  Greater than or equal to

B   Between (inclusive)

C   Contains

S   Starts With

E   Ends With


true = defaults to "true"


false = defaults to "false"


* String operations are NOT case sensitive

[IF(1|=|2|YES|NO)] will return NO 


[IF(2|=|2|YES|NO)] will return YES.


[IF(1|B|2|3|YES|NO)] will return NO 


[IF(2|B|1|3|YES|NO)] will return YES


[IF(2|B|2|3|YES|NO)] will return YES


[IF(2|B|2|3)] will return true


[IF(1|B|2|3)] will return false

[IFNULL(value|alt|prenotnull|postnotnull|prenull|postnull)]


[ISNULL(value|alt|prenotnull|postnotnull|prenull|postnull)]

Returns value is not null but if null returns alternate value. Optionally with pre-post values.


value = Value to check


alt = Alternate value if Value is blank


prenotnull = Text to place on the start of the return value if Value was not blank


postnotnull = Text to place on the end of the return value if Value was not blank


prenull = Text to place on the start of the return value if Value was blank


postnull = Text to place on the end of the return value if Value was blank


* One case where pre/post values are very useful is when displaying LastName, FirstName. If LastName is blank you don't want to include a comma and space.

    [IFNULL([LastName]|||, )][FirstName]

[IFNULL(XYZ|ABC)] - Returns XYZ


[IFNULL(|ABC)] - Returns ABC


[IFNULL(Smith|||, )]John - Returns Smith, John


[IFNULL(|||, )]John - Returns John

[NOTNULL(value|pre|post)]

Adds text before and/or after value only if value is not blank. 


This is a more efficient way to append text such as delimiters on values if they are not blank. 

[NOTNULL(Smith||; )] = "Smith; "


[NOTNULL(||; )] = ""


[NOTNULL(Smith|-|; )] = "-Smith; "

[CASE(val|var1|res1|var2|res2|else)]

Goes through the list of vars and returns the associated res when a var is matched to val. If no vars are matched, it returns the 'else' value or empty if no 'else' is specified.


The var/res pairs can be repeated as many times as needed.


This is similar to a SELECT CASE in VB or SWITCH statement in C# or JavaScript.

[CASE(X|A|1|x|2|z|3|0)] = 2 (matched on x)


[CASE(F|A|1|x|2|z|3|0)] = 0 (no match so else is used)


[CASE(test|test|yes|testx|no)] = yes

[ISEMAILVALID(Email|YesVal|NoVal)]

Returns YesValue if valid and NoValue if not valid

[ISEMAILVALID(good@email.com|YES|NO)] returns YES

[ISEMAILVALID(bad@email|YES|NO)] returns NO