This article will cover the following:

- Various values that can be used within CAKE's custom fields

- Operators

- Wildcard Characters

- Functions


Custom Fields

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

 

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)


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.


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)