Print      Open PDF Version of Online Help

Administering Oracle CRM On Demand > Expression Builder > Expression Builder Functions > JoinFieldValue

JoinFieldValue

A record type can be referenced by another record type through a foreign key. A joined field is a field on a record type that uses a foreign key to reference a field on another record type. For example, on most record types, the OwnerId field is a foreign key that references the User record type. The Owner field on many records is a joined field that uses the OwnerId foreign key to reference the Alias field on the User record type.

The JoinFieldValue function in Expression Builder returns the value of a joined field when the referenced record type and the related foreign key fields are specified in the function. The JoinFieldValue function can also retrieve other fields on the referenced record type that are not directly joined to the referencing object, which allows you to look up fields across record types from related record types.

If the ID field of a referenced record type is present as a foreign key on the record type on which you configure a workflow rule, then you can use the JoinFieldValue function to retrieve field values from the referenced record type.

Also, you can use the following foreign key fields, which are available on all record types, to retrieve field values from the User record type:

• CreatedById
• ModifiedById

NOTE: The JoinFieldValue function cannot return the values of the following fields in the Activity record type: Disbursement Transaction Count, Product Detailed Count, Promotional Items Dropped Count, and Samples Dropped Count. This limitation is due to the way that Oracle CRM On Demand calculates the values of these fields.

Syntax

JoinFieldValue (ref_record_type, foreign_key, field_name)

Result Type

String.

If an error occurs or the requested field value is empty, a zero-length string is returned.

Arguments

The following table describes the arguments for the function.

NOTE: The language-independent field names and the field-name syntax used in any example expressions in this topic are the field names and the field-name syntax that were used in Expression Builder in releases earlier than Release 29 Service Pack 1. These examples are still valid. However, if you select the corresponding fields for an expression from the Fields list in Expression Builder in Release 29 Service Pack 1 or later, then the field names and the field-name syntax will be different from those shown in the examples. It is recommended that you always select the fields for your expressions from the Fields list in Expression Builder, so that the field names and the field-name syntax are always correct. For more information about the field names and the field-name syntax in expressions, see About Expressions.

Examples

Example 1: A business process requires a workflow rule to look up the name of the new owner when the owner of a record is changed. When the owner of a record is changed, only the OwnerId foreign key field on the record is updated. As a result, the following condition on a workflow rule does not look up the new owner of the record:

PRE('<Owner>') <> [<Owner>]

Instead, you can use the following condition in the workflow to look up the new owner of the record:

PRE('<Owner>') <> JoinFieldValue('<User>',[<OwnerId>],'<Alias>')

Example 2: To get the email address of the previous owner of a record, use the following expression:

JoinFieldValue('<User>',PRE('<OwnerId>'),'<EMailAddr>')

Example 3: To get the email address of the current owner of a record, use the following expression:

JoinFieldValue('<User>',[<OwnerId>],'<EMailAddr>')

Example 4: A business process requires that a service request email is sent to a contact. Before the email is sent, the workflow rule checks that the contact has agreed to receive emails. The workflow rule (based on the service request record type) has the following condition:

JoinFieldValue('<Contact>',[<ContactId>],'<NeverEmail>')<> 'Y'

Example 5: To get the territory of the primary account for a service request, use the following expression:

JoinFieldValue('<Territory>', JoinFieldValue('<Account>', [<AccountId>], '<TerritoryId>'),'<TerritoryName>')

Related Topics

See the following topics for related Expression Builder information:

• About Expression Builder
• Using Expression Builder
• Expression Builder Functions