Auto suggest not working for dynamic query lookups

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Auto Suggest does not work for a custom attribute under a custom object, which is using a dynamic query based lookup. The issue can also be seen for custom attributes of OOTB objects such as Project, Task etc.

Steps to reproduce: 

1. Create a custom object based on the master project object.

2. Provide the dynamic query.

Example:

select name,code
from INV_INVESTMENTS 
where odf_object_code = 'application'

3. Click Save and Continue.

Expected Result: Autosuggest should work.

Actual Result: Autosuggest does not work.

Cause

Incorrect query construct.

Resolution

The query will need to be rewritten in the following NSQL format.

SELECT
@SELECT:inv.name:alias_name@,
@SELECT:inv.code:alias_code@
from INV_INVESTMENTS inv
WHERE @FILTER@

Additional Information

It is best to test using a copy of the lookup attribute/query.