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

Document ID : KB000005243
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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 

@select:id:id@, 

@select:name:Hesru@ 

from inv_investments 

where @filter@ 

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

Environment:
CA PPM all supported versions
Cause:

"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 @HAVING_FILTER@. This prerequisite is documented here.

Resolution:

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