Dynamic lookup type attribute doesn't show values in Modern UX when @[email protected] clause is missing

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

One of the lookup attributes is not showing lookup values in the Modern User Experience (MUX), but you are able to see the values in classic Clarity. 

Steps to Reproduce:

1. Log in to Clarity
2. Go to Administration->lookup
3. Create a lookup with a dynamic query
   
   SELECT  @SELECT:I.ID:[email protected],@SELECT:I.NAME:[email protected],@SELECT:I.CODE:[email protected] 
FROM INV_INVESTMENTS I 
where I.IS_ACTIVE = 1 
and UPPER(I.odf_object_code) = 'PROJECT' 
and (select COUNT(1) as INV_COUNT 
 from INV_HIERARCHIES H 
 where H.PARENT_ID = I.ID) > 1


4. Create an attribute and associate the lookup created above
5. Add an API Attribute ID to make the attribute available in the New User Experience Blueprints.
6. Check the attribute value in the New UX 

Expected Results: Attribute values are available and showing in New UX. 

Actual Results: Attribute value is not available however visible in classic UI. 

Note: This is not specific to MUX, rather this is caused by Auto Suggest functionality not working properly. This Auto Suggest functionality exists in Classic as well. Instead of Browsing if you start typing characters in the Classic lookup field, you can see the same behaviour as in Modern. Since MUX uses only Auto Suggest functionality, this issue appears that a lookup works in Classic but does not Modern. The problem here is with Auto Suggest vs Browse lookup behaviour that is only used in Classic.

Environment

Release : All

Component : CLARITY STUDIO

Resolution

Fixed in 16.0 as Defect DE62473

Workaround: Add @[email protected] clause in the NSQL. Example below:

SELECT  @SELECT:I.ID:[email protected],@SELECT:I.NAME:[email protected],@SELECT:I.CODE:[email protected] 
FROM INV_INVESTMENTS I 
where I.IS_ACTIVE = 1 
and UPPER(I.odf_object_code) = 'PROJECT' 
and (select COUNT(1) as INV_COUNT 
from INV_HIERARCHIES H 
where H.PARENT_ID = I.ID) > 1
and @[email protected]