How to Filter Lookups Using the REST API

Products

• Clarity PPM On Premise

Issue/Introduction

Is it possible to filter lookups using the Clarity REST API?

Environment

Release : 15.9.1

Resolution

As per the 'Describe' page [Administration > General Settings > System Options > API > API Documentation URL] , lookups can be filtered using three different criteria:

The GET method for list of lookup values supports limited filter. Use is limited to the following: id = 'value', code = 'value', searchText startsWith 'value': The 'value' is queried against all fields specified in Auto Suggest attribute search keys for the lookup. A query/filter expression consists of name of attribute(LHS), an = Operator and value(RHS). Example:'lookupValues?filter=(code = 'USD')' 

With a query like this:

SELECT
@SELECT:r.id:id@,
,@SELECT:r.full_name:[email protected]
,@SELECT:r.unique_name:[email protected]
,@SELECT:r.unique_name:unique_code@
,@SELECT:r.last_name:[email protected]
,@SELECT:r.first_name:[email protected]
FROM srm_resources r
WHERE 1=1
AND @[email protected]

It is possible to create a GET request with the following example 'filter' attribute values:

(id = 1)
(code = 'Administrator, CA PPM')
(searchText = 'Adminis*PPM')

The three will return the same resource, 'CA PPM Administrator'.

Note that 'id' should map to the 'ID' in the lookup, and 'code' to the 'UNIQUE_CODE' in the lookup, so in my example they are SRM_RESOURCES.ID and SRM_RESOURCES.UNIQUE_NAME, respectively.

It is necessary to include the parentheses, and spaces between the equal sign.

In order to filter by a particular attribute in the lookup, the attribute can be added to the Auto Suggest.