Creating Expressions with Expression Builder
Company administrators can use Expression Builder to create Siebel Query Language expressions, which are used in various parts of the application. When defining field level validations and default field values, a company administrator can create flexible expressions to cover a variety of business rules. For more information on field validation and field default values, see About Field Management.
Siebel Query Language expressions are also used extensively in workflow, to define both conditional triggers and the actions that are executed afterwards. For more information, see About Workflow Rules.
This topic provides reference information for Expression Builder. It is organized in the following sections:
- Using the Expression Builder Window
- Data Types in On Demand
- Data Type Arithmetic
- Operators
- Precedence of Operators
- Logical Operators
- Comparison Operators
- Arithmetic Operators
- Pattern Matching Operators
- Functional Operators
- Constants
For quick sample expressions, see Expression Builder Examples.
Using the Expression Builder Window
In Siebel CRM On Demand, wherever you see a box that accepts Query Language expressions, you can click the fx icon beside the box to open the Expression Builder window.
The Expression Builder window is specific to the Record Type. For example, when defining Field Validation on an Opportunity field, the Record Type is Opportunity. When constructing the body of an e-mail whenever a new Service Request is created, the Record Type is Service Request. The Expression Builder window picks up this Record Type from the context.
The Expression Builder window has two drop-down lists, a Show Acceptable Values link, and the expression text box.
- (Record Type) Fields. Shows the fields that are specific to the record type. You select a field from the list to paste it into the expression box. For example, selecting CloseDate from the list of Opportunity fields pastes [<CloseDate>] into the expression box. The brackets around the field name indicate that the value of the particular field will be evaluated and returned in its specific data type. Data types are described in more detail in the table below.
- Functions. Shows the functional operators that you can add into the expression box. For example, if you want to use the String version of the CloseDate value, use the functional operator FieldValue('<CloseDate>'). For more information, see the Functional Operators section, later in this document.
- Show Acceptable Values link. Shows what you can compare the selected value with. For example, if you select Opportunity Type in the list and then click Show Acceptable Values, you see the list of values that the field Opportunity Type can take. If there is no list of values for your selected field, you see the syntax of the constants that can be used in expressions.
To build an expression
- Click the fx icon
to open the Expression Builder window. - Select fields and operators from the drop-down lists, or type directly in the Expression box.
- (Optional) Click the Check Syntax button, and then make any necessary corrections.
- Save your expression.
Data Types in CRM On Demand
The following table shows the data type returned when a CRM On Demand field is used in an expression. To get the value of a field, use the syntax [<FieldName>].
CRM On Demand Field Type
|
Return Type in Expressions
|
Integer
|
Integer
|
Picklist
|
String
|
Currency
|
Currency
|
ID
|
String
|
Text
|
String
|
Check box
|
Boolean.
For example, you can use [<Active>] directly in places where conditions are expected. You can also use FieldValue('<Active>') = 'Y'.
|
Date
|
Date
|
Date Time
|
Date Time
|
Number
|
Number
|
NOTE: If the company administrator has renamed fields, the Record Type list shows the new names, but the expression syntax shows the original names. For custom fields, the Integration Tag names that company administrators can change are used in the expression syntax. The syntax is as follows:
[CustomTag_ITAG] and FieldValue('CustomTag_ITAG')
Data Type Arithmetic
If you create an expression that involves two different data types, the data type of the left argument determines the output data. When a statement references more than one field value, and the fields have different data types, the order of the data types can affect the calculation.
For example, the field Line Total is [Item Price] * [Quantity]. The following table shows the two possible results of the calculation.
|
|
|
Field
|
Data Type
|
Value
|
Quantity
|
Integer
|
5
|
Item Price
|
Currency
|
2.25
|
Calculation
|
Result
|
[<Item Price>] * [<Quantity>]
|
11.25
|
[<Quantity>] * [<Item Price>]
|
11.00
|
If you convert a number to a string instead of vice-versa, the calculation does not work correctly. For example:
Calculation
|
Result
|
Description
|
1234 + abcd
|
1234
|
"abcd" evaluates to the number 0 and then is added
|
abcd + 1234
|
abcd1234
|
1234 evaluates to the string "1234" and is appended
|
Operators
The different categories of operators are described in the sections that follow.
Precedence of Operators
Precedence is the order in which Oracle's Siebel CRM On Demand applications evaluate the various operators within a single expression. The following rules apply:
- Operators with higher precedence are evaluated before operators with lower precedence.
- Operators with equal precedence are evaluated from left to right.
- Lower-level numbers indicate higher precedence.
- The use of parentheses affects the order of precedence within an expression. The expression within parentheses is evaluated before the expression outside the parentheses.
The levels of precedence are listed in the following table.
Level
|
Operator
|
1
|
( )
|
2
|
- (negation)
|
3
|
^ (exponentiation)
|
4
|
* (multiplication), / (division)
|
5
|
+ (addition), - (subtraction), NOT logical operator
|
6
|
AND logical operator
|
7
|
OR logical operator
|
8
|
=, <>, >, <, >=, <= comparison operators
|
Logical Operators
The following table explains what a value of TRUE or FALSE means for each logical operator.
Operator
|
Returns TRUE if...
|
Returns FALSE if...
|
NOT
|
The condition evaluates to FALSE.
|
The condition evaluates to TRUE.
|
AND
|
All component conditions evaluate to TRUE.
|
Any component condition evaluates to FALSE.
|
OR
|
Any component condition evaluates to TRUE.
|
All component conditions evaluate to FALSE.
|
Comparison Operators
The following table describes the purpose of each comparison operator and gives an example of how it is used.
Operator
|
Purpose
|
Example
|
=
|
Equality test
|
[<Last Name>] = "Smith"
|
< >
|
Inequality test
|
[<Role>] <> "End-User"
|
>
|
Greater than
|
[<Revenue>] > 5000
|
<
|
Less than
|
[<Probability>] < .7
|
> =
|
Greater than or equal to
|
[<Revenue>] >= 5000
|
< =
|
Less than or equal to
|
[<Probability>] <= .7
|
Arithmetic Operators
The following table describes the purpose of each arithmetic operator and gives an example of how it is used.
Operator
|
Purpose
|
Example
|
+
|
Add
|
[<Record Number>] + 1
|
-
|
Subtract
|
[<Record Number>] - 1
|
-
|
Negate
|
[<Revenue>] < -100
|
*
|
Multiply
|
[<Subtotal>] * 0.0625
|
/
|
Divide
|
[<Total Items>] / [<Total Orders>]
|
^
|
Exponent
|
[<Grid Height>] ^ 2
|
Pattern Matching Operators
Pattern matching operators are used to compare character strings. They can be used with the NOT logical operator and with special characters, as described in the following table.
Operator
|
Purpose
|
Example
|
LIKE
|
Case sensitive string comparison
|
char1 LIKE char2
where:
- char1 is the value to be compared with the pattern
- char2 is the pattern to which char1 is compared.
|
NOT LIKE
|
Case sensitive string comparison
|
char1 NOT LIKE char2
or
NOT (char1 LIKE char2)
NOTE: The parentheses are required in this second example.
|
Character
|
Purpose
|
Example
|
*
|
Zero or more characters
|
[<Last Name>] LIKE "Sm*" returns all records where the [<Last Name>] value starts with the characters Sm, as in Smith, Smythe, Smart, and so on.
[<Last Name>] LIKE "*om*" would return all records where the [<Last Name>] field contains the characters om, as in Thomas, Thompson, Tomlin, and so on.
|
?
|
One character
|
[<First Name>] NOT LIKE "Da?" would return all records where the [<First Name>] value was three characters long and did not start with the letters Da. Records with Ted, Tom, and Sam would be returned, but Dax and Dan would not.
NOT ([<First Name>] LIKE "?o?") would return all records where the [<First Name>] value was three characters long and did not have as its middle character o. Records with Ted and Sam would be returned, but Tom and Bob would not.
|
Functional Operators
The following table shows examples of how syntax affects the functional operators.
Function syntax
|
Result
|
Operator(abc) or Operator('string') or Operator('7')
|
String constant
NOTE: When you enclose a value in quotes, it is always treated as a string.
|
Operator(7)
|
Valid number
|
Operator('<fieldname>')
|
Field name
|
Operator('customIntegrationtag_ITAG')
|
Custom Integration Tag
|
Operator(Operator2())
|
Result of Operator2()
|
Operator(expression)
|
All of (expression)
|
The following table describes the functions available in Expression Builder, in the order of their appearance in the list.
Function
|
Result Type
|
Description
|
Alias ()
|
String
|
Returns the alias of the signed-in user.
|
Currency ()
|
String
|
Returns the currency code for the position of the signed-in user (for example, USD).
|
DivisionName ()
|
String
|
Returns the division name of the signed-in user.
|
ExcludeChannel (channel name)
|
Boolean
|
Returns N if the specified channel name is the channel that the request came from.
The following strings are valid:
- Online
- Mass Update
- Record Merge
- Offline
- Web Services
- Office
- Partner
- OEI
For example, to stop a process from being triggered by a Web service request, add the following to the expression:
AND ExcludeChannel ("Web Services")
|
FieldValue (string1)
|
String
|
Returns the current field value of string1, where string1 is a valid XML name or customized integration tag.
|
FindNoneOf (string1, string2 )
|
Integer
|
Returns the position of the first instance in string1 which does not match any character in string2.
For example:
FindNoneOf ("abcdef", "xyzc")
returns 1.
|
FindOneOf (string1, string2 )
|
Integer
|
Returns the position of the first instance in string1 of a character in string2.
For example:
FindOneOf ("abcdef", "xyzc")
returns 3.
|
FirstName ()
|
String
|
Returns the signed-in user's first name.
|
GetGroupId ()
|
String
|
Returns the CRM On Demand calendar-sharing group.
|
IfNull (expr1, expr2)
|
Type of expr1
|
Returns the value of expr1 if expr1 is not NULL. Returns expr2 if expr1 is NULL.
The return type of IfNull is the type of its first argument, even if the first argument is NULL. The second argument is converted to the type of the first argument before its value is returned.
|
IIf (testExpr, expr1, expr2)
|
Type of expr1
|
If testExpr is TRUE, returns the value of expr1; otherwise returns the value of expr2.
The following expression contains only "Bob" if the [Last Name] field is NULL and the [First Name] field is "Bob":
[Last Name] + IIf ([Last Name] IS NULL, "", ",") + [First Name]
|
InStr (string, substring, int1, int2)
|
Integer
|
Return the first position of substring in string. You can restrict the search to a subset of the string by specifying the minimum (int1) and maximum (int2) points of the string to search.
|
IsManagerPosition ()
|
Boolean
|
Returns Y if the user has direct reports, or N if the user has no direct reports.
|
JobTitle ()
|
String
|
Returns the Job Title of the currently signed-in user. Similar to PositionId ().
|
JulianDay (date)
|
Integer
|
Equal to the Oracle Julian Day, for all dates in the 20th and 21st centuries.
|
JulianMonth (date)
|
Integer
|
Equal to the JulianYear() * 12 + currentMonth, where January = 1.
NOTE: The Julian functions must include Today() or a field name as a parameter.
For example, you need to use either JulianMonth([Created]) (of a field) or JulianMonth(Today()) (of the current date).
|
JulianQtr (date)
|
Integer
|
Equal to the JulianYear() * 4 + currentQuarter, where currentQuarter = (currentMonth - 1) / 3 + 1 rounded down to the next integer.
|
JulianWeek (date)
|
Integer
|
JulianDay() / 7, rounded down to the next integer.
|
JulianYear (date)
|
Integer
|
Equal to the current year + 4713.
|
Language ()
|
String
|
Returns the language code (for example, ENU) that is the active client language setting.
|
LanguageName ()
|
String
|
Returns the language name of the signed-in user's language.
|
LastName ()
|
String
|
Returns the signed-in user's last name.
|
Left (text, integer)
|
String
|
Returns the leftmost n characters in the text string or field. For example, Left ("Adams", 2) returns "Ad."
|
Len()
|
Integer
|
Returns the length of a string or string variable.
|
LN (number)
|
Number
|
Returns the natural log of the number.
For example:
LN (10)
returns 2.30.
|
LocalCurrency ()
|
String
|
Returns the currency code for the signed-in user (for example, JPY).
|
Locale ()
|
String
|
Returns the signed-in user's locale code.
|
LocaleListSeparator ()
|
String
|
Returns the List Separator character for the locale, such as ','.
|
LocaleName ()
|
String
|
Returns the signed-in user's locale name, such as "English-US".
|
LoginId ()
|
String
|
Returns the Row ID of the signed-in user record.
|
LoginName ()
|
String
|
Returns the Sign In ID of the user.
|
Lookup (type, value)
|
String
|
NOTE: To use this function, you must be familiar with the structure of the List of Values table in Siebel CRM On Demand.
Finds a row in the List of Values table where:
- TYPE matches the type argument and
- VALUE matches the value argument.
The function returns the value of ORDER_BY for that row.
The primary purpose of the Lookup function is to avoid additional joins in a business component.
|
LookupMessage (Message type, message value)
|
String
|
Get user language translation of message.
|
LookupName (type, lang_ind_code)
|
String
|
NOTE: To use this function, you must be familiar with the structure of the List of Values table in Siebel CRM On Demand.
Finds a row in the List of Values table where:
- TYPE matches the type argument
- CODE matches the lang_ind_code argument, and
- LANG_ID matches the language code of the currently active language.
Returns the language-independent code (CODE) for the row.
This function is used to obtain the untranslated value in the specified LOV.
|
LookupValue (type, lang_ind_code)
|
String
|
NOTE: To use this function, you must be familiar with the structure of the List of Values table in Siebel CRM On Demand.
Finds a row in the List of Values table where:
- TYPE matches the type argument
- CODE matches the lang_ind_code argument, and
- LANG_ID matches the language code of the currently active language.
Returns the display value (VAL) for the row.
LookupValue tries to find the display value for the specified lang_ind_code. If not found, LookupValue just returns the lang_ind_code itself as the value.
This function is used to obtain the translation of the specified untranslated value in the specified LOV into the currently active language.
|
LOVLanguage ()
|
String
|
Returns the model's selected language.
|
Mid (string, int, int)
|
String
|
Returns all characters after x to a maximum of y.
|
OrganizationName ()
|
String
|
Returns the organization name of the signed-in user.
|
PositionId ()
|
String
|
Position ID of the currently signed-in user.
|
PositionName ()
|
String
|
Position name of the currently signed-in user.
|
PRE (Field_Name)
|
String
|
Returns the value of the field before the record updates are saved.
|
Right (text, integer)
|
String
|
Returns the rightmost n characters in the text string or field.
For example, Right ("Adams", 2) returns "ms".
|
RowIdToRowIdNum ([Id])
|
String
|
Converts an alphanumeric row ID to a unique, pure numeric row ID.
|
RowNum ()
|
Integer
|
Returns the record's position within the active set of records.
|
Timestamp ()
|
Date Time
|
Today's date and time (for example, 01/02/08 11:15:22).
It is possible to perform calculations with date-time fields in calculated fields. When a number is entered in a date-time field, days are represented by integers; hours, minutes and seconds are represented by fractions.
For example, to add one minute to the current date and time, use the following expression, which is derived from the fact that one day has 1440 minutes:
Timestamp() + 1/1440
In this example the product delivery interval, measured in seconds, is added to the current date and time:
Timestamp() + [Product Delivery Interval]/86400
You can use the Timestamp function in queries. For example:
Created >= Timestamp() - 0.1
returns those records created within the last one-tenth of a day.
|
ToChar (num_expr, format)
|
String
|
Returns a string that represents a number or date in a format specified by the optional format parameter. (For example, ToChar (10, "##.##") returns "10.00".)
|
Today ()
|
Date
|
Today's date (for example, 1/26/08).
NOTE: Today() and Timestamp() functions return different results. Today() does not do the UTC (universal time code) conversion. TimeStamp() does do the UTC conversion.,
|
UserValue (fieldname)
|
String
|
Queries the active user record for '<fieldname>' value, where '<fieldname>' is a valid XML name or customized integration tag, such as UserValue('<Role>') or UserValue('<RoleId>').
|
UtcConvert (date, format string)
|
Time
|
Converts a local time (in the current user's signed-in user's time zone) to another local time in the specified time zone.
For example, if the user is on Pacific time and wants to convert the time to North American Eastern Standard Time:
UtcConvert("12/14/2008 5:07:05 PM", "Eastern Standard Time")
returns 12/14/2008 00:07:05 PM.
|
Constants
The following table describes the constants that appear when you click the Show Acceptable Values link for a field that has no list of values.
Type
|
Acceptable Values
|
Check box
|
Enter the English Y or N (yes or No). You cannot use the equivalents in other languages.
|
Currency, Integer, Number or Percent
|
Enter all values for these field types according to these guidelines, regardless of how they are displayed:
- Use American English formatting for Number field types. For example, enter 1000.50 for one thousand dollars and fifty cents.
- Do not use a comma (,) (except as a separator between values).
- Do not use the dollar sign ($) or other currency identifiers.
- Do not use the percent sign (%).
|
Date
|
Enter values in this format, regardless of your locale: MM/DD/YYYY (month, day, year). For example, enter 05/01/2008 for May 1, 2008.
|
Date/Time
|
Use the 24-hour format when entering the time part of date-time fields.
Enter date-time values in this format, regardless of your locale: MM/DD/YYYY HH:MM:SS (hour:minute:second).
For example, enter 05/01/2008 21:30:00 for 9:30 p.m. on May 1, 2008.
|
Phone
|
Enter phone values as a string of numbers without hyphens (-), parentheses [()], other special characters, or spaces.
For example, enter 215 5557916 for (215) 555-7916.
Exception: Enter the plus sign (+) to indicate that you are including country codes. You can also add the pound sign (#) to indicate extensions. Then use spaces to separate the country code, area or city code, local number, and extension.
For example, enter +34 93 5551500 #234 for Spain, Barcelona, local number, and extension.
|
Text
|
Enter text as you see it in the application (case-sensitive).
When matching partial values (substrings), you must use the Contains At Least One Value condition.
|
|