Expressions – Bookmark Templates

This entry is part 13 of 19 in the series Setup Guide - Bookmark Templates

Both filters and computed fields use expressions enabling you to compute and manipulate data. In the Filter example, OriginalQuote <> 0 was the expression; in the Computed Fields example, (cmpToRevenueAmount / cmpToExpenseAmount) – 1 was the expression.

Expressions can do comparisons, computations, or aggregations. They can include both operators and functions. Text springs require single quote marks around them, and Grouping with parentheses is recommended.

Operators

Operator Explanation Example Possible Result
< Less Than OriginalQuote < 5 4, 3, 2, etc.
> Greater Than OriginalQuote > 5 6, 7, 8, etc.
<= Less Than or Equal OriginalQuote <= 5 5, 4, 3, etc.
>= Greater Than or Equal OriginalQuote >= 5 5, 6, 7, etc.
<> Not Equal OriginalQuote <> 0 -1, 1, 2, etc.
= Equal OriginalQuote = 5 5
IN In the (list) State IN (‘NY’,’NJ’,’CT) NY, NJ, CT
LIKE Like the text and wildcard that follows. Wildcards are * or % and they can be used before or after the text string. State LIKE N% NE, NM, NY, etc.
+ Concatenation City + ‘, ’ + State Norwalk, CT
E.g., OriginalQuote = 1000 in the following:
+ Plus OriginalQuote + 10 1010
Minus OriginalQuote – 10 990
* Multiplied By OriginalQuote * 100 100,000
/ Divided By OriginalQuote / 10 100
% Remainder after division OriginalQuote % 3 1

 

Functions

Function Explanation Example Possible Result
IIF IIF(test, a,b)
If test is true, returns a, otherwise returns b
IIF(Quantity > 50, ‘Yes’, ‘No’) 51 = Yes
49 = No
ISNULL ISNULL(a,b)
If a is null (i.e., not specified), returns b, otherwise returns a
ISNULL(Quantity, ‘n/a’) = n/a
20 = 20
LEN The string length LEN(State) 2
TRIM Removes leading and trailing blanks, tabs, carriage returns and line feeds TRIM(City)
SUBSTRING SUBSTRING(String,start,len)
Extracts part of a string starting at the specified character and for the length specified
SUBSTRING(City,1,4) Philadelphia = Phil
Series Navigation<< Computed Fields – Bookmark TemplatesData from Attachments – Bookmark Templates >>