Clarity

In this blog I will show how to link multiple lookups and even the OBS to limit another lookup, all without saving.

You can use this for a number of situations and really improves the user experience over static dependent lookups.

Purpose(s):

• Limit values shown to user
• Limit values which trigger processes

Time to read:5 minutes

Level: Advanced

Pre-requisites:

• Ability to write NSQL
• Understand studio objects
• Read Aurora_Gaimon 's blog:CA PPM: An easy way to create parameterized lookups 

I found this technique a few years back looking at the "Looking Mapping" object, Aurora has done the community a great favour by documenting it so I'll just expand on some more tips.

Today I will expand Aurora's work by showing some advanced techniques.

Step 1: Plan what you need your lookup(s) to do

In this example we are going to limit a sub lookup to a parent AND OBS. We can of course make this much much more complicated so don't let this limit your creativity.

You can also use it to display HTML or execute javascript based on prior conditions. 

Step 2a: create a OBS selector

EXAMPLE: this allows ether "all department" or the value.

SELECT
 @SELECT:x.id:id@,
 @SELECT:x.parent_id:parent_id@,
 @SELECT:x.name:name@, 
 @SELECT:x.path:path@,
 @SELECT:x.hierarchy_level:hierarchy_level@
FROM
(SELECT 
obs.id,
obs.parent_id,
obs.name,
nobs.path,
nobs.hierarchy_level
FROM 
prj_obs_units obs 
INNER JOIN nbi_dim_obs nobs ON nobs.obs_unit_id = obs.id
WHERE 
obs.type_id = 5000001
UNION ALL
SELECT
1 AS id,
NULL AS parent_id, 
'All Department' AS name,
'ALL' AS path,
1 AS hierarchy_level
FROM DUAL) x
WHERE @FILTER@

Step 2b: Create your lookup mapping object

I suggest this format, note in my simple example OBS and parent value are single values.

My real world use case needed multi value lookups, i.e. 1 value in the map for many parents and many departments

• Internal ID - the ID to be sent into the object, not shown in UI
• ID - set up the ID to be autoID
• Name - rename to "parent lookup ID" - this is where you will capture which parent lookup mapping we need
• Parent lookup ID - the value(s) that this child value should show for
• Child Value - the value to show for this parent. (ether a lookup or a hardcoded value)
• OBS Value - a lookup returning valid OBS value(s), we show the name for humans but Intneral ID is stored
• is active - should the value show  in the pick list

These steps are covered in depth by Aurora, so i'm just showing the highlights

Step3: Create your lookup

The trick is that we can add more params and seems to be unlimited, only constrained by your ability to program the map and SQL in the lookup

Again if you add multi value lookups you will have much more complexity to code, this example is a single value lookup.

SELECT  DISTINCT
@SELECT:MAP.ID:ID@,
@SELECT:MAP.Z_CHILD:LOOKUP_NAME@
FROM    
ODF_CA_Z_MAP MAP
WHERE   
(@WHERE:PARAM:USER_DEF:STRING:TYPE_CONSTRAIN@ IS NULL OR MAP.Z_PARENT = @WHERE:PARAM:USER_DEF:STRING:TYPE_CONSTRAIN@)
--ADD AS MANY EXTRA PARAMS THAT YOU NEED, MULTI VALUE LOOKUPS NEED EXTRA LOGIC
AND  (map.z_obs = 1 OR map.obs = @WHERE:PARAM:USER_DEF:INTEGER:TYPE_CONSTRAIN2@)
AND MAP.NAME = 'Z_MY_PARENT'
AND     @FILTER@
@BROWSE-ONLY: 
AND MAP.Z_IS_ACTIVE=1 
:BROWSE-ONLY@

Step 4: Create your attribute in the object

For each param you need to map the values in. OBS appears as a a odf_****** value

Now you've done this you will have the lookup values constrained by OBS and the parent value WITHOUT saving.

Hope you find this useful.

As always this code is untested and you use it at your own risk