Searching

Please visit the routes page to find search routes. Search routes use the POST verb (GET|PUT|DELETE are not supported at this time), and end with ‘/search’. Note that not all fields on a resource are searchable. Fields that are known to not be searchable are listed under each search route, but this may not be a complete list. Search is not supported on multi-select EA fields at this time.

Search operators are contained in the body of a JSON request using prefix notation.

Comparative search operators operate on a single field, and include:

• equals
• greaterthan
• lessthan
• greaterthanorequalto
• lessthanorequalto
• contains
• isNull (starting release 94)
• isNotNull (starting release 94)

Note that the CONTAINS operator works on a literal string (does not support wildcard characters or regex expressions) and is case-insensitive.

Boolean search operators can be nested to specify precedence. Supported operators are:

• and
• or
• not

The ability to search against collection and integer array fields is also available. These types of fields can contain multiple values, and are denoted by square brackets. For example, the api/groups routes returns collections for (domain) rights[], users[], and roles[]. Please refer to the code samples below for examples of how to search against collections and integer arrays.

To use api/users/search to find records matching a loginID, your POST body might look like:

{
   "equals" :
   {
      "fieldName" : "loginId",
      "fieldValue" : "jwallace"
   }
}

To use api/attachments/search to find Attachments modified after 2016 Oct 9, the POST body might look like:

{
   "greaterthan":{
      "fieldName":"modifiedDate",
      "fieldValue":"2016-10-09T20:16:24.723Z"
   }
}

To use api/activities/search to find Activities where the name CONTAINS the string ‘RD’ (case-insensitive) anywhere in the name field, the POST body might look like:

{
    "contains" :
    {
        "fieldName" : "name",
        "fieldValue" : "RD"
    }
}

To use api/activities/search to find Activities where EA #1 is not empty/null (starting with release 94), the POST body might look like:

{
    "isNotNull" :
    {
        "fieldName" : "extrAttr1"
    }
}

To use api/attachments/search to find Attachments modified between 2016 Sept 9 AND 2016 Nov 9, the POST body might look like:

{
   "and":[
      {
         "greaterthanorequalto":{
            "fieldName":"modifiedDate",
            "fieldValue":"2016-09-09T20:16:24.723Z"
         }
      },
      {
         "lessthanorequalto":{
            "fieldName":"modifiedDate",
            "fieldValue":"2016-11-09T20:16:24.723Z"
         }
      }
   ]
}

To use api/attachments/search to find Attachments modified between 2016 Sept 9 AND 2018 Nov 9, with attachmentIDs between 7000 and 7400, the POST body might look like:

{
    "and":
    [
        {
            "greaterthanorequalto" :
            {
                "fieldName" : "modifiedDate",
                "fieldValue" : "2016-09-09"
            }
        },
        {
            "lessthanorequalto" :
            {
                "fieldName" : "modifiedDate",
                "fieldValue" : "2018-11-09"
            }
        },
        {
            "lessthanorequalto" :
            {
                "fieldName" : "attachmentId",
                "fieldValue" : 7400
            }
        },
        {
            "greaterthanorequalto" :
            {
                "fieldName" : "attachmentId",
                "fieldValue" : 7000
            }
        }
    ]
}

To use api/activities/search to find activities where visualEndDate > 2018-01-31 AND modifiedDate < 2017-12-18 AND origin = 3, the POST body might look like:

{
    "and": [
        {
            "and": [
                {
                    "lessthan": {
                        "fieldName": "visualenddate", 
                        "fieldValue": "2018-01-31 00:00:00.000"
                    }
                }, 
                {
                    "greaterthan": {
                        "fieldName": "modifieddate", 
                        "fieldValue": "2017-12-18 00:00:00.000"
                    }
                }
            ]
        }, 
    	{
    		"equals": {
        		"fieldName": "origin", 
        		"fieldValue": 3
    		}
		}
    ]
}

To use api/attachments/search to find attachments where attachment_id > 4900 AND modified_date < ‘2017-11-11’ AND object_id >= 2000 AND object_type_id <= 1 AND modified_user =317, the POST body might look like:

{
    "and": [
        {
            "and": [
                {
                    "and": [
                        {
                            "lessthan": {
                                "fieldName": "modifiedDate", 
                                "fieldValue": "2017-11-11"
                            }
                        }, 
                        {
                            "greaterthan": {
                                "fieldName": "attachmentId", 
                                "fieldValue": "4900"
                            }
                        }
                    ]
                }, 
                {
                    "and": [
                        {
                            "greaterthanorequalto": {
                                "fieldName": "objectId", 
                                "fieldValue": "2000"
                            }
                        }, 
                        {
                            "lessthanorequalto": {
                                "fieldName": "objectTypeId", 
                                "fieldValue": "1"
                            }
                        }
                    ]
                }
            ]
        }, 
        {
            "equals": {
                "fieldName": "modifiedUser", 
                "fieldValue": "317"
            }
        }
    ]
}

To use api/activities/search to find Activities where the activityID is not greater than 202 (which is equivalent to less than or equals to 202) the POST body might look like:

{
    "not":
        {
            "greaterthan" :
            {
                "fieldName" : "activityID",
                "fieldValue" : "202"
            }
        }
}

To use api/activities/search to find Activities where the name CONTAINS the string ‘RD’ OR the ownerID is equal to 455, the POST body might look like:

{
	"or": [
		{ 
			"contains" :
			{
		        "fieldName" : "name",
		        "fieldValue" : "RD"
		    }
		},
		{ 
			"equals" :
			{
		        "fieldName" : "ownerID",
		        "fieldValue" : 455
		    }
		}
	]
}

Search phrases can be nested to indicate precedence. Deeper-nested phrases typically have higher precedence than shallower-nested phrases. Otherwise, NOT has higher precedence than AND, which has higher precedence than OR. For all practical purposes, comparison operators have the same level of precedece.

Nesting allows you to support expressions like (x OR y) AND z. For example, to use api/activities/search to find Activities where the (name CONTAINS the string ‘RD’ OR the ownerID is equal to 455) AND the activityID is greater than or equal to 4901, the POST body might look like:

{
	"and": [
		{
			"or": [
				{ 
					"contains" :
					{
				        "fieldName" : "name",
				        "fieldValue" : "RD"
				    }
				},
				{ 
					"equals" :
					{
				        "fieldName" : "ownerID",
				        "fieldValue" : 455
				    }
				}
			]
		},
		{ 
			"greaterthanorequalto" :
            {
                "fieldName" : "activityID",
                "fieldValue" : "4901"
            }
		}
	]
}

Collection fields are denoted with square brackets (‘[ ]’), and are used to represent fields that can hold multiple values. For example, GET api/users returns a collection named “rights[]”, which returns all the domain rights associated with a user. The format for each element of the collection varies by collection. In this example, the “rights[]” collection includes a functionID and a domainID, and looks something like:

“rights”: [
{
“functionID”: 1,
“domainID”: 1
},
{
“functionID”: 5,
“domainID”: 1
}
]

In addition to traditional single-value fields, you can search a collection to identify matching records. To do this, you need to specify the name of the collection to be searched, the target field within that collection, and the target value(s) that should be matched. For example, to use api/users to find users who have the domain rights 18, 24, or 697, the POST body might look like:

{
   "equals" :
   {
      "collectionName" : "rights",
      "fieldName" : "functionID",
      "fieldValue" : "[18, 24, 697]"
   }
}

NOTE: collection search is limited to boolean or integer arrays within the collection.

Integer arrays are similar to collections, except instead of holding a collection of data, an integer array holds a collection of integers that represent primary keys (ids) of other resources. Like collections, integer arrays are denoted with square brackets (‘[ ]’).

Attachments, Treatments, and Users support search on the following integer arrays:

• • Attachments: Projects
  • Treaments: AssignedActivityIDs
  • Users: Groups, InvestmentReviewUserBackups, ConceptReviewUserBackups, ActivityFinancialUserBackups, WorkflowUserBackups, ConceptReviewGroupBackups, ActivityFinancialGroupBackups, and WorkflowGroupBackups

Since the data contained is integer arrays is more limited than collections, the sytax for searching an integer array is slightly simpler. For example, the following JSON could be used to search for users with associated with group 105 or 108 or 214 on the POST api/users/search route:

{
   "equals" :
   {
      "fieldName" : "groups",
      "fieldValue" : "[105, 108, 214]"
   }
}

“Standard” collection search only supports search on a single field in the collection. For example, let’s say we’re trying to find all Review Tasks assigned to a User that have not been voted on yet. Assume the following query sent to POST api/tasks/search:

{
	"and": [{
			"equals": {
				"collectionName": "assignees",
				"fieldName": "userId",
				"fieldValue": "[1]"
			}
		},
		{
			"equals": {
				"collectionName": "assignees",
				"fieldName": "voteResult",
				"fieldValue": "[0]"
			}
		}
	]
}

and assume the following data:

This search will return true for both Task 100 and 105.  This is because the User Id and Vote Result fields are inspected independently and not as a group.  The way the query reads is “return true if any assignee is User Id 1 and any assignee is Vote Result 0”. If instead you wanted only record 100 (where UserID=1 AND voteResult=0 in the same record), you would need to use the nested array syntax introduced in release to search both values within the same collection.