Skip to content
Developerhome
Product homepage
People
API Status:

Checking…

Filtering

  Less than to read

API Filtering Guide

The Sage People API enables you to control what data is returned in the API response through the filtering mechanism using the $filter query parameter. We follow the filtering syntax defined in the Microsoft REST API guidelines.

The Sage People API supports filtering using the $filter parameter on all API endpoints.

Filtering is supported for the following fields:

  • Standard fields (fields returned by the api by default)
  • Additional fields (see additional fields guide for more information)

Using the $filter parameter

The Sage People API $filter is a query parameter and is applied to the end of the request url using a ? to indicate the start of a query. The examples used throughout are applied against the /bonuses endpoint.

The $filter parameter has three parts:

  1. {api field} is the name of the field or the name of a field within a nested object as it appears in the api response. Nested objects such as currentEmployment and workAddress in the Employees API are referenced in the filter using dot notation. For example: a field is fullName, a field within a nested object is: workAddress.name.
  2. {operator} is the operator to be applied to the fields value. For example: eq, gt, etc. A full list of supported operators can be found below.
  3. {field value} is the raw value of the api field to filter by.

NOTE: You cannot apply a filter against a nested object directly such as $filter=workAddress eq null

The field value in the filter must be entered in a format matching the API field’s data type:

  • use single quotation marks for string values. For example:'Performance'
  • do not use single quotation marks for decimal, integer, or boolean values. For example: 100.00, 100, true, false
  • enter dates in the ISO format yyyy-mm-dd. For example: 2020-01-01

When using decimal numbers in a filter, make sure you format them consistently with the decimal separator (.). Do not use the comma separator (,). For example $filter=amount eq 5,00 must be written as: $filter=amount eq 5.00

The full format of a request using a filter:

/spapi/{version}/{endpoint}?$filter={api field} {operator} {field value}

Here are some examples of a valid request using the $filter parameter:

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=amount gt 1000

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=reason eq 'performance'

Logical Operators

You can chain multiple filter operations together using logical operators (and, or):

/spapi/{version}/{endpoint}?$filter={api field} {operator} {field value} {LOGICAL OPERATOR} {api field} {operator} {field value}

These can be used to specify more explicit or complex filtering operations. You can only chain filter operations using the same logical operator (multiple ands or multiple ors) unless you enforce the precedence by using grouping operators. For example, the following is a valid filter:

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=reason eq 'performance' and amount gt 5000 and paidDate gt 2020-01-01

The example below is not supported:

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=reason eq 'performance' and amount gt 5000 or paidDate gt 2020-01-01

NOTE: Logical operators must be written in lower case.

Grouping Operators

Grouping Operators () can be used with logical operators to develop more complex queries. You can group a filter statement within the parentheses to enforce precedence against that statement. Below are some examples of how grouping operators can be used:

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=reason eq 'performance' or (amount gt 5000 and paidDate gt 2020-01-01)

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=(reason eq 'performance' and amount gt 5000) or paidDate gt 2020-01-01

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=(reason eq 'performance') or (amount gt 5000 or paidDate gt 2020-01-01)

Unary Operators

The unary operator not can be used to negate the result of a simple expression or grouping. It cannot be used within the same group as logical operators (and & or). For example, the following is valid:

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=not (reason eq 'performance' or reason eq 'Commission')

However, the following is NOT valid:

{orgURL}/services/apexrest/spapi/{version}/bonuses?$filter=not reason eq 'performance' or reason eq 'Commission'

Supported Operators

The Sage People API currently supports the following filter operators.

Operator Description Example
Comparison Operators    
eq Equal reason eq ‘performance’
ne Not equal paidDate ne 2020-01-01
gt Greater than amount gt 2000.00
ge Greater than or equal amount ge 1000
lt Less than amount lt 200
le Less than or equal amount le 1000
Logical Operators    
and Logical AND reason eq ‘performance’ and paidDate ne 2020-01-01
or Logical OR paidDate ne 2020-01-01 or amount gt 2000.00
not Logical negation not amount lt 200
Grouping Operators    
() Precedence grouping reason eq ‘performance’ or (amount gt 5000 and paidDate gt 2020-01-01)

Additional Fields

The Sage People API only supports filtering on fields returned as part of the API response. The additional fields mechanism allows for other object fields to be added to the API response. These fields can then be used to filter the response data like any other API field. However, unlike the standard fields, additional fields are filtered using the source (Salesforce SOQL field name) as the field name, not the field’s API name.

The filter is constructed using an object name and/or object relationship separated by a dot.

  • {Namespaced Object}
  • {Namespaced Field}

For example:

fHCM2__Bonus__c.fHCM2__Pay_Code__c

NOTE: You cannot use a Namespaced Object name by itself as a filter: you must use the full reference of the Namespaced Object plus the Namespaced Field.

For standard Salesforce fields such as ‘id’ and ‘name’ the source is the same ‘id’ and ‘name’ see the example output below:

Additional Fields Filtering

Encoding

You must encode the entire URL including the filter string, not just the base URL.

This is particularly important if your field values contain special characters, such as the plus sign +. For example, if a telephone number includes the country dialling code '+44 7846 746930', add it to your filter as '%2b44 07424703356'.