Searching

Search Routes

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 fields at this time.

Search Operators

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.

Examples: Searching with Comparison Operators

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"
    }
}

Examples: Searching with AND Operator

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"
            }
        }
    ]
}

 

Examples: Searching with the NOT Operator

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"
            }
        }
}

Examples: Searching with the OR Operator

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
		    }
		}
	]
}

Examples: Specifying Precedence when Searching with a Combination of Boolean Operators

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"
            }
		}
	]
}

Examples: Searching a Collection

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]"
   }
}

Examples: Searching an Integer Array

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]"
       }
    }