How to Add Formula Parameters in CAKE Custom Fields

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.

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

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

The following arithmetic operators are also supported:

Both * and % can be used interchangeably as wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters must be enclosed in brackets ([]). If a bracket itself is in the clause, each bracket character should also be enclosed in brackets — for example [[] or []].

A wildcard is allowed at the start, end, or both ends of a pattern. It is not allowed in the middle of a string.

• "ItemName LIKE '*product*'" — matches anywhere in the string ✓
• "ItemName LIKE '*product'" — matches at the end ✓
• "ItemName LIKE 'product*'" — matches at the start ✓
• "ItemName LIKE 'te*xt'" — wildcard in the middle ✗ not allowed

The following functions are supported in CAKE custom field formula parameters:

Converts an expression to a specified .NET type.

• Syntax: CONVERT(expression, type)
• expression — The expression to convert.
• type — The .NET type to convert to (e.g. System.Int32, System.String).

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

Conversion notes: 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 and TimeSpan can each be coerced to and from String and themselves only.

Returns the length of a string.

• Syntax: LEN(expression)
• expression — The string to evaluate.

Example: LEN('#phone_work#')

Checks an expression and returns either the expression value or a replacement value if the expression is null.

• Syntax: ISNULL(expression, replacementvalue)
• expression — The expression to check.
• replacementvalue — The value to return if expression is null.

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

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

• Syntax: IIF(expr, truepart, falsepart)
• 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', 'not expensive')

Removes all leading and trailing blank characters including \r, \n, \t, and spaces.

• Syntax: TRIM(expression)
• expression — The expression to trim.

Example: TRIM('#company_name#')

Returns a sub-string of a specified length starting at a specified position within a string.

• Syntax: SUBSTRING(expression, start, length)
• expression — The source string.
• start — Integer specifying the starting position (1-based).
• length — Integer specifying the number of characters to return.

Example: SUBSTRING('#phone#', 7, 8) — returns 8 characters starting at position 7.

You may also be interested in:

• How to Add Formula Parameters in Custom Lead Fields
• DataColumn.Expression — Microsoft Learn

If you have any questions, please reach out to your dedicated CAKE Client Success Manager/Account Manager or contact the CAKE Support Team at support@getCAKE.com.