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

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

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:[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

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 syntax such as @[email protected] This prerequisite is documented here.

Resolution

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