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