Aurora_Gaimon

CA PPM: An easy way to create parameterized lookups

Blog Post created by Aurora_Gaimon Employee on Jun 26, 2017

It's been few weeks since last post. Today, I feel like talking about parameterized lookups. They are known as well as "dependent" or "hierarchy" lookups/attributes. How you want to name it, it does not really matter. The important thing is to understand the power and capabilities that CA PPM studio offers with lookups. As you may know, we can create statics and dynamic lookups. And if we combine them ...? Let's take a look:

 

I will explain using a simple example. There will  be 2 level, one for Countries and one for the Cities. Depending on the country selected, the correct right cities which belong to that country should be displayed.

 

Let's go step-by-step.

 

  • Create a static lookup for the 1st level:

 

  • Add some values. For demo purposing I will use a copule of countries, my 2 favorite ones :
    • Spain
    • The Netherlands

  • Create another static lookup for the 2nd level:

 

  • Add some values. For demo purposing, I will use a couple of cities: 
    • Amsterdam
    • Rotterdam
    • Barcelona
    • Madrid

 

  • Create a custom object which will be used for mapping:

 

  • Create two custom attributes in that custom object which will be built using the static lookups:

 

 

  • Configure the list and create view of your custom object in order to add your mappings:

 

  • Create a dynamic lookup using a NSQL query:

 

Use following code:

SELECT DISTINCT
        @SELECT:SQLWRAP.LOOKUP_CODE:LOOKUP_CODE@,
        @SELECT:SQLWRAP.NAME:LOOKUP_NAME@,
        @SELECT:SQLWRAP.SORT_ORDER:LOOKUP_SORT@
FROM
(
     SELECT LOOKUP.LOOKUP_CODE
    , LOOKUP.NAME
    , LOOKUP.SORT_ORDER
     FROM odf_ca_z_mapping_cc MAP, CMN_LOOKUPS_V LOOKUP
     WHERE   MAP.Z_CITY = LOOKUP.LOOKUP_CODE AND LOOKUP.LOOKUP_TYPE = 'Z_LKP_LEVEL_2' AND LOOKUP.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
     AND (@WHERE:PARAM:USER_DEF:STRING:TYPE_CONSTRAIN@ IS NULL OR
     @WHERE:PARAM:USER_DEF:STRING:TYPE_CONSTRAIN@ = MAP.Z_COUNTRY)
)SQLWRAP
WHERE @FILTER@

 

 

** Note: Ensure the display attribute is the "Name":

 

  • It’s ready to be used in any object. For demo purposing, I will use project object.

 

  • Create two new attributes. The first level will be based on lookup level 1 static lookup:

 

  • On the other hand, the second level will be based on dynamic lookup:

 

      ** Note: Do not forget to indicate the object attribute if for mapping, as indicated in the red square.

 

  • Add the attributes in the desired object view:

 

  • We can see that depending on the Country selected, just right cities will be shown:

   

 

 

** Note I'm attaching a quick demo. There is not need to click on "save" to get values refreshed in 2nd level lookup.

 

 

If you need help with CA PPM studio, lookups, NSQL queries... engage CA Services or ask questions in CA Communities.

 

That’s all. Thanks for reading until here. Did you like it? Please, don’t be shy and share it.

Attachments

Outcomes