Oracle DRM, External Lookup Properties and Filtered Lists

Have you ever created a list-type property in DRM and wished you could contextually filter the list? Do you have a DRG Workflow model that includes a property update that you wish you could restrict users from selecting an inappropriate entry? In this post, we’ll discover the use of external lookups that leverage the selected value of a another DRM property to filter the list of values.

DRM is a very flexible tool that allows the creation of different types of properties that can be associated with the nodes or members within a hierarchy. From free-form text to system calculated values to drop-down lists, DRM has most of the bases covered when it comes to property types. The one aspect that I found missing though was the ability to have a list of members that could be filtered or limited based on a set of criteria or the value of another property. After the recent addition of external lookup property types in DRM, I discovered a method that accomplishes this very goal of creating a filtered selection list.

To demonstrate, let’s take a property named “State” created as a “Defined” property type with an associated List (List checkbox checked). The members in the list include those shown below.

Now let’s also assume I have another property called “ZipCode”. A sample of list values for that member are shown below.

For this example, these two properties are associated with a hierarchy called Clients. When both properties are created as simple lists types in DRM, users that have access to make updates via DRM or DRG can select a state and zip code combination that may be invalid since there are no controls or filters in place to restrict available list members. Obviously, we don’t want users to be able to select a zip code that isn’t valid for the state that was selected. How can we limit the selection of possible zip codes based on the State that was selected?

Now that we have the option to create a property with an associated external lookup using SQL to populate values based on relational tables and views, we can leverage variables passed in to the (external lookup) SQL query to filter the resulting data set. The steps below will walk through the process of setting up such a property.

First, we need to create a database table or view the will contain a list of states along with valid zip codes for each state. Below is a screenshot of the sample table I created for this purpose.

The first column contains the state and the second column is the zip code. Notice that each state can have multiple zip codes, but a given zip code is only relevant to a single state. Now that we have the table created, we’ll need to create the external connection (in DRM) that will allow us to link the ZipCode variable to this external table.

In DRM, click the “Administration” link on the left-hand pane. From the “New” drop-down selection, select “External Connection” and create a connection to your relational source.

Enter the connection details for your relational database. Here’s an example below for an Oracle database (Data Source=SERVERNAME:PORT/SID). Note that the syntax for the “Connection String” will depend on the type of database being used.

After entering the connection details, save the connection. Then click on the “External Operations” tab to open the window to create a new External Operation. Click to add a new database operation with an “Operation Type” of “Lookup”. Name the connection and enter the SQL to pull the records from the table we just created. Create the SQL with a where clause based on the value of a “STATE” parameter and create the associated parameter.

Now when we use this external lookup connection with our ZipCode variable, we can pass the value set for the State property to filter the results returned for the ZipCode property.

Open the definition tab for the ZipCode property and change the “Propery Type” option to “External LookUp” (ELU) as shown below.

Open the ELU tab and specify the ELU connection we created previously. Also, specify the value of the State property as the parameter to pass in to the ELU SQL code we created.

Save the updated ZipCode property and let’s examine how it operates with the Client hierarchy.

Open the Clients hierarchy and click the ellipse to open the list for the ZipCode property.

There are no values available because we haven’t selected a value for the State property yet. Without a value passed in to the ELU SQL for the State parameter, the SQL will return an empty list. This is expected behavior. Now, set the value of the State property to a state. Click the ZipCode variable again to open the ELU list and notice that the list is filtered based on the State we selected.

Another example with Alabama selected as the state.

Now, only valid zip codes for the selected state can be selected. This is especially important in DRG where governance users can make updates and we want to limit their ability to enter or select “bad” data.

Nerdy Ragnar loves External Lookup properties!