Custom parameterized lookup attribute doesn't sort in list view

Products

• Clarity PPM On Premise

Issue/Introduction

On a custom sub object, an attribute with type Lookup - Number does not sort in the list view. The attribute is built off of a dynamic lookup in which it queries for the parent ID (odf_parent_id) and is mapped in the attribute properties parameter mappings to this ID. It seems that when it is not mapped to such an ID the sorting works fine. This is a sub object of the project object.

STEPS TO REPRODUCE: 

1. Create lookup to pull project code, something like this
select code from inv_investments

2. Create attribute on the project object pointing to the lookup created in step 1

3. Create subobject of the project object called random_sub_object, with id (random_sub_object)

4. Create lookup with below NSQL

SELECT DISTINCT 
   @SELECT:INV.ID:ID@,
   @SELECT:INV.CODE:CODE@,
   @SELECT:INV.NAME:NAME@,
   @SELECT:INV.DESCRIPTION:DESCRIPTION@,
   @SELECT:INV.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,
   @SELECT:'en':LANGUAGE_CODE@,
   @SELECT:1:LANGUAGE_ID@

FROM inv_investments inv
JOIN odf_ca_project odf ON inv.id = odf.id
JOIN inv_investments parentinv ON parentinv.code = odf.parent_id
JOIN odf_ca_random_sub_object rel ON parentinv.id = rel.odf_parent_id
WHERE (rel.odf_parent_id = @WHERE:PARAM:USER_DEF:INTEGER:PARENT_ID@ OR @WHERE:PARAM:USER_DEF:INTEGER:PARENT_ID@ IS NOT NULL)
AND @FILTER@

5. Create attribute on the random_sub_object subobject pointing to the lookup created in the step 4 above and map the parameter to odf_parent_id

6. Add that newly created attribute to the List view

7. Attempt to create few instances of the subobject

8. Try to sort on the parameterized lookup created in Step 5

9. Observe it is not sorting

Expected Results: It should allow to sort on parameterized lookup.

Actual Results: It does not sort on parameterized lookup.

Environment

Release : 15.9.3, 16.0

Component : CLARITY STUDIO

Resolution

DE63768, Engineering team reviewed this beaviour and suggested lookup query should be tweaked so that sorting works fine. Ideally param passed to query in where clause must be added within @BROWSE-ONLY: and :BROWSE-ONLY@.

They have changed the query as given below and sorting works fine in classic clarity and MUX. Please confirm whether this solution makes sense for your case.

SELECT DISTINCT
   @SELECT:INV.ID:ID@,
   @SELECT:INV.CODE:CODE@,
   @SELECT:INV.NAME:NAME@,
   @SELECT:INV.DESCRIPTION:DESCRIPTION@,
   @SELECT:INV.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,
   @SELECT:'en':LANGUAGE_CODE@,
   @SELECT:1:LANGUAGE_ID@
FROM inv_investments inv
JOIN odf_ca_project odf ON inv.id = odf.id
JOIN inv_investments parentinv ON parentinv.code = odf.parent_id
JOIN odf_ca_random_sub_object rel ON parentinv.id = rel.odf_parent_id
WHERE 1=1 AND @FILTER@
@BROWSE-ONLY:
  AND (rel.odf_parent_id = @WHERE:PARAM:USER_DEF:INTEGER:PARENT_ID@ OR @WHERE:PARAM:USER_DEF:INTEGER:PARENT_ID@ IS NOT NULL)
:BROWSE-ONLY@