Difference between revisions of "Lookups"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Β 
(100 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[File:lookupexample.png|right|thumb]]
[[File:lookupexample.png|right|thumb]]
Β 
__TOC__
==About Lookups==
A ''Lookup'' is a type of [[Field]] in an [[Object]].
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.
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-Derived Fields]]
''See also:'' [[Multi Object Lookup]]
:*'''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|Lookup Field Configuration]]


;Considerations:
;Considerations:
:* By creating a Lookup field in an object, a corresponding [[Related Information]] section is created automatically in the (second) related object.
:* 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]]
::: ''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:
{{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 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 Layout's field list whenever two objects are joined by a many-to-many relationship. (Such fields are ''not'' displayed in the the '''Fields''' tab.)}}
:* [[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.)}}


==Example: Add a Lookup Field==
===Lookup on a Many-to-Many Relationship===
[[File:Lookuprecordselectorexample.gif|right|thumb|Lookup Window - Lookup a list of customers who have rented a specific movie]]This example adds a Lookup field to the Movies object. The Lookup field relates the Movies object to the Customer object:
{{:Common:Many-to-Many Lookup Considerations}}
#Click '''Designer > Data > Objects > Movies'''
#Click the Fields tab
#Click the '''[Add Field]''' button; Complete the following information:
#;Field Label:Name of the field, used internally in the database
#;Display Label:Label that is displayed in data entry forms; ''Learn more: [[#About Display Labels|About Display Labels]]
#;Display Attributes:Choose from available [[Display Attributes]]
#;Master Detail Relationship:Optional, ''Learn more: [[Master-Detail relationship]]''
#;Object Name:Choose an object (Customers, in this example)
#:''Learn more: [[#About Objects in Lookup Fields|About Objects in Lookup Fields]]
#;Lookup Display Type
#:Choose one of the available options:
#:*Popup
#:*Drop Down
#:''Learn more: [[About Lookup Display Types]]
#;Lookup Type:Choose the Record Selection Type from the available options
#:''Learn more: [[#Record Selection Information|Record Selection Information]] (below)
#:Optionally, click the ''Advanced Options'' link to [[#Customize Lookup Field Options|Customize Lookup Field Options]]


===About Objects in Lookup Fields===
===About Objects in Lookup Fields===
Line 37: Line 32:


===Fields (Column Headings) in a Related Information Section===
===Fields (Column Headings) in a Related Information Section===
[[File:Lookupfieldinrelated.gif|right|upright|thumb|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.
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)
;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)
Line 44: Line 39:
:*In Lookups to {{Type|}}s, the following fields are displayed as column headings:
:*In Lookups to {{Type|}}s, the following fields are displayed as column headings:
::*The first three fields of the object, selected based on date of creation
::*The first three fields of the object, selected based on date of creation
::*[[Custom Control]]s are excluded from use as column headings
:*In Lookups to {{Type}}s, the following fields are displayed as column headings:
:*In Lookups to {{Type}}s, the following fields are displayed as column headings:
::*Name
::*Name
::*Created By
::*Created By
::*Date Created
::*Date Created
:*In Lookups to [[Contacts]], the following fields are displayed as column headings:
::*First Name
::*Last Name
::*Created By
::*Date created


==Customize a Lookup Field==
==Create a Lookup Field==
To add a Lookup field:
#Visit '''[[File:GearIcon.png]] > Customization > Objects > {object} > Fields'''
#Click '''[Add Field]'''
#Fill in the [[#Field Properties|Field Properties]], shown below.
Β 
==Field Properties==
This section describes the properties you can set when creating a Lookup field. (Some the sections do not appear until you have selected and object.)


;Summary:
;Summary:
#[[#Additional Fields to Display|Additional Fields to Display]] - Show these fields in the [[Lookup Window]]
# [[#Basic Information|Basic Information]]
#[[#Display Attributes|Display Attributes]] - Choose the Related Information Section where the Lookup field is displayed
# [[#Display Attributes|Display Attributes]]
#[[#Record Selection Information|Record Selection Information]] - Select criteria for the records to display in the [[Lookup Window]]
# [[#Default Value|Default Value]]
#[[#Post Selection JavaScript|Post Selection JavaScript]] - Perform optional data validation, messaging or actions after the lookup record is selected
# [[#Lookup Field Configuration|Lookup Field Configuration]]
#[[#Role Based Permission Control|Role Based Permission Control]] - Show or Hide this field in the [[Form Layout]], based on the selected [[Role]]
# [[#Lookup Display Type|Lookup Display Type]]
#[[#Description Information|Description Information]] - Add a description of the field or the Extended Metadata
# [[#Record Selection Information|Record Selection Information]] - Specify selection criteria for the records to display in the [[Lookup Window]]
# [[#Post Selection JavaScript|Post Selection JavaScript]] - Perform optional data validation, messaging or actions after the lookup record is selected
# [[#Role-Based Field Visibility|Role-Based Field Visibility]] - Show or Hide this field in the [[Form]], based on the selected [[Role]]
# [[#Description Information|Description Information]] - Add a description of the field or the Extended Metadata


===Additional Fields to Display===
=== Basic Information ===
The Additional Fields are displayed in a [[Lookup Window]] when the Lookup [[File:lookupicon.gif|link=]] icon is selected from a record. Select the fields to display. Use the arrow buttons to move the objects to the Selected Fields list.
:;Label:Label that is displayed in data entry forms; ''Learn more: [[#About Display Labels|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|About Objects in Lookup Fields]]


===Display Attributes===
:;Always Required:Click the checkbox to make the field required.
Choose the section in the [[Form Layout]] where this field is displayed.
Optionally, click the checkbox to make the field required.


===Record Selection Information===
:;Field Name:Name of the field, used internally in the database
In this section, you control the records displayed in the [[Lookup Window]].


;Record Selection Type:
===Display Attributes===
:;Standard Lookup:[[Related Information|Standard Lookups]] (default)
:;Section: Select the section in the default [[Form]] where the Lookup field should appear.
:;Required: Check this box if a value is always required to create a record.


:;Custom Page: Select a custom [[Page]] that provides a customized equivalent of a [[Lookup Window]] in the GUI.<br/>''Learn more: [[Using a Custom Page for a Lookup]]
=== 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]]
{{:Common:UseLOOKUPFunctionToGetRecordID}}


:;View/Report:Select a [[View]] or [[Report]] (based on this object) that defines the records to display
<span id="Lookup_Field_Configuration"><!--GUI Label that should have been changed--></span>
::* This option only appears if the target option for the Lookup is a {{Type|}}
=== Lookup Settings ===
::* Views and Reports have a built-in record selection criteria functionality, providing a powerful filter for the [[Lookup Window]].
<blockquote>
::* Optionally, use Reports based on [[Database View]]s for cross-object record filtering. Β 
====Record Selection====
:* '''Popup:''' A separate window appears showing the lookup data. (default)
:* '''Drop Down:''' A drop down list appears at the current field in the user's window.


:; 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.
:;Considerations:
:: For example:
::* The Drop Down option is available for User objects and Custom objects. It may not be available for other standard objects.
::* A "RestaurantFinder" object could have records for restaurants, major cross-streets, and cities, where a <tt>location</tt> 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.
::* A Drop Down list is limited to 200 entries. If the lookup data is longer than that, a runtime error occurs.
::* At the same time, a self-referencing <tt>category</tt> 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.)<br/>'''Note:'''<br/>To maintain sanity when adding and inspecting records, there would typically be a <tt>type</tt> field, where the possible values might be "Restaurant", "Location", or "Category", but in the Lookup view, you would only display a <tt>name</tt> field. The resulting views might then look like one of these:
::* A Drop Down list should only be used for a list that is short enough to be scanned at a glance. For longer ::lists a Popup is preferable, because the user can type one or more initial characters to navigate the list.


:::{| cellpadding="5" cellspacing="0"
====Additional Fields to Display====
!Location Hierarchy !! Category Hierarchy
The Additional Fields are displayed in a [[Lookup Window]] when the Lookup [[File:lookupicon.gif|link=]] icon is selected from a record. Select the fields to display. Use the arrow buttons to move the objects to the Selected Fields list.
|-
| valign="top"|<syntaxhighlight lang="java" enclose="div"> [+] New York
Β  Β  [+] Theatre District
Β  Β  Β  [-] Sardis


[+] Chicago
====Copy Data====
Β  Β  [+] Downtown
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.
Β  Β  Β  [-] The Bakery</syntaxhighlight> || valign="top"|<syntaxhighlight lang="java" enclose="div"> [+] Service
Β  Β  [+] Slow Food
Β  Β  Β  [-] Sardis
Β  Β  Β  [-] The Bakery


Β  Β  [+] Fast Food
;To copy data:
Β  Β  Β  [-] ...</syntaxhighlight>
# Choose a field to copy from the target record, and select the field to copy to in the current record.
|}
# Click '''[+ Add More]''' to specify additional fields to copy.


;Sort by: Select the column on which the records will be sorted, when displayed to the user for choosing.
:;Considerations:
::* Not all data types are supported. In general, you can copy the simple data types like text, number, and field. But you can't copy more complex data like formulas and attachments.
::* For more extensive operations, use [[#Post Selection JavaScript]]


;Sort Order: Specify '''Ascending''' or '''Descending''' sort.
When you choose this option, the dropdown list shows you the fields that can be copied.
::* 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 on the form for 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|Lookup-Derived Fields]].)
::* Auto-Copy is not supported in the [[User]] object)
::* The built-in [[Standard Fields]] like <tt>created_by</tt> and <tt>workflow_state</tt> can never be the target of an auto-copy.
::*There are restrictions on the field mappings that are allowed.<br>''Learn more:'' [[Auto-Copy Field Mappings]]
</blockquote>


;Record Selection Criteria: Optionally, add filters using [[#Fixed String Values|Fixed String Values]] or [[#Dynamic Filters|Dynamic Filters]] as criteria to determine the records to display in a [[#Lookup Window|Lookup Window]].
===Record Filtering===
When the user clicks the icon next to a Lookup field, the platform's [[Lookup Window]] is displayed, allow the user to choose a record. This section determines how that window appears.


::{| border="0" cellpadding="5" cellspacing="0"
;Sort Options: These options appear for {{type|}}s when the Record Selection Type is "Standard".
|
;Considerations:


*The available Record Selection Types can vary, depending on the objects used to build the Lookup Field:
:;Sort by: Select the column on which the records will be sorted, when displayed to the user for choosing.
::{| border="1" cellpadding="5" cellspacing="0"
!Lookup Scenario
!Record Selection Types Available:
|-
| {{typenolink}} --> {{typenolink}}||Standard, Custom Page
|-
| {{typenolink}} --> Object||Standard, Custom Page, View/Report
|-
| {{typenolink}} --> User Object||Standard, Custom Page
|-
| Object --> {{typenolink}}||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:
:;Sort Order: Specify '''Ascending''' or '''Descending''' sort.
::*[[#3. Record Selection Information|Record Selection Criteria]]
::*[[#4. Post Selection JavaScript|Post Selection JavaScript]]


*If Record Selection Type is ''Standard Lookup'' or ''View/Report'', then the following sections are displayed conditionally:
;Filtering Criteria: If the target of the lookup is a {{typenolink}}, this section does not appear. For other lookups, this section can be used to add filters using [[#Fixed String Values|Fixed String Values]] or [[#Dynamic Filters|Dynamic Filters]] as criteria to determine the records to display in a [[#Lookup Window|Lookup Window]].
::{| border="1" cellpadding="5" cellspacing="0"
!Lookup Scenario
![[#3. Record Selection Information|Record Selection Criteria]] Section
![[#4. Post Selection JavaScript|Post Selection JavaScript]] Section
|-
|width=40%|{{typenolink}} --> {{typenolink}}|| ||align="center"|[[File:Checkmark.gif|link=]]
|-
| {{typenolink}} --> Object||align="center"|[[File:Checkmark.gif|link=]]||align="center"|[[File:Checkmark.gif|link=]]
|-
| {{typenolink}} --> User Object|| ||align="center"|[[File:Checkmark.gif|link=]]
|-
| Object --> {{typenolink}}|| ||align="center"|[[File:Checkmark.gif|link=]]
|-
| Object --> Object||align="center"|[[File:Checkmark.gif|link=]]||align="center"|[[File:Checkmark.gif|link=]]
|-
| Object --> User Object||align="center"|[[File:Checkmark.gif|link=]]||align="center"|[[File:Checkmark.gif|link=]]
|}


====Fixed String Values====
====Fixed String Values====
Line 174: Line 159:


===Post Selection JavaScript===
===Post Selection JavaScript===
{{:Using 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.
===Role-Based Permission Control===
Β 
:;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 Permission Control===
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.
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===
===Description Information===
Optionally, include a description of the field.
Optionally, include text that will appear as a [[Field Hint]] when the field is displayed.


==Using the Lookup Icon==
==Using the Lookup Icon==
Line 198: Line 175:
==Lookup-Derived Fields==
==Lookup-Derived Fields==
{{:Lookup-Derived Fields}}
{{:Lookup-Derived Fields}}
==Layout Specific Filter==
[[File:layoutspecificlookupfilter.gif|right|thumb]]For objects that contain multiple [[Form Layouts]], 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 [[Form Layout]]s and associated Record Selection Criteria.
;Note: If additional [[Form Layout]]s 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 [[Form Layouts]] 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 Layout is displayed. When a salesperson clicks the Lookup [[File:lookupicon.gif|link=]] icon, a [[Lookup Window]] is displayed that contains only the records that meet the Record Selection Criteria associated with that [[Form Layout]].
To add a Layout Specific Filter:
#Create an Object (or edit an existing object)
#Add a Lookup field, via [[#Add a Lookup Field|Add a Lookup Field]]
#Add multiple [[Form Layouts]], via [[Clone a Form Layout]]
#In the [[Lookup]] Field, navigate to the ''Layout Specific Filter'' section and click the [New] button
#Select a Form Layout and define Record Selection Criteria
#Optionally, add [[Post Selection JavaScript]]
#Click '''[Save]''' or [Cancel] to stop the action
:[[File:layoutspecificfilterinalookupfield.gif|none|thumb]]


==Self Reference Lookup==
==Self Reference Lookup==
{{:Self Reference Lookup}}
{{:Self Reference Lookup}}
<noinclude>


<noinclude>[[Category:Core Concepts]]</noinclude>
[[Category:Core Concepts]]
[[Category:Design]]
</noinclude>

Latest revision as of 10:39, 14 March 2017

Lookupexample.png

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

See also: Multi Object Lookup

  • 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

Notepad.png

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

Lookup on a Many-to-Many Relationship

Special considerations for a Lookup on a Many-to-Many Relationship:

  • The Lookup automatically becomes a Multi Value Lookup.
  • It cannot be made read only.
  • Layout Rules cannot test the set of selected values.
  • You can specify a record-filter, to limit the number of possible choices. However, post selection JavaScript is not supported here.
  • When displayed the chosen values cannot be clicked to visit the corresponding records.

About Objects in Lookup Fields

Any Object can be the target of a Lookup, as well as:

  • Users associated with records in the object become clickable
  • When clicked, the associated User record is opened
  • A lookup field that targets the Team object can be clicked
  • When clicked, the corresponding Team record is opened

Fields (Column Headings) in a Related Information Section

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
  • In Lookups to System Objects, the following fields are displayed as column headings:
  • Name
  • Created By
  • Date Created

Create a Lookup Field

To add a Lookup field:

  1. Visit GearIcon.png > Customization > Objects > {object} > Fields
  2. Click [Add Field]
  3. Fill in the Field Properties, shown below.

Field Properties

This section describes the properties you can set when creating a Lookup field. (Some the sections do not appear until you have selected and object.)

Summary
  1. Basic Information
  2. Display Attributes
  3. Default Value
  4. Lookup Field Configuration
  5. Lookup Display Type
  6. Record Selection Information - Specify selection criteria for the records to display in the Lookup Window
  7. Post Selection JavaScript - Perform optional data validation, messaging or actions after the lookup record is selected
  8. Role-Based Field Visibility - Show or Hide this field in the Form, based on the selected Role
  9. 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

Display Attributes

Section
Select the section in the default Form where the Lookup field should appear.
Required
Check this box if a value is always required to create a record.

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

Notepad.png

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 Settings

Record Selection

  • Popup: A separate window appears showing the lookup data. (default)
  • Drop Down: A drop down list appears at the current field in the user's window.
Considerations
  • The Drop Down option is available for User objects and Custom objects. It may not be available for other standard objects.
  • A Drop Down list is limited to 200 entries. If the lookup data is longer than that, a runtime error occurs.
  • A Drop Down list should only be used for a list that is short enough to be scanned at a glance. For longer ::lists a Popup is preferable, because the user can type one or more initial characters to navigate the list.

Additional Fields to Display

The Additional Fields are displayed in a Lookup Window when the Lookup Lookupicon.gif 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 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
  1. Choose a field to copy from the target record, and select the field to copy to in the current record.
  2. Click [+ Add More] to specify additional fields to copy.
Considerations
  • Not all data types are supported. In general, you can copy the simple data types like text, number, and field. But you can't copy more complex data like formulas and attachments.
  • For more extensive operations, use #Post Selection JavaScript

When you choose this option, the dropdown list shows you the fields that can be copied.

  • 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:
  1. A new record is selected on the form for the Lookup field
  2. 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 User object)
  • 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 Filtering

When the user clicks the icon next to a Lookup field, the platform's Lookup Window is displayed, allow the user to choose a record. This section determines how that window appears.

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.
Filtering Criteria
If the target of the lookup is a System Object, this section does not appear. For other lookups, this section can be used to add filters using Fixed String Values or Dynamic Filters as criteria to determine the records to display in a Lookup Window.

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)
Standard Filter Expression syntax can be used:
  • Owner equals BLANK - Record that has no owner
  • Status contains 1|2|3|4 - Picklist field contains one of the specified values
A derived field name can also be specified 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 format, $fieldname_name
  • For all other fields, use $fieldname
Learn more: Field Names
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


Post Selection JavaScript

Use JavaScript to perform additional operations after the user chooses a Lookup target record in an object Form.

Examples
  • Add a validation to confirm that the record targeted by the Lookup is consistent with data in the current record.
  • Copy data from the target record to the current record.
Learn more: Post Selection JavaScript

Role-Based Permission Control

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 text that will appear as a Field Hint when the field is displayed.

Using the Lookup Icon

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

Optionally, start typing to use the Auto Completion 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 Lookupicon.gifIcon.

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 GearIcon.png > Customization > 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.

Notepad.png

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:

  1. Visit GearIcon.png > Customization > Objects > Orders
  2. Click the Fields tab
  3. 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:

  1. Go to the section Lookup Field Configuration and under that, to Additional Fields to Display
  2. 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.)
  3. 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:

  1. Visit GearIcon.png > Customization > Objects > Orders
  2. Click the Forms tab
  3. 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".)
  4. Drag that field into the form, at the location where you want it to be displayed.
  5. Click [Save Changes]
  6. Click the pencil icon PencilIcon.png next to the field, to edit it.
  7. Change the label that appears in the form:
    Display Label - Customer Address.
  8. 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.)

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

Self Reference Lookup

About Self Reference Lookups

An Object can contain a Lookup field that references itself (a self reference). This configuration creates a parent-child relationships between records.

Examples include:

  • An organization could have multiple subsidiaries, and some of those 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:

Add a Self Reference Lookup Field to an Object

First:

  1. Click GearIcon.png > Customization > Objects > {object}
  2. 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)
  3. Provide any other necessary information in the Basic Information and Display Attributes sections
  4. Click [Save]

Notepad.png

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:

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

Display Related Records from the Current Object

When a Self Reference Lookup field is added, a new section is added to the Related Information section of each object Form. By default, the section is hidden.

To make the related-record Hierarchy View visible:

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

Create a Self Reference Lookup between Records

  1. Navigate to the {object} tab (Company, for example)
  2. Edit a record, select a Parent Company from the lookup field and Save the record
  3. Select the record designated as Parent Company
  4. Save your selection.