View source for Lookup

[[File:lookupexample.png|right|thumb]]
__TOC__
==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-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|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===
{{:About Objects in Lookup Fields}}

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

;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 {{Type|}}s, the following fields are displayed as column headings:
::*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:
::*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|Field Properties]], shown below.

==Field Properties==
This section describes the properties you can set when creating a {{PAGENAME}} field.

;Summary:
# [[#Basic Information|Basic Information]]
# [[#Default Value|Default Value]]
# [[#Lookup Field Configuration|Lookup Field Configuration]]
# [[#Lookup Display Type|Lookup Display Type]]
# [[#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

=== Basic Information ===
:;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 fields that specify 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]]
:;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]]
{{:Common:UseLOOKUPFunctionToGetRecordID}}

=== 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 [[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.

:;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|Lookup-Derived Fields]].)
::* Auto-Copy is not supported in the [[System Objects]] ([[Task]], [[User]], [[Appointment]], [[Document]])
::* 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]]

===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.<br/>''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 {{Type|}}
::* 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 View]]s 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 <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.
::* 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:

:::{| cellpadding="5" cellspacing="0"
!Location Hierarchy !! Category Hierarchy
|-
| valign="top"|<syntaxhighlight lang="java" enclose="div"> [+] New York 
    [+] Theatre District 
       [-] Sardis

 [+] Chicago
    [+] Downtown
       [-] The Bakery</syntaxhighlight> || valign="top"|<syntaxhighlight lang="java" enclose="div"> [+] Service
    [+] Slow Food
       [-] Sardis
       [-] The Bakery

    [+] Fast Food
       [-] ...</syntaxhighlight>
|} 

;Sort Options: These options appear for {{type|}}s 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|Fixed String Values]] or [[#Dynamic Filters|Dynamic Filters]] as criteria to determine the records to display in a [[#Lookup Window|Lookup Window]].

<blockquote>
;Considerations:

*The available Record Selection Types can vary, depending on the objects used to build the Lookup Field:
::{| 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:
::*[[#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:
::{| 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 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}}
</blockquote>

===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==
{{:Lookup Icon}}

===Lookup Window===
{{:Lookup Window}}

==Lookup-Derived Fields==
{{:Lookup-Derived Fields}}

==Layout Specific Filter==

[[File:layoutspecificlookupfilter.gif|right|thumb]]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 [[Form]]s and associated Record Selection Criteria.

;Note: If additional [[Form]]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 [[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 [[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]].

To add a Layout Specific Filter:
#Create an Object (or edit an existing object)
#[[#Create a Lookup Field|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
:[[File:layoutspecificfilterinalookupfield.gif|none|thumb]]

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

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