Print      Open PDF Version of Online Help


Previous Topic

Next Topic

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.

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.

Argument

Description

ref_record_type

The language-independent name of the referenced record type. (A language-independent name is a name that does not change when the user language changes.)

The argument must be enclosed in field string identifiers. For example:

'<Account>'

See the table in the Record Types section of this topic for a list of the language-independent record-type names.

foreign_key

A literal or expression that returns a valid row ID in the referenced record type (specified in the ref_record_type argument). If the row ID is invalid, the function returns a zero-length string. Direct field references must be enclosed in field identifiers. For example:

[<AccountId>]

field_name

The language-independent name of a field in the referenced record type (specified by the ref_record_type argument). If the field does not exist in the referenced record type, an error is returned. The field name must be enclosed in field string identifiers. For example:

'<Type>'

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>')

Record Types

In the following table, the Referenced Record Type column shows the language-independent names of the referenced record types that you can look up using the JoinFieldValue function. The Foreign Key column shows the foreign keys that you use to look up the referenced record type. The Record Type column shows the record types that you use to configure workflow rules where the foreign keys are exposed.

Record Type

Foreign Key

Referenced Record Type

Account

ParentAccountId

Account

Account

BookId

Book

Account

SourceCampaignId

Campaign

Account

PrimaryContactId

Contact

Account

CustomObject1Id

CustomObject1

Account

CustomObject2Id

CustomObject2

Account

CustomObject3Id

CustomObject3

Account

TerritoryId

Territory

Account

OwnerId

User

Account Competitor

CompetitorId

Account

Account Competitor

PrimaryContactId

Contact

Account Contact

AccountId

Account

Account Contact

ContactId

Contact

Account Contact Role

ContactId

Contact

Account Note

AccountId

Account

Account Team

AccountId

Account

Account Team

UserId

User

Accreditation

BookId

Book

Activity

AccountId

Account

Activity

BookId

Book

Activity

CampaignId

Campaign

Activity

PrimaryContactId

Contact

Activity

CustomObject1Id

CustomObject1

Activity

CustomObject2Id

CustomObject2

Activity

CustomObject3Id

CustomObject3

Activity

DealerId

Dealer

Activity

FundRequestId

FundRequest

Activity

LeadId

Lead

Activity

MedEdEventId

MedEdEvent

Activity

OpportunityId

Opportunity

Activity

PortfolioId

Portfolio

Activity

ServiceRequestId

ServiceRequest

Activity

DelegatedById

User

Activity

OwnerId

User

Application

BookId

Book

Assessment

ContactId

Contact

Assessment

LeadId

Lead

Assessment

OpportunityId

Opportunity

Assessment

ServiceRequestId

ServiceRequest

Asset

ManufacturerId

Account

Asset

AccountId

Account

Asset

CustomerContactId

Contact

Asset

ProductId

Product

Auto Dealer Makes

AccountId

Account

Auto Sales Hours

AccountId

Account

Auto Service Hours

ProductId

Product

Business Plan

BookId

Book

Call Product Detail

ProductId

Product

Call Sample Dropped

OwnerId

User

Campaign

BookId

Book

Campaign

CampaignId

Campaign

Campaign

CustomObject1Id

CustomObject1

Campaign

CustomObject2Id

CustomObject2

Campaign

CustomObject3Id

CustomObject3

Campaign

OwnerId

User

Campaign Recipients

ContactId

Contact

Campaign Recipients

CampaignContactId

Contact

Campaign Recipients

CampaignId

Campaign

Certification

BookId

Book

Contact

AccountId

Account

Contact

BookId

Book

Contact

SourceCampaignId

Campaign

Contact

ManagerId

Contact

Contact

ContactId

Contact

Contact

CustomObject1Id

CustomObject1

Contact

CustomObject2Id

CustomObject2

Contact

CustomObject3Id

CustomObject3

Contact

TerritoryId

Territory

Contact

OwnerId

User

Contact Note

ContactId

Contact

Contact Team

ContactId

Contact

Contact Team

UserId

User

Course

BookId

Book

Custom Object 01

AccountId

Account

Custom Object 01

ActivitiyId

Activity

Custom Object 01

BookId

Book

Custom Object 01

CampaignId

Campaign

Custom Object 01

ContactId

Contact

Custom Object 01

ParentId

CustomObject1

Custom Object 01

CustomObject2Id

CustomObject2

Custom Object 01

CustomObject3Id

CustomObject3

Custom Object 01

DealerId

Dealer

Custom Object 01

FundId

Fund

Custom Object 01

HouseholdId

Household

Custom Object 01

LeadId

Lead

Custom Object 01

MedEdEventId

MedEdEvent

Custom Object 01

OpportunityId

Opportunity

Custom Object 01

PortfolioId

Portfolio

Custom Object 01

ProductId

Product

Custom Object 01

ServiceRequestId

ServiceRequest

Custom Object 01

SolutionId

Solution

Custom Object 01

OwnerId

User

Custom Object 01

VehicleId

Vehicle

Custom Object 02

AccountId

Account

Custom Object 02

ActivityId

Activity

Custom Object 02

BookId

Book

Custom Object 02

CampaignId

Campaign

Custom Object 02

ContactId

Contact

Custom Object 02

CustomObject1Id

CustomObject1

Custom Object 02

ParentId

CustomObject2

Custom Object 02

CustomObject3Id

CustomObject3

Custom Object 02

DealerId

Dealer

Custom Object 02

FundId

Fund

Custom Object 02

HouseholdId

Household

Custom Object 02

LeadId

Lead

Custom Object 02

MedEdEventId

MedEdEvent

Custom Object 02

OpportunityId

Opportunity

Custom Object 02

PortfolioId

Portfolio

Custom Object 02

ProductId

Product

Custom Object 02

ServiceRequestId

ServiceRequest

Custom Object 02

SolutionId

Solution

Custom Object 02

OwnerId

User

Custom Object 02

VehicleId

Vehicle

Custom Object 03

AccountId

Account

Custom Object 03

ActivityId

Activity

Custom Object 03

BookId

Book

Custom Object 03

CampaignId

Campaign

Custom Object 03

ContactId

Contact

Custom Object 03

CustomObject1Id

CustomObject1

Custom Object 03

CustomObject2Id

CustomObject2

Custom Object 03

ParentId

CustomObject3

Custom Object 03

DealerId

Dealer

Custom Object 03

FundId

Fund

Custom Object 03

HouseholdId

Household

Custom Object 03

LeadId

Lead

Custom Object 03

MedEdEventId

MedEvent

Custom Object 03

OpportunityId

Opportunity

Custom Object 03

PortfolioId

Portfolio

Custom Object 03

ProductId

Product

Custom Object 03

ServiceRequestId

ServiceRequest

Custom Object 03

SolutionId

Solution

Custom Object 03

OwnerId

User

Custom Object 03

VehicleId

Vehicle

Custom Objects 04 - 40

BookId

Book

Deal Registration

BookId

Book

Dealer

BookId

Book

Dealer

CustomObject1Id

CustomObject1

Dealer

CustomObject2Id

CustomObject2

Dealer

CustomObject3Id

CustomObject3

Dealer

ParentDealerId

Dealer

Dealer

PrimaryTerritoryId

Territory

Dealer

OwnerId

User

Dealer Territory

ChannelPartnerId

Dealer

Exam

BookId

Book

Financial Account

BookId

Book

Financial Plan

BookId

Book

Fund

BookId

Book

Fund

CustomObject1Id

CustomObject1

Fund

CustomObject2Id

CustomObject2

Fund

CustomObject3Id

CustomObject3

Fund

FundId

Fund

Fund

PrimaryOwnerId

User

Fund Participant

FundId2

Fund

Fund Request

CampaignId

Campaign

Fund Request

PrimaryContactId

Contact

Fund Request

FundId

Fund

Fund Request

OwnedByEmployeeId

FundRequest

Fund Request

FundOwnerId

User

Fund Request

OwnerId

User

Household

AccountID

Account

Household

BookId

Book

Household

PrimaryContactId

Contact

Household

CustomObject1Id

CustomObject1

Household

CustomObject2Id

CustomObject2

Household

CustomObject3Id

CustomObject3

Household

OwnerGroupId

Household

Household Contact

CampaignId

Campaign

Lead

AccountId

Account

Lead

BookId

Book

Lead

CampaignId

Campaign

Lead

ContactId

Contact

Lead

CustomObject1Id

CustomObject1

Lead

CustomObject2Id

CustomObject2

Lead

CustomObject3Id

CustomObject3

Lead

OpportunityId

Opportunity

Lead

OwnerId

User

Lead

SalesRepId

User

Lead

ReferredById

User

MDF Requests

BookId

Book

MedEd Event

BookId

Book

MedEd Event

ProductId

Product

MedEd Event

OwnerId

User

Objective

BookId

Book

Opportunity

AccountId

Account

Opportunity

BookId

Book

Opportunity

SourceCampaignId

Campaign

Opportunity

KeyContactId

Contact

Opportunity

ConatactId

Contact

Opportunity

CustomObject1Id

CustomObject1

Opportunity

CustomObject2Id

CustomObject2

Opportunity

CustomObject3Id

CustomObject3

Opportunity

DealerId

Dealer

Opportunity

TerritoryId

Territory

Opportunity

OwnerId

User

Opportunity Competitor

PrimaryContactId

Contact

Opportunity Competitor

CompetitorId

Opportunity

Opportunity Contact Role

ContactId

Contact

Opportunity Contact Role

OpportunityId

Opportunity

Opportunity Note

OptyId

OpportunityId

Opportunity Partner

PrimaryContactId

Contact

Opportunity Partner

PartnerId

Opportunity

Opportunity Partner

OwnerId

User

Opportunity Team

OptyId

Opportunity

Opportunity Team

UserId

User

Partner

ParentAccountId

Account

Partner

BookId

Book

Partner

SourceCampaignId

Campaign

Partner

TerritoryId

Territory

Partner

ChannelAccountManagerId

User

Partner

OwnerId

User

Partner Program

BookId

Book

Policy

BookId

Book

Portfolio

AccountId

Account

Portfolio

InstitutionId

Account

Portfolio

BookId

Book

Portfolio

PrimaryContactId

Contact

Portfolio

CustomObject1Id

CustomObject1

Portfolio

CustomObject2Id

CustomObject2

Portfolio

CustomObject3Id

CustomObject3

Portfolio

OwnerGroupId

Portfolio

Portfolio

ParentPortfolioAccountId

Portfolio

Portfolio

ParentProductId

Product

Portfolio

ProductId

Product

Portfolio

OwnerId

User

Portfolio Owner

ContactId

Contact

Portfolio Owner

PortfolioId

Portfolio

Product

ParentProductId

Product

Revenue

OpportunityAccountId

Account

Revenue

AssetId

Asset

Revenue

ContactId

Contact

Revenue

OpportunityId

Opportunity

Revenue

ProductId

Product

Revenue

OwnerId

User

Sample Lot

BookId

Book

Sample Transaction

BookId

Book

Service Request

AccountId

Account

Service Request

AssetId

Asset

Service Request

BookId

Book

Service Request

ContactId

Contact

Service Request

CustomObject1Id

CustomObject1

Service Request

CustomObject2Id

CustomObject2

Service Request

CustomObject3Id

CustomObject3

Service Request

DealerId

Dealer

Service Request

ProductId

Product

Service Request

OwnerId

User

Service Request

VehicleId

Vehicle

Solution

CustomObject1Id

CustomObject1

Solution

CustomObject2Id

CustomObject2

Solution

CustomObject3Id

CustomObject3

Solution

ProductId

Product

Special Pricing Request

BookId

Book

Vehicle

AccountId

Account

Vehicle

BookId

Book

Vehicle

PrimaryContactId

Contact

Vehicle

CustomObject1Id

CustomObject1

Vehicle

CustomObject2Id

CustomObject2

Vehicle

CustomObject3Id

CustomObject3

Vehicle

ProductId

Product

Vehicle

SellingDealerId

Dealer

Vehicle

ServicingDealerId

Dealer

Vehicle

OwnerId

User

In addition, the following foreign key fields are common to all record types, and they reference the following user record types:

  • CreatedById
  • ModifiedById

Related Topics

See the following topics for related Expression Builder information:


Published 5/4/2012 Copyright © 2005, 2012, Oracle. All rights reserved. Legal Notices.