11Operators and Expressions
Operators and Expressions
This chapter describes operators and expressions. It includes the following topics:
Operators
This topic describes operators. It includes the following topics:
Operator Precedence
Precedence is the order that Siebel CRM uses to evaluate the operators that a single expression contains. It evaluates higher precedence operators before it evaluates lower precedence operators. It evaluates operators that are equal in precedence from first to last.
You can use parentheses to modify the order of precedence that Siebel CRM uses to evaluate an expression. It evaluates the expression in the parentheses first, and then evaluates parts of the expression that reside outside of the parentheses.
The following table describes the levels of precedence. A lower level indicate higher precedence. For example, level 1 possess the highest precedence.
Table Operator Precedence
| Level | Operator |
|---|---|
1 |
() |
2 |
- (negation) |
3 |
^ (exponentiation) |
4 |
The following operators possess this level:
|
5 |
The following operators possess this level:
|
6 |
AND logical operator. |
7 |
OR logical operator. |
8 |
Comparison operators. The following operators possess this level: = (equal to) <> (not equal to) > (greater than) < (less than) >= (greater than or equal to) <= (less than or equal to) |
Comparison Operators
The following table describes each comparison operator.
Table Comparison Operators
| Operator | Description | 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 |
Logical Operators
The following table describes logical operators.
Table Logical Operators
| Operator | Returns TRUE | Returns FALSE |
|---|---|---|
NOT |
If Siebel CRM evaluates the condition to FALSE. |
If Siebel CRM evaluates the condition to TRUE. |
AND |
If Siebel CRM evaluates all component conditions to TRUE. |
If Siebel CRM evaluates any component condition to FALSE. |
OR |
If Siebel CRM evaluates any component condition to TRUE. |
If Siebel CRM evaluates all component conditions to FALSE. |
Arithmetic Operators
The following table describes arithmetic operators.
Table Arithmetic Operators
| 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 |
LIKE and NOT LIKE Operators
The LIKE operator matches a portion of one character value to another value. Siebel CRM uses the following format for a LIKE operator in a character string comparison that uses pattern matching:
char1 LIKE char2
where:
char1 is the value that Siebel CRM compares to the pattern.
char2 is the pattern where Siebel CRM compares char1.
To exclude a pattern, you can use the NOT logical operator with the LIKE operator. It uses the following format:
char1 NOT LIKE char2
or
NOT (char1 LIKE char2)
Each character is case-sensitive.
The NOT operator requires parentheses.
The Search Engine Table property of a view or applet must reference the same table that the index references. For example, if the search index references the S_EVT_ACT table, then the view and the applet must reference the Action business component.
Wildcard Characters You Can Use with the LIKE Operator
The following table describes wildcard characters that you can use with the LIKE operator.
Table Wildcard Characters You Can Use with the LIKE Operator
| Character | Description |
|---|---|
* |
Replace zero or more characters. For example: [Last Name] LIKE "Sm*" This example returns all records whose last name starts with Sm. For example, Smith, Smythe, Smart, and so on. For example: [Last Name] LIKE "*om*" This example returns all records whose Last Name field includes om. For example, Thomas, Thompson, Tomlin, and so on. Using the * (asterisk) wildcard to find all entries in a field might cause a performance problem. If it does, then you can use IS NOT NULL instead. For more information, see NULL Operator. |
? |
Replace one character. For example: [First Name] NOT LIKE "Da?" This example returns all records whose first name is three characters long and does not start with the letters Da. For example, it returns records that include Ted, Tom, and Sam. It does not return records that start with Dax or Dan. For example: NOT ([First Name] LIKE "?o?") This example returns all records whose first name is three characters long and does not include o as the middle character. For example, it returns Ted and Sam. It does not return Tom or Bob. |
NULL Operator
The NULL operator in an SQL statement represents a value that is not known or is not applicable. Siebel CRM evaluates an expression that includes a NULL operator differently than it evaluates other operators. NULL is not a value. A comparison function does not operate correctly if a NULL operator exists in the comparison. For instance, if NULL = NULL is not TRUE. Note the following:
SQL and Siebel CRM provide special functions and grammar that support NULL, including the IS NULL operator and the IfNull function. A comparison, string concatenation, and Boolean operation include special behavior that handles a NULL operator.
You can set the type for a NULL operator similar to how you set the type for a value. An operand or result can be a NULL string, NULL number, NULL Boolean, and so on.
If one side of a comparison is:
NULL. The comparison returns a NULL of type Boolean.
Is not NULL. The comparison returns TRUE or FALSE. For example, 1>2 is FALSE, and 1<NULL is NULL.
If one side of an arithmetic operation is NULL, then the operation returns NULL of the appropriate type, except for a string concatenation. NULL adds no characters during a string concatenation operation. For example:
1 + 2 is 3
1 + NULL is NULL (of type Integer)
"Fred" + ", Smith" is "Fred, Smith"
"Fred" + NULL is "Fred"
IS NULL Operator
The IS NULL operator is a unary operator. A unary operation is an operation that includes only one operand. If you use the IS NULL operator, and if the operand for this operator evaluates to:
TRUE. IS NULL evaluates to NULL.
FALSE. IS NULL evaluates to not NULL.
You cannot use the = (equal) operator to determine if a value is NULL because a NULL operand does not contain a value.
If Null Function
The IfNull function contains two arguments and returns the value of the first or second argument depending on if the first argument is NULL. IfNull (a,b) returns one of the following values:
a if a is not NULL
b if a is NULL
Siebel CRM sets the return type of the IfNull function to the type of the first argument that it contains, even if this first argument is NULL. Siebel CRM converts the second argument to the type of the first argument before it returns the value of the IfNull function.
Flag Fields and NULL
A flag field is a type of field that Siebel CRM can evaluate to a Boolean value, such as True or False, or Y or N. You can use a flag field to turn functionality on or off. A flag field is typically but not always a calculated field.
If you configure Siebel CRM to query a flag field, then you must use caution. The <> (not equal to) and NOT IN comparison operators cannot evaluate a field that contains a null value. Siebel CRM sets the default value of a flag field to null, so a workflow condition of <>'Y' does not work. To avoid this situation, you can do one of the following:
Use IS NOT NULL as a comparison operator.
Use IN ('N',NULL).
Predefault the business component field to 'N'.
Using the EXISTS Operators with Multivalue Groups
This topic describes how to use the EXISTS operator with multivalue groups.
Using the [NOT] EXISTS Operator with Multivalue Groups
To reference a multivalue group field, you can specify the [NOT] EXISTS operator in a query by example or in the Search Specification property of an object. Siebel CRM uses a multivalue group field to display the child records of a parent record in the parent record applet. For example, assume the following:
Opportunities are a separate entity.
Contacts are a separate entity.
The Opportunity business object references the Opportunity and Contact business components.
A many-to-many relationship exists between opportunities and contacts:
One or more contacts can modify an opportunity.
A contact can modify one and only one opportunity.
A form applet displays and manages opportunity information and displays any contact information that is specific to the opportunity. This applet includes the following fields that reference the Opportunity business component:
Opportunity Name
Contact First Name
Contact Last Name
This form applet displays the opportunity name in a predefined text box control. Siebel CRM defines the contact first name as a multivalue group field and the last name as a multivalue group field instead of as predefined edit controls. If you use query by example on a multivalue field (MVF), then you must include only multivalue fields that Siebel CRM displays in the originating business component.
Assume you set the search specification for the opportunity name to the following value:
Wine Festival
In this example, the Opportunity business component returns all opportunities that contain a name of Wine Festival.
Assume you set the search specification for the contact last name to the following value:
Smith
In this example, the Opportunity business component returns all opportunities that include a contact with a last name of Smith. This situation occurs because you defined the search specification on the Opportunity business component and not on the Contact business component.
Examples of Using EXISTS and NOT EXISTS
A query by example that resides in the last name field in the client must use the following format:
EXISTS(Smith)
A predefined query where the Opportunity is the business component must use the following format:
Opportunity.Search = "EXISTS ([Last Name] = ""Smith"")"
A search specification that resides in the Search Specification property of the business component or applet must use the following format:
EXISTS ([Last Name] = 'Smith')
The following example chooses records according to multiple child and grandchild criteria. It defines a more complex query and demonstrates that a query can use all the business components involved in the view without specifying the business component that contains the field:
EXISTS ([ChildField1] = 'X' AND [ChildField2] = 'Y') EXISTS ([GrandchildField1] = 'A' AND [GrandchildField2] = 'B')
You can add NOT in front of EXISTS to query for everything other than the value that you include after EXISTS.
Using the EXISTS Operator with a Primary
If a multivalue field includes a primary ID field, and if the Use Primary Join property of the Multi Value Link that this multivalue field references contains a check mark, and if the search specification:
Includes EXISTS. The query results include every record in the multivalue group that matches the search specification.
Does not include EXISTS. The query results include every primary record in the multivalue group that matches the search specification.
If you do not specify a primary ID field for the multivalue group, or if the Use Primary Join property does not contain a check mark, then the query must include EXISTS.
If you specify a query that does not use EXISTS, then the object manager automatically inserts EXISTS as part of the search specification. If the field value for a child record is empty, then the object manager uses the EXISTS clause to get the parent record. It does not get any parent record that does not include a child. You can use the following code to query for parent records that do not include a child:
NOT EXISTS(*)
To query a multivalue group, Siebel CRM specifies a value for a multivalue group or multivalue field for the primary value, by default. For example, if you use the following value to query the Account Team, and if the multivalue group supports a primary, then Siebel CRM returns all records that contain VSILVER as the primary position on the team:
VSILVER
If a view includes sales team visibility, then you must not use query by example to constrain the account team. Instead, you can use a view that includes the All visibility filter. For example, assume you log in as SADMIN, navigate to the My Accounts view, and then query the Account Team with a login name, such as VSILVER. In this situation, Siebel CRM does not return all accounts where SADMIN is on the team and VSILVER is the primary.
Expressions
This topic describes expressions you can use in a calculated field. It includes the following topics:
Using Functions in the Predefault and Postdefault Properties
Using Expressions In the Calculated Field and Field Validation Properties
A calculated expression is a type of expression that includes a calculated field and a validation expression.
You can use the following functions to set a predefault or postdefault value in a business component. Siebel VB or COM objects do not support these functions:
AccountId
ContactLogin
JobTitle
OrganizationId
You cannot use a custom function in a calculated expression.
You can only use numbers between negative 2147483647 and 2147483648 in a field validation expression.
For more information, see Siebel VB Language Reference.
Functions You Can Use in a Calculated Expression
The following table describes the functions that you can use in a calculated expression. The Result column describes the type of result the function returns. The Query column indicates if Siebel CRM can use the function in a query.
Table Functions You Can Use in a Calculated Expression
| Function | Result | Query | Description |
|---|---|---|---|
AccountId |
String |
Yes |
Returns the account ID of the current user. |
ContactLoginId |
String |
Yes |
Returns the contact ID of the current user. If you configure Siebel CRM to not use the contact login method for a Web application, then this function cannot get any value and it returns an empty string. It is recommended that you use a contact login and an external security authentication service. For example, LDAP. |
Count ("mvlink") |
Integer |
No |
Returns the number of rows in the multivalue group that the mvlink defines, where mvlink identifies the name of a multivalue link. |
Currency |
String |
Yes |
Returns the currency code for the current position. For example, USD. |
DivisionId |
Integer |
Yes |
Returns the division ID of the current user. To limit visibility to employees who reside in the same division as the person who is currently logged in, you can add the following code to the Search Specification property of the applet: [Division Id] = DivisionId() |
DivisionName |
String |
Yes |
Returns the division name of a user who is an employee. You can use this function to do the following:
You can create a new calculated field so that Siebel CRM displays the division name in the calculated field. It displays the division name of the user who is logged in when this user creates a service request. If you use the configuration that the Using the Division Functions topic describes, then Siebel CRM predefaults the Reported By Division joined field to this division name, and this field never receives another value after Siebel CRM creates this service request. |
EXISTS |
String |
Yes |
For example: IIf(EXISTS([Participant-Employee Login] = LoginName()), "Y", "N") |
GetProfileAttr ("Attribute") |
String |
Yes |
Returns the value that Siebel CRM stores in the profile attribute if this attribute is defined. You can use this function in personalization to do the following:
You can set a personalization attribute for a session that is equal to the value of the shared global attribute, and then reference the personalization attribute in a calculated field. GetProfileAttr returns NULL for an undefined attribute or for an attribute that is not configured. For example: GetProfileAttr("Attribute") = "" If the attribute does not exist, or if it exists and if the value is different than "", then this code returns FALSE. For example: GetProfileAttr("Attribute")
IS NULL If the attribute does not exist, then this code returns TRUE. |
IfNull (expr1, expr2) |
Type of expr1 |
Yes |
Returns one of the following values:
|
IIf (testExpr, expr1, expr2) |
Type of expr1 |
No |
Returns one of the following values:
If the expression references a DTYPE_NUMBER field, then the data type of expr1 determines the data type of the value that this function returns. For more information, see Using the IIf Function. |
InvokeServiceMethod ("ServiceName", "MethodName", "InputProp1=val1, InputProp2=val2, ...", "OutputProp") |
String |
No |
For more information, see Using the InvokeServiceMethod in a Calculated Field. |
JobTitle |
Integer |
Yes |
Returns the Job Title of the employe who is currently logged in. It is similar to the PositionId and DivisionId methods. |
JulianDay |
Integer |
Yes |
Equal to the Oracle Julian Day for all dates in the 20th and 21st centuries. |
JulianMonth |
Integer |
Yes |
Equal to: JulianYear()* 12 + currentMonth where:
|
JulianQtr |
Integer |
Yes |
Equal to: JulianYear() * 4 + currentQuarter where:
|
JulianWeek |
Integer |
Yes |
Equal to the following, rounded down to the next integer: JulianDay()/ 7 |
JulianYear |
Integer |
Yes |
Equal to the current year plus 4713. |
Language |
String |
Yes |
Returns the language code for the language that the client currently uses. For example, ENU. One of the following items sets this language:
This setting is not the OM - Resource Language Code server parameter that the Administration - Server Configuration screen contains. |
Left (text, integer) |
String |
Yes |
Returns the first n characters in the text string or field. For example, the following code returns Adam: Left
("Adams", 4) |
Len |
String |
Yes |
Returns the length of a string or string variable. You can specify the number of characters between parentheses. |
Locale |
String |
Yes |
Returns the locale code that is associated with the Application Object Manager (AOM). A locale is a set of rules that determine how Siebel CRM displays data to the user or receives data from the user. Siebel CRM stores locale codes in the LOCALE_CODE column of the S_LOCALE table. For more information, see Siebel Global Deployment Guide. |
LocalCurrency |
String |
Yes |
Returns the currency code for the client computer. For example, JPY. |
LoginId |
String |
Yes |
Returns the login ID. For example, 0-3241. |
LoginName |
String |
Yes |
Returns the login name. For example, BSTEVENS. |
Lookup (type, value) |
String |
No |
Returns the value of the ORDER_BY column of a row in the S_LST_OF_VAL list of values where:
You can use this function to avoid creating joins in a business component. |
LookupExpr (type, value_expr) |
String |
No |
Returns the value of the ORDER_BY column of the first row in the S_LST_OF_VAL list of values that the expression evaluates to TRUE. To do this, it searches rows in this list of values where the TYPE column matches the type argument. It evaluates the contents of the VALUE column as an expression. This function does an in-memory linear parse evaluate search. You must make sure that the LOV type contains less than 30 rows. |
LookupName (type, lang_ind_code) |
String |
Yes |
Returns the language-independent code that resides in the NAME column of the S_LST_OF_VAL list of values where:
This function gets the untranslated value from this list of values. |
LookupTranslation([fieldname]) |
String |
No |
Returns the value of the field in the language that the client displays. |
LookupValue (type, lang_ind_code) |
String |
No |
Returns the display value that the VAL column contains in a row in the S_LST_OF_VAL list of values where:
This method finds the display value for the lang_ind_code. If it does not find this value, then it returns the value that lang_ind_code contains. This function gets the translation of the untranslated value from this list of values. |
Max ([mvfield]) |
Integer |
No |
Returns the maximum value from a field in child records. You must define the child record that this function examines as a multivalue field that a multivalue group contains. This multivalue group is associated with the business component that contains the field that this method evaluates. For example: Max ([Number of Employees]) This code gets the maximum number of employees for all locations. |
Min ([mvfield]) |
Integer |
No |
Returns the minimum value from a field in child records. You must define the child record that Siebel CRM examines as a multivalue field that is part of a multivalue group. The multivalue group is associated with the business component of the field that Siebel CRM evaluates. For example: Min ([Number of Employees]) This code returns the minimum number of employees that exist for all locations. |
OrganizationId |
Integer |
Yes |
Returns the organization ID of the user who is currently logged in. It returns this value if Siebel CRM does not define an organization for the user. For example, if the user uses a customer application, then it returns the ID of the Default Organization. |
OrganizationName |
String |
Yes |
Returns the organization name of a user who is an employee. |
ParentBCName |
String |
Yes |
Returns the name of the parent business component of an active link. For example, Opportunity. |
ParentFieldValue (field_name) |
String |
Yes |
Returns the value of the field_name field that resides in the parent business component. It returns the result as a string. If Siebel CRM updates the parent row, then it does not modify this result. You must use Link Specification = TRUE to export the parent business component field. If a parent business component does not exist, or if Siebel CRM does not instantiate it, such as in a script, then this function returns the following error: No active link |
PositionId |
String |
Yes |
Returns the position ID of the user who is currently logged in as an employee. For example, 0-4432. |
PositionName |
String |
Yes |
Returns the position name of the user who is currently logged in as an employee. |
Preference ("category", "pref_name") |
String |
Yes |
Returns the value for a category and preference name. For example, the Price List field in the Service Agreement business component contains the following predefault value: Expr: "Preference
(""Quote"", ""PriceList"")"This example returns the price list that Siebel CRM uses for a quote that the Price List Sales Methodology view of the User Preferences screen specifies. This view resides in the client. |
Right (text, integer) |
String |
Yes |
Returns the last n characters in the text string or field. For example, the following code returns dams. Right
("Adams", 4) |
RowIdToRowIdNum ([Id]) |
String |
Yes |
Converts an alphanumeric row ID to a unique, numeric row ID in the Service Request business component. |
Sum ([mvfield]) |
Integer |
No |
Sums the values from a field in child records into a field in a parent record. You must define this child record as a multivalue field that is part of a multivalue group. This multivalue group is associated with the business component that contains the field in the parent record. |
SystemPreference ("Preference") |
String |
Yes |
Returns the value of a system preference. For example: SystemPreference("Training: Employee Calendar")This code returns TRUE for an employee. You can use this function to set a predefault or postdefault value. For example, the following code sets a predefault value: Expr: 'SystemPreference("Default
Time Zone")'You can set these values in the following ways:
|
ToChar ([field_name], 'format') |
String |
No |
Returns a string that represents a number or date in a format that the optional ToChar([Start Date], 'MM/DD/YYYY') This code returns the starting date of a record as a string that uses the following format: MM/DD/YYYY The following examples describe the difference between using # and 0 for a number that does or does not include decimal places. The # symbol only returns decimal places if they exist. The 0 symbol adds decimal places if the number contains fewer decimals than the format argument specifies:
If you do not specify the |
Timestamp |
Date Time or UTC Date Time |
Yes |
For more information, see Using the Timestamp Function. |
Today |
Date |
Yes |
Returns the date for today. For example, 1/26/2012. The Today function and the Timestamp function functions might return different results. For more information, see Using the Timestamp Function. |
UtcConvert ("utc_date_time", "time_zone") |
Date Time |
Yes |
This function converts UTC time to local time in the time zone that the time_zone argument specifies. For example: UtcConvert("12/14/2012
5:07:05 PM", "Eastern Standard Time")This code returns the following value: 12/14/2012 12:07:05 PM |
UtcOffset ("utc_date_time", "time_zone") |
Integer |
Yes |
Returns the UTC offset in minutes for a date and time in the time zone that the time_zone argument specifies. It returns one of the following values:
For example: UtcOffset("3/30/2012 14:00:00", "Pacific Standard
Time")This code returns the following value because 3/30/2012 14:00:00 occurs 7 hours behind UTC: -420 For example: UtcOffset("3/30/2012
14:00:00", "India Standard Time") This code returns the following value because the time occurs 5.5 hours ahead of UTC: 330 |
Using the Division Functions
This topic includes examples that use the division functions.
Using the Division Name Function to Display the Division Name
This example describes how to create a calculated field that displays the division name of the user who is currently logged in. If this user creates a service request, then Siebel CRM displays this field.
To use the Division Name function to display the division name
In Siebel Tools, in the Object Explorer, click Business Component.
In the Business Components list, locate the Service Request business component.
In the Object Explorer, expand the Business Component tree, and then click Field.
In the Fields list, create a new field using values from the following table.
Property Value Calculated
TRUE
Calculated Value
DivisionName()
Name
Division (Calc)
Parent Name
Service Request
Type
DTYPE_TEXT
In the Fields list, create another new field using values from the following table.
Property Value Column
ATTRIB_03
Join
S_SRV_REQ_X
Name
Reported By Division
Pre Default Value
Field: 'Division Name'
Read Only
TRUE
Display the field you created in the relevant applets.
Searching or Sorting According to the Division
You can use the DivisionId function and DivisionName function in a search specification, sort specification, or calculated value. For example, assume Siebel CRM must display only the employees who reside in the same division as the person who is currently logged in. To configure this requirement, you can add the following DivisionId function to the Search Specification property of an applet:
"DivisionId() = [Division Id]"
These functions are not available in a script. To get the division ID or division name in a script, you must use the GetFieldValue business component method. For example, you can use the following code in a Siebel eScript script to get the division ID:
var oEmpl = TheApplication().GetBusObject("Employee");
var bcEmp = oEmpl.GetBusComp("Employee"); bcEmp.ActivateField("Division
Id"); bcEmp.ActivateField("Login Id"); bcEmp.SetSearchSpec("Login Id",
TheApplication().LoginId()); bcEmp.ExecuteQuery(ForwardOnly); bcEmp.FirstRecord; var
divId = bcEmp.GetFieldValue("Division Id");
Using the IIf Function
This topic describes using the IIf Function with string concatenation. The following example assigns an expression to the Calculated Value property:
[Field] Name = "Full Name" TextLen = 102 // Last Name + First Name + 2 Calculated = "TRUE" CalculatedValue = "[Last Name] + "," + [First Name]"
Double quotation marks must enclose the "," string constant because double quotation marks enclose the entire value. If the Last Name field contains NULL, and if the First Name contains Bob, then the Full Name field contains the following value:
, Bob
Consider the following example:
CalculatedValue = "[Last Name] + IIf ([Last Name] IS NULL, "", ",") + [First Name]"
If the Last Name field contains NULL, and if the First Name field contains Bob, then the result contains the following value:
Bob
The CalculatedValue expression must reside on one line.
Using Julian Functions
The Julian functions must include the Today function or a field name as an argument. For example:
JulianMonth([Created]) (of a field) or JulianMonth(Today()) (of the current date).
The following example includes the JulianMonth function in a predefined query to get the opportunities that Siebel CRM closed during the previous month:
'Opportunity'.Search = "JulianMonth([Close Date]) = JulianMonth(Today()) - 1"
The following example returns all service requests that include a commit time that occurs two days in the future:
'Service Request'.Search = "JulianDay([Commit Time]) = JulianDay(Today()) + 2"
The following example sets a variable to the integer value of the current month:
currentMonth = JulianMonth(Today()) - JulianYear(Today()) * 12
Using the Timestamp Function
The Timestamp function returns the date and time for today. For example:
01/02/2012 11:15:22
You can use this function in a query. For example:
Created >= Timestamp() - 0.1
This code returns records that Siebel CRM created in the last one-tenth of a day.
Any expression that uses the TimeStamp function uses the date and time from the user computer even if the Siebel Server computer uses a different time zone.
The Today function and the Timestamp function might return different results. The TimeStamp function does UTC (universal time code) conversion. The Today function does not do UTC conversion.
Configuring Siebel CRM to Do Calculations with the Timestamp Function
Use the Timestamp function for fields of type DTYPE_DATETIME and DTYPE_UTCDATETIME. If you configure Siebel CRM to do a calculation that involves seconds, then it is recommended that you use at least five significant figures for accuracy.
You can configure Siebel CRM to do calculations with a date and time field in a calculated field. If the user enters a number in a date and time field, then:
Integers and hours represent days.
Fractions represent minutes and seconds.
For example, to add one minute to the current date and time, you can use the following expression, where one day contains 1440 minutes:
Timestamp() + 1/1440
In this example, Siebel CRM adds the product delivery interval, in seconds, to the current date and time:
Timestamp() + [Product Delivery Interval]/86400
You must set the Type property of the calculated field to DTYPE_DATETIME or DTYPE_UTCDATETIME.
Using Functions in the Predefault and Postdefault Properties
The Pre Default Value property of a field assigns a value to this field for a new record. If Siebel CRM displays this field, and if it is not set to Read Only, then the user can modify this field. For example, the Currency Code field contains the following predefault value:
System: Currency
Siebel CRM sets the currency code for a new contact to the default system currency.
If you set the predefault value for a date and time field, then you must not use quotes to enclose the value. For example, assume you use the following predefault value for a DTYPE_UTCDATETIME field:
"07/31/2007 23:59:59"
This configuration results in an error message that is similar to the following:
The value "'07/31/2007 23:59:59'" cannot be converted to a date time value.(SBL-DAT-00359)
The Post Default Value property of a field assigns a value to a field before Siebel CRM writes the record to the database. For example, the Personal Contact field contains a postdefault value of N. If the user does not designate a new contact as personal, then Siebel CRM assumes that it is not a personal contact.
If the Type property of a field contains any of the following values, then this field is a date and time field:
DTYPE_DATE
DTYPE_DATETIME
DTYPE_TIME
DTYPE_UTCDATETIME
The following table describes the format that Siebel CRM uses in a function that references a predefault or postdefault field.
Table Format That Siebel CRM Uses in Functions That Reference Predefault or Postdefault Fields
| Function | Result Type | Description |
|---|---|---|
Expr: 'Timestamp()' |
Date Time or UTC Date Time |
Returns the date and time. This expression is equivalent to the Timestamp function that returns the current date and time. It returns the same value as System: Timestamp. For example: Expr: 'Timestamp()' + 0.041667 This code returns the current date and time plus one hour. For more information, see Using the Timestamp Function. |
Expr: 'Today()' |
Date |
Returns the day. This expression is equivalent to the Today function. It returns the current date. It returns the same value as System: Today. For example: Expr: 'Today() - 1' This code returns the date for yesterday. You can use the Today function only with a DTYPE_DATE field. If you use it with a DTYPE_DATETIME or DTYPE_UTCDATETIME field, then Siebel appends the following value to the current date: 12:00:00 AM |
Field: 'FieldName' |
String |
Returns a value that the FieldName field of the current business component contains. If FieldName is a joined field, then Field: 'FieldName' does not work in the Predefault Value property. You must make sure the field that Siebel CRM defaults and the referenced field are the same field type. For example:
12:00:00 AM |
Parent: 'BusComp.Field', 'BusComp.Field' |
String |
Returns the value that a field in the parent business component contains. To default a value, the Link Specification property of the field in the parent business component must be set to TRUE. You can include multiple BusComp.Field arguments where a comma separates each argument. Siebel CRM examines these arguments from first to last until it finds a value. For example: Parent: 'ServiceRequest.Account', 'Account.Name' You must include a space after each comma that separates a field. If the business component name includes an apostrophe, then you must enclose the name with double quotation marks. For example: Parent: "FINS AG Agent's Contracts.Status Of Contract" You can use a System call to terminate a chain of parent calls. For example: Parent: 'Opportunity.Currency Code', 'Account.Currency Code', System: Currency |
System: Creator |
String |
Returns the login name. For example, BSTEVENS. |
System: CreatorId |
String |
Returns the login ID. For example, 0-3241. |
System: Currency |
String |
Returns the currency for this position. For example, USD. The Currency field in the Divisions or Organizations view of the Group Administration screen sets this currency. If the division uses a currency that is different than the currency that the organization uses, then Siebel CRM uses the division currency. |
System: LocalCurrency |
String |
Returns the currency that the client computer uses. For example, JPY. |
System: OrganizationId |
String |
Returns the organization ID. For example, 1-24E1. |
System: OrganizationName |
String |
Returns the organization name. For example, Siebel Service. |
System: Position |
String |
Returns the position name. For example, VP of Sales. |
System: PositionId |
String |
Returns the position ID. For example, 0-4432. |
System: Timestamp |
Date Time or UTC Date Time |
Returns the date and time for today. For example: 04/02/12 11:15:22 If you use the System: Timestamp function as the predefault value for a field, then the data type for this field must be DTYPE_DATETIME or DTYPE_UTCDATETIME. |
System: Today |
Date |
Returns the date for today. For example: 04/26/12 If you use the System: Today function as the predefault value for a field, then the data type for this field must be DTYPE_DATE. |
Using Expressions In the Calculated Field and Field Validation Properties
The Calculated Value property of a field specifies an expression that Siebel CRM uses to calculate the value of a field. The Validation property restricts the values for a single value field. Siebel CRM evaluates the Validation property only if the user modifies the value in this field in the client. It evaluates the Validation property of the business component fields that the current applet references. If these fields are part of other business components, then Siebel CRM also validates these fields.
Note the following:
The entire expression for the calculated value or validation must reside on one line.
The Calculated Value property and the Validation property can contain no more than 255 characters.
To reference a field value, you must use
[Field Name].You cannot use the Validation property to make the field required if this property references another field value.
Siebel CRM cannot validate a multivalue field.
Elements You Can Use in Calculated Fields and Query By Example
The following table describes the elements that you can use in the Calculated Value property, Validation property, or in a query by example expression.
Table Elements You Can Use in Calculated Fields and Query By Example
| Element | Description |
|---|---|
Calculated value or validation statement |
You can use the following statements:
|
Condition |
You can use the following conditions:
|
Comparison |
You can use the following comparisons:
|
Expression |
You can use the following expressions:
|
Constant |
You can use the following constants for the Calculated Value property, the Validation property, and query by example:
"MM/DD/YYYY"
"HH:MM:SS"
"MM/DD/YYYY HH:MM:SS" To reference a date and time format in a control or list column, you must use the format that the Control Panel specifies. You can use the following constants only for the Calculated Value property, the Validation property, but not query by example:
|
Identifier |
[field name] |
Searching with Query By Example
You can configure Siebel CRM to use query by example (QBE) through list columns or controls as a predefined query or in the Search Specification property. The format is slightly different in the client but in all situations the format is simple BNF (Backus-Naur Format).
Using the InvokeServiceMethod in a Calculated Field
The InvokeServiceMethod method calls a business service from a calculated field and returns OutputProp. If you configure Siebel CRM to call this method, then it is recommended that you use the following guidelines:
This method requires an input argument in the third field. If you do not specify this argument, then the calculated field does not provide a return value and a parsing error results. If the business service method does not require an input argument, then you must provide an argument as a placeholder. For example, if MyMethod does not use an input argument, then you can use code that is similar to the following:
InvokeServiceMethod("MyService","MyMethod", "a=a","MyReturn")
If you configure Siebel CRM to send a field value instead of a string literal as an input argument, then you must enclose the field name in brackets. The following example uses input arguments that specify the value in the Name and Location fields:
InvokeServiceMethod("MyService","MyMethod", "prop1=eval([Name]),prop2=eval([Location])","MyReturn")
Siebel CRM evaluates the following code before it calls the business service:
eval(expression)
The name of the return property in the calculated field, such as MyReturn in these examples, must match the name of a property in the output property set of the business service. If Siebel CRM cannot call the method due to this incorrect format, then it does not create an error and the calculated field is empty.
You must not display a calculation expression that calls a business service in a list applet. Doing so might result in poor performance because Siebel CRM repeatedly instantiates the business service each time it displays the field in the list.
Using Calculated Fields with Chart Coordinates
This example uses calculated fields with chart coordinates. Assume you must set the following coordinates:
0-200
200-999
1000-4999
5000-24999
25000+
To use calculated fields with chart coordinates
Create a calculated field that contains the one relevant value of the five coordinate values.
For example, if the record contains a value of 500, then the calculated field value is 200-999.
To view the coordinates in the chart in the order you require, create a list of values that contains the following values:
0-200
200-999
1000-4999
5000-24999
25000+
How Siebel CRM Handles Data Types During a Calculation
The Type property of a field specifies the data type. A multivalue field gets the data type from the source field. Siebel CRM can convert some types to another type during a calculation. Some operations might create different results with different types. For example, "10" + "10" creates "1010", while 10 + 10 creates 20.
An item that occurs first in a calculation possesses a higher precedent. For example, "10" + 10 creates the following result. Siebel CRM converts the argument that is after the operator to a string:
"1010"
For example, 10 + "10" creates the following result. Siebel CRM converts the argument that is after the operator to a number:
20
The Type property of a field can contain one of the following values. The user cannot query a DTYPE_NOTE field:
DTYPE_BOOL
DTYPE_CURRENCY
DTYPE_DATE
DTYPE_DATETIME
DTYPE_ID
DTYPE_INTEGER
DTYPE_NOTE
DTYPE_NUMBER
DTYPE_PHONE
DTYPE_TEXT
DTYPE_TIME
DTYPE_UTCDATETIME
How Data Type Affects Order of Precedence
If the Calculated Value property references more than one field value, and if these fields use different data types, then the order of the data types can affect the calculation. For example, the Quote Item business component includes the Line Total calculated field. This field uses the following calculated value:
[Item Price] * [Quantity]
where:
The data type of the Item Price field is DTYPE_INTEGER.
The data type of the Quantity field and the Line Total field is DTYPE_CURRENCY.
In this example, if Item Price is 2.25, and if Quantity is 5, then Siebel CRM sets the Line Total to 11.25.
Assume you modify the calculated value of the Line Total field to the following:
[Quantity] * [Item Price]
In this example, if Item Price is 2.25, and if Quantity is 5, then Siebel CRM sets the Line Total to 11.00.
Guidelines for Configuring Calculated Fields
If you configure a calculated field, then use the following guidelines:
You must not use a calculated field to do an update. A calculated field does not support an update for even a simple expression, such as [Field]). The exception is that a specialized business component can override SqlSetFieldValue. For important caution information, see Using Specialized Classes.
If the calculated value of a calculated field is not a DTYPE_TEXT value, then you must explicitly specify the field type.
You must not configure Siebel CRM to store a calculated field in a column.
You must not create validation criteria on a calculated field. Siebel CRM ignores this criteria.
Siebel CRM supports a query on a calculated field. The work that Siebel CRM performs if it does a query on a calculated field depends on the functions that it uses in the calculation. If Siebel CRM:
Can use the function directly in the WHERE clause in the SQL statement, then it uses the function.
Cannot use the function directly in the WHERE clause, then Siebel CRM must examine each record in the business component to identify the records that it displays to the user. This configuration impacts performance. The IIf function and Lookup function are examples of functions that Siebel CRM cannot use in the WHERE clause.
You must not configure Siebel CRM to sort calculated fields.
You can configure Oracle’s Siebel CRM so that a calculated field references the results of another calculated field in the same business component.
To place a link in a calculated field, you can use the following HTML tag:
<a href= …></a>