Parameterized lookup value blank in list views if "IS NULL" is missing from the query

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When using parameterized lookups on objects, the values do not show in Object List Views while displaying correctly on Properties views.

Steps to reproduce (MSSQL server - specific query)

1. XOG in the attached lookup descriptions lookup_write.xml
2. Connect to Clarity, make sure three lookups are now created:
   Test
   test_flat
   test_ids
3. Go to Clarity Studio : Objects : Incident: Attributes
4. Create a new attributeTest1 based on test_IDs lookup
5. Save
6. Create a new attribute Test2 based on Test dynamic lookup, hit Save
7. In the attribute Test2 properties, in section Lookup Parameter Mappings select test1 for Object Attribute ID
8. Save
9. Add both attributes Test1 and Test2 to Incident Properties Edit Layout view in General section
10. Add both attributes Test1 and Test2 to Incident List View
11. Publish the views
12. Now go to Home - Incidents
13. Create a new incident, select ZZtest as value in Test1, and Clarity in Test2
14. Save
15. Now go back to Incident list view.

Expected results: See both of the values populated in the list
Actual results: Values are blank

Environment

Release: All

Resolution

Modify the dynamic lookup query to include OR X IS NULL statement. Note: The side effect of this is that it will make display all values in the second parameterized dynamic lookup if no value is selected in lookup 1 (because of the IS NULL condition). All the rest will be working as expected and it will display in the List views.

Example of change needed below:

         @SELECT:nls.name:name@
FROM     cmn_lookups lkp
         INNER JOIN cmn_captions_nls nls
            ON nls.table_name = 'CMN_LOOKUPS'
           AND nls.pk_id = lkp.id
WHERE    lookup_type = 'TEST_STATIC'
AND      lkp.is_active = 1
AND      nls.language_code = @WHERE:PARAM:LANGUAGE@
AND      @WHERE:PARAM:USER_DEF:INTEGER:Test_IDS_id@ = LEFT(lkp.lookup_code,3)

to

         @SELECT:nls.name:name@
FROM     cmn_lookups lkp
         INNER JOIN cmn_captions_nls nls
            ON nls.table_name = 'CMN_LOOKUPS'
           AND nls.pk_id = lkp.id
WHERE    lookup_type = 'TEST_STATIC'
AND      lkp.is_active = 1
AND      nls.language_code = @WHERE:PARAM:LANGUAGE@
AND      (@WHERE:PARAM:USER_DEF:INTEGER:Test_IDS_id@ = LEFT(lkp.lookup_code,3) OR
@WHERE:PARAM:USER_DEF:INTEGER:Test_IDS_id@ IS NULL)

Additional Information

See also: Lookups master KB for Clarity