Overview of Lookups

Lookups are lists of values in applications. You define a list of values as a lookup type comprising a set of lookup codes, each code's translated meaning, and optionally a tag.

On the UI, users see the list of translated meanings as the values available for selection. Lookups provide a means of validation and lists of values where valid values appear on a list with no duplicate values. For example, an application might store the values Y and N in a column in a table, but when displaying those values in the user interface, Yes or No (or their translated equivalents) should be available for users to select. For example, the two lookup codes Y and N are defined in the REQUIRED_INDICATOR lookup type.

Note: Don't include spaces in lookup codes. Use the underscore character (_) to separate the words if needed. For example, instead of creating the lookup code as DEV PROGRAM, create it as DEV_PROGRAM.

The following table contains an example of a lookup type for marital status (MAR_STATUS) that has lookup codes for users to specify married, single, or available legal partnerships.

Lookup Code

Meaning

Tag

M

Married

Not applicable

S

Single

Not applicable

R

Registered Partner

+NL

DP

Domestic Partner

-FR, AU

In this case, tags are used for localizing the codes. All legislations list Married and Single. Only the Dutch legislation lists Registered Partner. And all legislations except France and Australia also list Domestic Partner.

When managing lookups, you need to understand the following.

  • Using lookups in applications

  • Configuration levels

  • Accessing lookups

  • Enabling lookups

  • The three kinds of lookups: standard, common, and set-enabled

Using Lookups in Applications

Use lookups to provide validation or a list of values for a user input field in a user interface.

An example of a lookup used for validation is a flexfield segment using a table-validated value set with values from a lookup type. An example of a lookup in a list of values is a profile option's available values from which users select one to set the profile option. Invoice Approval Status gives the option of including payables invoices of different approval statuses in a report. The lookup code values include All, so that users can report by all statuses: Approved, Resubmitted for approval, Pending or rejected, and Rejected.

Configuration Level

The configuration level of a lookup type determines whether the lookups in that lookup type can be edited. This applies data security to lookups.

Some lookup types are locked so no new codes and other changes can be added during implementation or later, as needed. Depending on the configuration level of a lookup type, you may be able to change the codes or their meanings. Some lookups are designated as extensible, so new lookup codes can be created during implementation, but the predefined lookup codes can't be modified. Some predefined lookup codes can be changed during implementation or later, as needed.

The configuration levels are user, extensible, and system. The following table shows the lookup management tasks permitted at each configuration level.

Permitted Task

User

Extensible

System

Deleting a lookup type

Yes

No

No

Inserting new codes

Yes

Yes

No

Updating start date, end date, and enabling the lookup code

Yes

Yes, only if the code isn't predefined data

No

Deleting codes

Yes

Yes, only if the code isn't predefined data

No

Updating tags

Yes

No

No

Updating module

Yes

No

No

Predefined data means LAST_UPDATED_BY = SEED_DATA_FROM_APPLICATION.

If a product depends on a lookup, the configuration level must be system or extensible to prevent deletion.

Once the configuration level is set for a lookup type, it can't be modified. The configuration level for newly created lookup types is by default set at the User level.

Access to the REST Resources

Users can retrieve information about lookups using the following REST resources:

  • standardLookupsLOV

  • commonLookupsLOV

  • setEnabledLookupsLOV

  • genericLookupsLOV

However, you can control whether a lookup is a part of the LOV or not. On the UI, for each lookup you can specify the REST Access Secured value that in turn determines whether it's included in the response or not. These values are:

  • Anonymous: Lookup is available to a user having anonymous role or authenticated role.

  • Authenticated: Lookup is available to a user having only the authenticated role.

  • Secure: Lookups aren't available to users as part of generic REST Resources (Standard, Common, or Set-Enabled). To make it available, your security administrator must assign a specific function security policy for each lookup type to a role and assign that role to the selected users.
    Note: The function security policy is provided only for predefined lookup types.

For all lookups, the default value is set to Secure. So, if you want to make the lookup available to users through any of those resources, you must change the value to Authenticated or Anonymous, depending on who needs to access that information.

Standard, Common, and Set-Enabled Lookups

The following table shows the available types of lookups.

Lookup Type

Description

Standard

Lists the available codes and translated meanings.

Set-enabled

Associates a reference data set with the lookup codes.

Common

Legacy lookups or lookups that have attributes.

Standard lookups are the simplest form of lookup types consisting only of codes and their translated meaning. They differ from common lookups only in being defined in the standard lookup view. Common lookups exist for reasons of backward compatibility and differ from standard lookups only in being defined in the common lookup view. These can also be lookups having attribute columns. Set-enabled lookup types store lookup codes that are enabled for reference data sharing. At runtime, a set-enabled lookup code is visible because the value of the determinant identifies a reference data set in which the lookup code is present.

Accessing Lookups

Standard, set-enabled, and common lookups are defined in the Standard, Set-enabled, and Common views, respectively. Applications development might define lookups in an application view to restrict the UI pages where they might appear.

In lookups management tasks, lookups might be associated with a module in the application taxonomy to provide criteria for narrowing a search or limiting the number of lookups accessed by a product specific task such as Manage Purchasing Lookups.

Enabling Lookups

A lookup type is reusable for attributes stored in multiple tables.

Enable lookups based on the following.

  • Selecting an Enabled check box

  • Specifying an enabled start date, end date, or both

  • Specifying a reference data set determinant

If you make changes to a lookup, users must sign out and back in before the changes take effect. When defining a list of values for display rather than validation, limit the number of enabled lookup codes to a usable length.

To view the predefined lookups and their lookup codes, use the following tasks in the Setup and Maintenance work area:

  • Manage Standard Lookups

  • Manage Common Lookups

  • Manage Set-Enabled Lookups

Translating Lookups

You can translate the lookups that you defined to the preferred language(s) without changing the language session of the application. Use the translation option available on the lookup code table. By default, for each lookup, all the permitted language rows in the translator dialog box appear in the source language (the current session language). When you edit a particular language entry, you can modify the translated meaning and description to the language in which you want the lookup to appear. Once the updates are made, the end-users can view the lookup in the translated text.

Note:

You can add the translation for only as many languages as are permitted by the administrator. The functionality to limit the number of languages displayed on the dialog box is controlled through the Translation Editor Languages profile option. It can be set at the SITE or USER level. If nothing is specified, all active languages are displayed.

Deleting Lookup Types and Lookup Codes

You can delete lookup types and lookup codes from the lookups management tasks in the Setup and Maintenance work area. For example, you can delete lookups that are part of the Purchasing module, from the Manage Purchasing Lookups task in the Setup and Maintenance work area. Select the required lookup type to see the lookup codes associated with it. To delete a lookup code, select the required code and click the Delete icon. To delete a lookup type, similarly, select the required lookup type and click the Delete icon.

You can also use REST resources to delete lookup types and lookup codes.
Here are a few things to know about deleting lookup types and codes:
  • Certain lookup types and codes can’t be deleted, for example, predefined lookup types and codes can’t be deleted.
  • You can’t delete lookup codes in bulk. Instead, you can delete an entire lookup type.