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

book

Article ID: 5243

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application 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

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

Environment

CA PPM all supported versions

Resolution

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