Auto-suggest does not work when "Order By" or "Group By" clauses are used in Dynamic Query Lookups


Article ID: 5243


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


When a dynamic query contains an "Order By" or "Group By" clause, the auto-suggest functionality does not work.


Steps to Reproduce:

1. Create a dynamic lookup, with a code such as the below: 


@select:id:[email protected]

@select:name:[email protected] 

from inv_investments 

where @[email protected] 

order by name 

2. Couple this lookup to an attribute on an object. Display this attribute in the Layout:Edit view 

3. Navigate to the appropriate Layout:Edit view, and browse for a value on this lookup field. Copy this value 

4. Refresh the page and paste the value copied in step 3 in the lookup field.


Expected Result: The lookup field matches and provides suggestions

Actual Result: The lookup field does not provide suggestions


"Order By" clause is not to be used in Dynamic Lookups. The UI of Dynamic Lookups provisions for ordering a single column, and that is the design right now. As for the "Group By" clause, it is to be used with "Having" clause - with a sytax such as @[email protected] This prerequisite is documented here.


CA PPM all supported versions


For situations that require the "Order By" clause, the UI provides a way to sort a column.