Lookup using external connection displays ID instead of full name

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

SUMMARY:
A dynamic lookup defined against an external DB connection is not being saved correctly on the list views.
This is happening on both lists on Modern UX and Classic UX.

The lookup display correctly on other views like properties, fly out, etc.

Steps to Reproduce:

1. Create a lookup/dynamic lookup in Studio.
2. Use an external DB to generate that lookup.
3. Define the lookup (.i.e):

SELECT 
@SELECT:PLATAFORMAS.ID_PLAT:ID_PLAT@,
@SELECT:PLATAFORMAS.CODIGO:CODIGO@,
@SELECT:PLATAFORMAS.NOMBRE:NOMBRE@,
@SELECT:PLATAFORMAS.TEST:TEST@,
@SELECT:PLATAFORMAS.NOMBRE_COMPLETO:NOMBRE_COMPLETO@
FROM (
 SELECT
 PLAT.ID ID_PLAT,
 INV.CODE CODIGO,
 INV.NAME NOMBRE,
 PLAT.Z_CODIGO_TEST TEST, 
 INV.CODE || '-' ||INV.NAME || '-' || PLAT.Z_CODIGO_TEST NOMBRE_COMPLETO
 FROM ODF_CA_Z_PLATAFORMA PLAT
 JOIN INV_INVESTMENTS INV ON PLAT.ID=INV.ID
 ORDER BY INV.CODE
)PLATAFORMAS
WHERE @FILTER@

4. Select the full name (NOMBRE_COMPLETO) as the display attribute on the Lookup.
5. Create an attribute on the Project Object using that lookup.
6. Add the attribute to the list of Projects.
7. Add some value to the attribute based on the lookup.

Expected Results: The value for NOMBRE_COMPLETO is displayed on the attribute in the list.
Actual Result: The value for ID_PLAT is displayed on the list.

Environment

Release : 16.0.1

Cause

This has been reviwed by SE as DE70439

Resolution

This is working as designed.

As the list view will use the single query to fetch the data, the query will be executed on Niku database (our Clarity Database).
For dynamic lookups, if the lookup's data source is Niku, then the main query will include the lookup query to get the related caption/lookup value otherwise it will select the column value only in main query i.e. in this case the Project's external attribute value. As the lookup column is storing the Lookup code we get the lookup code.