Lookup

About Lookups

A Lookup is a type of Field in an Object.

Lookup fields establish relationships between objects, which enables the creation of very complex data models in applications. For example, in the Orders object, a Lookup field references the Customers object. This is a One-to-Many relationship, where a single Customer can have many Orders.

In addition to pointing to a target record, you can use the Lookup relationship to create:

Lookup-Derived Fields

Read-only fields that echo data from the target record, as though they were part of the current object record.

Learn more: Lookup-Derived Fields

Lookup-based "Auto-Copy" Fields

Fields in the current object record that are pre-filled with data copied from the Lookup target, but whose values can be modified by the user.

Learn more: Lookup Field Configuration

Considerations

By creating a Lookup field in an object, a corresponding Related Information section is created automatically in the related object. For a given target record in the related object, that section lists all records in the current object that Lookup to the target record.

Learn More: Object Relationships

Note: In addition to the standard Lookup fields that can be selected when creating or editing a Field definition, there are:

Multi Object Lookup - Another field type that lets the user select the Lookup-object, and then select a specific record within that object.
Multi Value Lookup - A field that automatically appears in the Form's field list whenever two objects are joined by a many-to-many relationship. (Such fields are not displayed in the the Fields tab.)

About Objects in Lookup Fields

Any Object can be the target of a Lookup, including:

Users

Users associated with records in the object become clickable
When clicked, the associated User record is opened

Documents

Documents associated with records in the object become clickable
When clicked, the associated Document record is opened

Teams

A lookup field that targets the Team object can be clicked
When clicked, the corresponding Team record is opened

Templates

Use this option when using the sendEmailUsingTemplate call in the Java API. This allows the Template Record Id to be fetched programmatically for Campaigns, which saves time (instead of hardcoding record id).

Fields (Column Headings) in a Related Information Section

In the Customer record, Movies are displayed, with the default field headings (Name, Genre, Rating)

By creating a Lookup field in an object, a corresponding Related Information section appears in a second object. The appearance of the columns in the Related Information Section are predefined, and can be modified.

Example: In the Movies object (A), add a lookup to the Customer object (B), which creates a one-to-many relationship (one Customer to many Movies)

The columns displayed in the Related Information section (of Object B) are auto-selected, based on the type of object:

In Lookups to Custom Objects, the following fields are displayed as column headings:

The first three fields of the object, selected based on date of creation
Custom Controls are excluded from use as column headings

In Lookups to Built-in or CRM objects, the following fields are displayed as column headings:

Name
Created By
Date Created

In Lookups to Contacts, the following fields are displayed as column headings:

First Name
Last Name
Created By
Date created

Create a Lookup Field

To add a Lookup field:

Visit Designer > Objects > {object} > Fields
Click [Add Field]
Fill in the Field Properties, shown below.

Field Properties

This section describes the properties you can set when creating a Lookup field.

Summary

Basic Information
Default Value
Lookup Field Configuration
Lookup Display Type
Record Selection Information - Specify selection criteria for the records to display in the Lookup Window
Post Selection JavaScript - Perform optional data validation, messaging or actions after the lookup record is selected
Role-Based Field Visibility - Show or Hide this field in the Form, based on the selected Role
Description Information - Add a description of the field or the Extended Metadata

Basic Information

Label

Label that is displayed in data entry forms; Learn more: About Display Labels

Display Type

Select Lookup

Master Detail Relationship

This option appears only for legacy Lookup fields that specified the (now deprecated) master-detail relationship.

Learn more: Master-Detail relationship

Object Name: Choose an object (Customers, in this example); Learn more: About Objects in Lookup Fields
Always Required: Click the checkbox to make the field required.
Field Name: Name of the field, used internally in the database

Default Value

Paste in a record ID, or create a function expression that resolves to a string that contains one.

Considerations

If the string does not match a valid record, the field remains empty.
Default values do not trigger Post Selection JavaScript

Note:
You can use the LOOKUP function to return the record_id field for a record that matches specified criteria. (For the User object, the LOOKUP function returns a text string--which is what you need, in this context. For all other objects, it returns a number. To convert that numeric value to a string, wrap the LOOKUP function in a TEXT function: TEXT(LOOKUP(...)).)

Lookup Field Configuration

Lookup Display Type

Popup
Drop Down

Learn more: About Lookup Display Types

Additional Fields to Display: The Additional Fields are displayed in a Lookup Window when the Lookup icon is selected from a record. Select the fields to display. Use the arrow buttons to move the objects to the Selected Fields list.

Copy Data: When you create a Lookup field, you can also specify a mapping between fields in the target and the current object, as explained below.

Lookup-Based Auto-Copy of Field Data

When the user chooses a target record, you can automatically copy data in the target fields to fields in the current record, prefilling them with data.

To copy data:

Choose fields in the target record to copy into the current record, and specify the field to copy them to.
Click [+ Add More] to specify additional fields to copy.

Considerations

Fields are only copied when they are part of a form. If a form does not contain an auto-copy field, then selecting a Lookup target does not copy data to that field.

The field can be present in the form, but hidden. In that case, the auto-copy happens.
To be sure that such fields are created in future forms, consider making the auto-copy field Always Required.

When adding a record, if the user enters data into a copied-data field and then does the lookup, the field is overwritten. To minimize the chance of that happening, make sure that such fields come after the Lookup field, they way they do in the Default Form. (You can also make the field read-only, if the user never really needs to change it.)
When updating a record, the auto-copy only happens when the Lookup target record changes (or is re-selected).
Copied data in the current record does not change when the lookup-target record changes. (If the sales tax changes for a state, for example, the change does not affect records that already copied the previous tax rate.)
When updating a record, copied data in the current record is only changed if:

A new record is selected in the Lookup field
The current form contains the field (even if it is hidden).

(For fields that are updated whenever the current record is updated, use Lookup-Derived Fields.)

Auto-Copy is not supported in the System Objects (Task, User, Appointment, Document)
The built-in Standard Fields like created_by and workflow_state can never be the target of an auto-copy.
There are restrictions on the field mappings that are allowed.
Learn more: Auto-Copy Field Mappings

Record Selection Information

In this section, you control the records that are displayed in the Lookup Window for the user to choose from.

Record Selection Type

Standard Lookup: (default) The choice is made directly from the target object, using the platform's standard Lookup Window.
Custom Page: The choice is made using custom Page that provides a customized equivalent of a Lookup Window in the GUI.
Learn more: Using a Custom Page for a Lookup

View/Report

Select a View or Report (based on this object) that defines the records to display

This option only appears if the target option for the Lookup is a Custom Object
Views and Reports have a built-in record selection criteria functionality, providing a powerful filter for the Lookup Window.
Optionally, use Reports based on Database Views for cross-object record filtering.

Hierarchical View

This option is used for objects that have self-referential lookups. Since it is possible for an object to have more than one self-referencing lookup field, you choose the Lookup field to use when creating the hierarchy.

For example:

A "RestaurantFinder" object could have records for restaurants, major cross-streets, and cities, where a location field points to a parent record. Selecting that field would construct a hierarchical view that displays cities at the top level, with major cross-streets under them, and nearby restaurants under that.
At the same time, a self-referencing category field could create a category hierarchy. For example, the "Service" category could contain "Fast Food" and "Slow Food", while the "Nationality" category contains "Oriental", "Indian", and "Italian". Selecting that field for the view would create a category-based hierarchy in the Lookup display. (Note that the same restaurant could appear in multiple categories.)
Note:
To maintain sanity when adding and inspecting records, there would typically be a type field, where the possible values might be "Restaurant", "Location", or "Category", but in the Lookup view, you would only display a name field. The resulting views might then look like one of these:

Location Hierarchy	Category Hierarchy
[+] New York [+] Theatre District [-] Sardis [+] Chicago [+] Downtown [-] The Bakery	[+] Service [+] Slow Food [-] Sardis [-] The Bakery [+] Fast Food [-] ...

Sort Options: These options appear for Custom Objects when the Record Selection Type is "Standard".

Sort by: Select the column on which the records will be sorted, when displayed to the user for choosing.

Sort Order: Specify Ascending or Descending sort.

Record Selection Criteria: Optionally, add filters using Fixed String Values or Dynamic Filters as criteria to determine the records to display in a Lookup Window.

Considerations

The available Record Selection Types can vary, depending on the objects used to build the Lookup Field:

Lookup Scenario Record Selection Types Available:

Built-in or CRM Object --> Built-in or CRM Object Standard, Custom Page

Built-in or CRM Object --> Object Standard, Custom Page, View/Report

Built-in or CRM Object --> User Object Standard, Custom Page

Object --> Built-in or CRM Object Standard, Custom Page

Object --> Object Standard, Custom Page, View/Report

Object --> User Object Standard, Custom Page, View/Report

If Record Selection Type is Custom Page, then the following sections are not displayed:

Record Selection Criteria

Post Selection JavaScript

If Record Selection Type is Standard Lookup or View/Report, then the following sections are displayed conditionally:

Lookup Scenario Record Selection Criteria Section Post Selection JavaScript Section

Built-in or CRM Object --> Built-in or CRM Object

Built-in or CRM Object --> Object

Built-in or CRM Object --> User Object

Object --> Built-in or CRM Object

Object --> Object

Object --> User Object

Fixed String Values

Fixed string values define a query with specific, fixed criteria

Use fixed string values when a simple lookup is needed for record selection criteria

This method "hard codes" the criteria, meaning that only the specified values are used

Over time, the string values may need to be changed, so it is important to consider how this criteria is maintained for the long term

Examples

Display records with Zipcode = 90210 and Lastname begins with 'B'

Display records with Membership Status = Lapsed

Display records with State = CA

Dynamic Filters

Dynamic Filters define a query with field variables, enabling more powerful controls.

Use Dynamic Filters when complex associations are needed for Record Selection Criteria

How it Works

In a Technology Asset record, display a list of Technicians certified to support or repair the equipment

For an Inventory Application, add a field to a Part Record that finds only component parts (subcomponents of the Part)

For a Product or Service Ordering Application, add a field to a Brand Record that limits the lookup to products that belong to the brand family

In a Candy Manufacturing Application, add a field to a Recipe Record that identifies organic ingredients only

Guidelines for building Dynamic Filters

The format of a Dynamic Filter changes, depending on the type of field:
Lookup

Other (text, date, other alphanumeric field)

To specify Dynamic filters in the UI, insert a derived field name in the Value field of the Record Selection Criteria builder
To create a derived field name, include a dollar sign ($) before the internal field name (this identifies the value as a dynamic filter)

If a Lookup field is chosen, use the $fieldname_name format

For all other fields, use $fieldname

Where fieldname can be found in Field Name

Examples

In a Lookup field of Object "Employee", to restrict the Employees available depending on the Location, set filter to {0} in the Location field. If the Location is any other field type, set filter to {1} in the Location field.

An IT assets object is linked to the Service Techs object, which includes staff members who are responsible for support

Service Techs have an Area of Responsibility that matches one of the available IT asset Types: "Laptop", "Desktop", or "Server"

The Service Tech object is defined as follows:

In the IT asset object, the Lookup field to the Service Tech object includes Record Selection Criteria that limits the lookup to Service Techs with Areas of Expertise that match the Asset Type:

When assigning the asset to a user, a Service Tech is selected, and the list of Service Techs displayed in the Lookup Window shows only the techs with expertise in the required area of technology

User-specific template variables

Record data can be compared to the User object data for the current user. Those criteria have the form:
{field_name} {operator} $user.{User_object_field}

For example:
Owner equals $user.id - Records owned by the current user

Created By equals $user.id - Records created by the current user

Modified By equals $user.id - Records modified by the current user

City equals $user.city - Records in the current user's city

Similarly, were you to add a custom hiredate field or birthday field to the User object, you select records that came before or after that date using the less than and greater than operators.

Label Template Variable Comment

Current Date $today Current date in Company date format

Current Date User Date Format $today_dateformatted Current date in User's date format

User Id $user.id

User Full Name $user.fullname First name and last name, together

User First Name $user.firstname

User Last Name $user.lastname

User Email Address $user.email

City $user.city

State/Province $user.state

Zip/Postal Code $user.zip

Country $user.country

Title $user.title

Division $user.division

Phone $user.phone

Fax $user.fax

Mobile Phone $user.mobile

Street $user.street

User HTML Signature $user.html_signature

User Text Signature $user.text_signature

User Name $user.username User's login name

All custom fields defined on the User Object

Post Selection JavaScript

To set up Post Selection JavaScript, click the Fields tab, select and edit a field (of type Lookup) and use JavaScript to add additional validations to the Post Selection JavaScript area. After a lookup operation is complete, the validation is performed and the specified action is taken.

Examples

Add a validation to confirm that the Lookup field matches the expected format (date, currency, phone number, etc)
Check that a reservation date is meaningful, i.e., for a future event, not one in the past
Confirm that inventory is available for the requested item

Learn more: Post Selection JavaScript

Role-Based Field Visibility

For each of the Roles available, choose whether the field is Visible or Hidden. All fields are set to Visible by default. Default Roles are installed with the platform and can be modified.

Description Information

Optionally, include a description of the field.

Using the Lookup Icon

A Lookup field can be identified in a form by the appearance of a Lookup icon . The Lookup icon is used in data entry to view and select items from a Lookup Window.

Optionally, use the Auto-Completion for Lookup Fields option for faster data entry in Lookup fields.

Lookup Window

The Lookup window presents a list of records related to the Lookup field.

To open a Lookup Window:

From a Lookup field, click the Lookup Icon.

Use these advanced search capabilities available in the lookup window:

Advanced Options: Filter the records in the lookup window
Customize Layout: Select the fields that appear the lookup window

Quick Add Button

In Accounts, Contacts, Prospects, and all Custom Objects, it is possible to enable a Quick Add button in a Lookup Window.

Learn more: Forms

Click the Add Contact (Quick Add) button to open a popup window based on the selected form layout

Lookup-Derived Fields

About Lookup-Derived Fields

Lookup-Derived Fields are based on Lookup fields. They allow data from a record that is the target of the Lookup to be displayed as though it were part of the current record.

For example, when Object B contains a Lookup field that relates to Object A, Lookup-derived fields can be selected that display as read-only fields (from Object B) within a record in Object A.

Examples

Display a Customer Address in an Order record
Show a list of components associated with a service or product merchandise
Show the contact information for an Employee assigned to an IT asset

Considerations

Lookup-derived fields are read-only. They cannot be modified.

Working with Lookup-Derived Fields

To add Lookup-Derived Fields:

Click Designer > Objects > {object} > Fields tab > {lookupField}
In the Additional Fields to Display section, select additional fields from the Available Fields list, and move them to the Selected Fields column

HowTo:Display Customer Data in an Order as a Read-Only Field

This example shows how to display data from a Customer record as a read-only field in an Order.

About the Process

In outline, a Lookup field that targets Customers is added to the Orders object. In the Lookup definition, Customer fields are then selected as "Additional Fields to Display" lookup. Finally, those fields are placed in a Form in the Orders object.

Note: For an alternative, consider doing a Lookup-Based Auto-Copy of Field Data. That method makes a copy of the data in the current record. You can then edit the value without changing the original data. You might use that feature for the Customer's phone number, for example, to allow a temporary number to be used for today's order.

Comparison of the two methods

Lookup-derived fields are displayed when the user is selecting a target record. Auto-copy fields aren't.
Lookup-derived fields are not automatically included in record forms. You have to add them manually.
Lookup-derived fields are read-only. The contents of Auto-copy fields can be modified.}}

Add a Customer Lookup

If one does not already exist, add a Customer Lookup to the Orders object:

Visit Designer > Objects > Orders
Click the Fields tab
If a Related to Customer lookup field does not already exist, add it, using the following parameters:
- Label - Related to Customer
- Display Type - Lookup
- Object Name - Customers

Specify the Lookup-derived Field (Additional Field to Display)

While adding the field (or editing it, if it already exists), you can now specify the Lookup-derived field. The Customer Name already appears to identify the lookup record, so here you'll specify the company address:

Go to the section Lookup Field Configuration and under that, to Additional Fields to Display
Select Customer Address and move it from the Available Fields list to the list of Selected Fields.
You have now specified Customer Address as a lookup-derived field in the Orders object. (Note that you could choose to copy data to a field, instead.)
Click [Save]

Learn more: Create a Lookup Field

Include the Field in the Record Display Form

The last step is to include the read-only lookup-derived field in the Orders display form:

Visit Designer > Objects > Orders
Click the Forms tab
In the Elements list, under the Fields tab, locate the field you created: Related to Customer Customer Address. (Mentally insert a comma when reading that label, so you see it as "Related to Customer, Customer Address".)
Drag that field into the form, at the location where you want it to be displayed.
Click [Save Changes]
Click the pencil icon next to the field, to edit it.
Change the label that appears in the form:
Display Label - Customer Address.
Click [Save]

Try it Out

From a Customer record, add an order. (That's the easiest way to do it, because the Related to Customer Lookup field is filled in for you.)

In the Workspace, click Customers.
In the Related Information section, where the Orders appear, click [New Record]
Add the information needed to place the order.
Click [Save]
A new record appears in the Orders section of the Customer record.
Click the folder icon for the Order.
The Order record appears, with the Customer Address displayed.
Click [Edit]. Note that the Customer Address field can't be edited.

Layout Specific Filter

For objects that contain multiple Forms, Record Selection Criteria can be expanded to include Layout Specific Filters.

With such a configuration, the records displayed in a Lookup Window are based on a combination of Forms and associated Record Selection Criteria.

Note: If additional Forms and associated Record Selection Criteria are not created, the Layout Specific Filter section does not appear in the Lookup field configuration page

Example: For a Movie Rental application, a number of Forms are defined, based on Team: Sales, Accounting. In the Orders object, a Lookup field is defined that is related to a Movie object (which contains records of all available movies, and includes fields such as Movie Title, Genre, Rating).

When the Sales team opens the Orders object, the customized Sales team Form is displayed. When a salesperson clicks the Lookup

icon, a Lookup Window is displayed that contains only the records that meet the Record Selection Criteria associated with that Form.

To add a Layout Specific Filter:

Create an Object (or edit an existing object)
Create a Lookup Field
Add multiple Forms, via Clone a Form
In the Lookup Field, navigate to the Layout Specific Filter section and click the [New] button
Select a Form and define Record Selection Criteria
Optionally, add Post Selection JavaScript
Click [Save] or [Cancel] to stop the action

Self Reference Lookup

An Object can contain a Lookup field that references the Object itself (i.e. a self reference). This configuration is used to build record hierarchies (parent-child relationships between records) which are displayed in a Related Information section.

About Self Reference Lookups

A self-reference lookup creates a hierarchical record structure. That hierarchy can be used to make it easier for a user to find the record they're looking for:

Here, for example, a designer is adding a new element to a list of sizes (Womens XX-Small). When the designer clicks the Lookup icon next to the Category field, a window opens that displays the existing records in a tree.

When taking an order for a T-shirt, for example, an order-entry clerk might select "Boys Medium". But here, the designer is adding a new record, so the selection is the category (Womens).

When the records are shown in a list view, they look pretty much like any other records:

Selection	Category
Boys	None
Boys Small	Boys
etc.

But when the records are displayed for a Lookup selection, the hierarchy becomes apparent.

Other examples include:

An organization might have multiple subsidiaries, and some of those companies might also have subsidiary companies
Employees have reporting relationships, to managers and subordinates
In manufacturing, parts in a component might have subcomponents (or be part of of a larger component)

Considerations

A Self Reference Lookup field:

Cannot reference itself, only other records in the object
Is not supported in Built-in or CRM objects
Is not supported in lookups to Built-in or CRM objects
Is not supported in Object Inheritance models
Can be deleted from the Fields tab in an Object

Add a Self Reference Lookup Field to an Object

First:

Click Designer > Objects > {object}
Click the Fields tab, click the [New Field] button, and complete the following information:
Label

Label to display in the form layout

Display Type

Lookup

Object Name

Choose the {object} selected in the first step

Field Name

Name of the field (Parent {object} is a common choice)

Section

Choose a section in the form layout (default selection can be used)
Provide any other necessary information in the Basic Information and Display Attributes sections
Click [Save]

Note: Whenever you create a lookup to this object in another object, you'll generally want to edit the Display Attributes section, and set the Record Selection Type to Hierarchical. You may want to do that here, as well, if you are planning to add a deep set of data (to make it easier to select the right parent). However, to select the Hierarchical option for the new self-referencing lookup field, you will have to save it first.

Then:

Click the Forms tab
Scroll to the Related Information Section
Click the Display Settings link in the heading
Move the object labeled: {object} based Hierarchy View to the Visible Objects area
Click [Save]

Display Related Records from the Current Object in a Hiearchy

When a Self Reference Lookup field is added, a new section is added to the Related Information section of each object Form:

The new section is named: <field> based Hierarchy View, where <field> is the self reference Lookup field.
By default, the section is hidden
When visible, this section displays the hierarchy of records that have the current record as the root.

These two diagrams display the difference between a normal related-record list and a related-record hierarchy:

Related Record List	Related Record Hierarchy

On the left is the normal list of related records from the "Boys" record in the Size object. The list shows all of the records that are directly related (Boys Large, Boys Medium, and so on).

On the right is the Hierarchy View--a tree. At the topmost level, that list shows the same records as the view on the left. But here, any entries to that look up to those records can be viewed by clicking on the [+] icons in the tree. So the entire subtree rooted at the current record (node) is available for inspection.

To make the related-record Hierarchy View visible:

Edit an object Form
Got to the Related Information section
Click the Display Settings link in the corner
Move the Hierarchy View from the list of Hidden Objects to the list of Visible Objects

Create a Self Reference Lookup between Records

Navigate to the {object} tab (Company, for example)
Edit a record, select a Parent Company from the lookup field and Save the record
Select the record designated as Parent Company
Scroll down to the Company based Hierarchy View section

Note that child Companies are displayed in the tree, demonstrating the relationships between records in the Company object

Edit an Object based Hierarchy View

To edit an <object> based Hierarchy View:

Click Designer > Objects
Navigate to the object of interest
In the Forms tab, navigate to the Related Information section
Navigate to the <object> based Hierarchy View, click the Edit Related Information icon
Fill in the Object based Hierarchy View settings.
Click [Save]
The <object> based Hierarchy View records are displayed in the Related Information section.

Object based Hierarchy View Settings

Title

Title of the Related Information section

Pre-populated with <object> based Hierarchy View
Optionally, change the Title

Fields to Display

Select fields in the list of those Available, and click the right-arrow button to move them to the Selected column; Optionally, click the up- or down-arrow buttons to change the order in which the fields appear

Sort By

Choose a field to determine the sort order of the records, and choose either Descending or Ascending order

Additional Sort By

Optionally, choose a second-level sort

Choose a field to determine the sort order of the records, and choose either Descending or Ascending order

Display Type

Choose from Tree or Tabular; Tree is default

Tree

Tabular

If Display Type is Tree, the Mode Type option is displayed
Mode Type - Collapsed or Expanded; Collapsed is default

Scrolling Window Size

Define the number of rows to display by selecting a Scrolling Window Size; Choose from:

No Scrolling, Show all Rows
5 Records, Scroll Window
10 Records, Scroll Window
20 Records, Scroll Window
30 Records, Scroll Window
40 Records, Scroll Window
50 Records, Scroll Window

Delete an Object based Hierarchy View

To Delete an Object based Hierarchy View, delete the field that is associated with this Related Information Section.

Visit Designer > Objects > {object} > Fields
Click the field name.
Click [Delete].
A confirmation dialog opens.
Click [OK] to delete the field.

This is a permanent deletion, and cannot be restored.

Lookup Scenario	Record Selection Types Available:
Built-in or CRM Object --> Built-in or CRM Object	Standard, Custom Page
Built-in or CRM Object --> Object	Standard, Custom Page, View/Report
Built-in or CRM Object --> User Object	Standard, Custom Page
Object --> Built-in or CRM Object	Standard, Custom Page
Object --> Object	Standard, Custom Page, View/Report
Object --> User Object	Standard, Custom Page, View/Report

Label	Template Variable	Comment
Current Date	$today	Current date in Company date format
Current Date User Date Format	$today_dateformatted	Current date in User's date format
User Id	$user.id
User Full Name	$user.fullname	First name and last name, together
User First Name	$user.firstname
User Last Name	$user.lastname
User Email Address	$user.email
City	$user.city
State/Province	$user.state
Zip/Postal Code	$user.zip
Country	$user.country
Title	$user.title
Division	$user.division
Phone	$user.phone
Fax	$user.fax
Mobile Phone	$user.mobile
Street	$user.street
User HTML Signature	$user.html_signature
User Text Signature	$user.text_signature
User Name	$user.username	User's login name
All custom fields defined on the User Object