Manage Lookups

A lookup associates values used by one application for a specific field to the values used by other applications for the same field. This provides the capability to map values across vocabularies or systems. For example, you can map country codes, city codes, currency codes, and so on.

Topics:

Note:

Specifying a lookup value that includes a special character of # is not supported.

• Create a Lookup

• Add Adapters or Domain Names to a Lookup

• Clone a Lookup

• Delete a Lookup

• Update Lookups in Active Integrations

• Export a Lookup

• Import a Lookup

• Create the lookupValue Function

Create a Lookup

Create a lookup to map values between applications.

1. In the navigation pane, click Home > Integrations > Lookups.
   
   
   Description of the illustration lookups.png
   
   
2. Click Create.
   The Create New Lookup dialog is displayed.
3. Enter a name and optional description for the lookup.
4. Click Create.

The Lookup page is displayed.

Add Adapters or Domain Names to a Lookup

Add adapters or domain names to a lookup to map values between connections.

1. Click Domain Name 1.
   A menu with options is displayed.
2. Select the type to create:

   • Replace with Adapter: This selection invokes the Select Adapter dialog for choosing the adapter to use. You can change your selection:

     • If you want to change your adapter selection, click the selected adapter type, then choose Select Different Adapter.

     • If you want to change from an adapter to a domain name, click the selected adapter type and choose Replace with Domain Name.

   • Enter Domain Name: This selection invokes a dialog for entering the domain name to use. There is a 50 character limit. You can change your selection:

     • If you want to change from a domain name to an adapter, click the domain name, then choose Replace with Adapter.

     • If you want to change the domain name, click the name, then choose Edit Domain Name.

     The adapter or domain name is displayed.

3. Enter a value in the field below the adapter or domain name. To add more rows for additional values, click the + sign.
4. Click Domain Name 2.
5. Repeat Step 2 to add an adapter or domain name.
6. Enter a value in the field below the adapter or domain name. To add more rows for additional values, click the + sign.
   
   
   Description of the illustration create_new_lookup.png
   
7. If you want to add another column, click +.
8. Repeat to add more adapters and domain names to the lookup.
9. Click Save when complete.

Clone a Lookup

You can clone a copy of an existing lookup. It is a quick way to create a new lookup with similar information. Lookups with adapters and domain names can be cloned.

1. In the navigation pane, click Home > Integrations > Lookups.
2. Go to the row of the lookup to clone.
3. On the Lookups page, select Clone from the menu.
   The Clone Lookup dialog is displayed.
4. Enter the lookup information.
5. Click Clone.
6. Click Edit to further configure your cloned lookup.

Delete a Lookup

You can delete a connection from the Lookup menu.

1. In the navigation pane, click Home > Integrations > Lookups.
2. Go to the row of the lookup to delete.
3. Select Delete from the menu.
   The Delete Lookup dialog is displayed.
4. Click Yes to confirm deletion.

Update Lookups in Active Integrations

Lookups are not deployed as part of integration activation. Therefore, changes that you make to lookups already used in active integrations typically take effect immediately. There is no need to re-activate integrations using a changed lookup for the new lookup value to take effect.

There can be instances in which updates to the runtime environment are slow because it is not in real-time and queuing can occur at runtime. For those cases, updates can occur in a couple of minutes.

Export a Lookup

Once you create a lookup, you can export that lookup for use in other Oracle Integration environments. You can export a lookup from either the Lookup Designer or from the Lookups list.

To export a lookup:

1. In the navigation pane, click Home > Integrations > Lookups.
2. Go to the row of the lookup to export.
3. Select Export to CVS from the menu.
4. In the dialog that appears, select Save File, and then click OK.
5. Save the file to the location you want.

The file is saved as a CSV file with the same name as the lookup.

Import a Lookup

You can import lookups that were previously exported from Oracle Integration.

The file to import must have the following for the first row, where table_name is the name of the table as you want it to appear in Oracle Integration. This name cannot contain spaces.

DVM,table_name

The second row contains the names of the adapters that are being mapped. Use the following case-sensitive IDs for each adapter:

Adapter Name	Identifier to Use in the Import File
Oracle Eloqua Cloud	eloqua
Oracle Engagement Cloud	osc
Oracle Messaging Cloud Service	oms
Oracle Service Cloud	rightnow
Oracle HCM Cloud	hcm
Oracle ERP Cloud	erp
Salesforce Cloud	salesforce

To import a lookup:

1. Locate the CSV file containing the lookup table you want to import.
2. In the navigation pane, click Home > Integrations > Lookups.
3. In the banner, click Import.
4. In the Import Lookup dialog, click Browse to navigate to and select the CSV file to import.
5. Click Import.
   If a lookup already exists with the same identifier and version, you must confirm whether to overwrite the existing lookup.

The imported lookup appears in the Lookups list on the left. You can customize or activate it, if it is ready.

Create the lookupValue Function

You can create the parameter values for the lookupValue function with the Build Lookup Function wizard. This wizard enables you to define the lookup table, source column, target column, and default value to use in the function. For these parameter values to be selectable in the wizard, you must have already created a lookup on the Lookups page.

Topics:

• Access the Build Lookup Function Wizard

• Select the Lookup Table

• Select the Source and Target Columns

• Specify the Default Value

• Review Your Lookup Table Selections

Access the Build Lookup Function Wizard

The Build Lookup Function wizard for creating the lookupValue function parameter values is accessible from the mapper or the Expression Builder in Oracle Integration.

To access the Build Lookup Function wizard:

Note:

You must already have created lookups to use this wizard. See Manage Lookups.

• From the mapper:

  See Access the Build Lookup Function Wizard.

• From the Expression Builder in an action, such as the switch action:

  1. Go to the Integrations page.

  2. Open an integration.

  3. Invoke the Expression Builder from within the integration. For example:

     • In an app-driven orchestrated integration, edit a switch activity.

     • In a basic routing integration, click the Filter link.

     The Expression Builder is displayed.

  4. Expand Components.

  5. Expand Functions > Oracle Integration.

  6. Expand the lookupValue function to view the available parameters to define in the Build Lookup Function wizard and Expression Builder.

     • dvmLocation

     • srcColumn

     • srcValue

     • targetColumn

     • defaultValue

  7. Drag the lookupValue function into the New Condition field.

     The Build Lookup Function wizard is displayed. To create the lookupValue function parameter values, see section Select the Lookup Table.

Select the Lookup Table

Select the lookup table to use in the lookupValue function.

Note:

You must already have created a lookup. Otherwise, no lookups are displayed for selection.

Element	Description
Lookup Table	Select the lookup table to use in the function. You can view the lookup description by clicking the information icon in the table. This can guide you in selecting the required lookup table. The number of columns defined in the lookup is also displayed.

Select the Source and Target Columns

Select the source and target columns to use in the lookupValue function.

The lookupValue function requires one source column and one target column. When you select a source and target column, the values available with the columns are displayed.

Element	Description
Select Source Column	Click the source column header to select from a list of available columns for this lookup table. The data included with the selected column is displayed. Both adapter and domain name columns are displayed.
Select Target Column	Click the target column header to select from a list of available columns for this lookup table. The data included with the selected column is displayed. Both adapter and domain name columns are displayed.

Specify the Default Value

Select the default value to use in the lookupValue function.

Enter the default value to use if no match is found. If there is no match that satisfies all the search values, the lookup fails and the default value is returned.

Element	Description
Default Value	Enter a default value to use if no match is found (for example, an actual default value to use or an error message such as No Value Found).

Review Your Lookup Table Selections

You can review the lookup table values to use in the lookupValue function on the Summary page.

You can review the lookup table values from the Summary page. The Summary page is the final wizard page after you have completed your configuration.

Element	Description
Parameter and Value Table	Displays a summary of the parameters and values you defined on previous pages of the wizard. To return to a previous page to update any values, click the appropriate tab in the left panel or click Back.
Resulting Expression	Displays the expression you defined on the previous pages of the wizard. The lookupValue function takes the following format: lookupValue(dvmLocation, srcColumn, srcValue, targetColumn, defaultValue) Where: dvmLocation: The lookup table selected on the Select Lookup Table page. srcColumn: The source column selected on the Select Columns page. srcValue: The source value you enter in the New Condition field of the Expression Builder after completing this wizard. Click Done to complete this wizard, then define the srcValue parameter value. targetColumn: The target column selected on the Select Columns page. defaultValue: The default value entered on the Default Value page. For example, a defined lookupValue function after you have completed the wizard and defined the srcValue parameter value in the Expression Builder can look as follows: dvm:lookupValue('tenant/resources/dvms/Country','rightnow','US','mysoap','No data found')

When you click Done, the function icon is created in the mapper and the function XPath expression is displayed in the Expression Builder.