External Lookup - Database

One of the new addition to DRM is external lookup.

What it does? Well it allows users to query external system for a property value. Currently it supports Database SQL/Procedure and WebServices.

In this blog i will be showing how to create external lookup and use.

Example: We will try to pass a zipcode and receive city name using external connections.

Note: I have already created a table called City_ZIP that constain city/zip/state and related information. 

Step 1

External Connection : We need to create external connection so that DRM know where to query for the data for external lookup.

• In Administer page of DRM, Click New and select External Connections

• Enter the database information. make sure the connection is successful by clicking on the plug icon (next to save icon)

• Click Save and click on the External Operations tab (located at bottom section)

• Click Add button. Click Statement Tab
• Enter Name: Get_City
• Operation Type: Lookup
• Database Operation type: Statement
• Parameters: MyZipCode
• Test Value: 00704 (This is optional. this is just to test. you can use any value that exists in the table). 
• SQL Statement: Select * from City_Zip where zipcode=<%MyZipCode%>;

• Click on the plug icon on top right. if everything is correct you should see results.

• Note: In the statement section remove the test value before saving. If you forget the lookup will always show data for the test value.
• Click OK. Click Save.

Step 2

Creating properties. We will create two properties. One for storing ZipCode and second will be look up to get CityName

• Create a Property as Zip_Code
• Name: Zip_Code
• Label: Zip_Code
• Property Level: Global
• Property Type: Defined
• Assign to category you want.If you dont assign you'll not be able to see the property.
• Save

• Create property : City_Name
• Property Name: City_Name
• Label:City_Name
• Property Level: Global
• Property Type: External Lookup


Click on External Lookup Tab

External Connection: DBConnection (one that you created in Step 1)
Operation: Get_City
Parameter Mapping
Name: MyZipCode
Param Source Type: Property
Param Source: Custom.Zip_Code (this is the property we just created in Step 2)
Column/property Mappings
Click on the Name List Box and select which column you want to use. In our example we wanted to get City.
Click Save.

Step 3 : Testing

• Open Hierarchy
• Select a node.
• In the Zip_Code Property enter a valid zip code: example: 00704
• Click eclipse button on the City_Name Property.
• you should see a popup list with all the cities within the zip code.
• Select one of them and it should get saved in the City_Name Property.