Paging, Filtering and Sorting

 How to use Filter, Sort, Page and PageSize

• sorts is a comma-delimited ordered list of property names to sort by. Adding a – before the name switches to sorting descendingly.
• filters is a comma-delimited list of {Name}{Operator}{Value} where
  • {Name} is the name of a property for the given entity.
    • You can also have multiple names (for OR logic) by enclosing them in brackets and using a pipe delimiter, eg. (Phone|Mobile)==90919293 asks if Phone or Mobile equals 90919293
  • {Operator} is one of the supported Operators (see below for list of Operators).
  • {Value} is the case-insensitive value to use for filtering
    • You can also have multiple values (for OR logic) by using a pipe delimiter, eg. Notes@=new|old will return records with Notes that contain the text ‘new’ or ‘old’.
• Use | (single pipe) when the OR is on values for the same field and operator (eg. customerId==114961|114962). Use || (double pipe) when the OR spans different fields or operators.
• Use || between two complete filter expressions to OR them together. Multiple ||-separated expressions inside a single comma-delimited token are grouped and OR’d; separate comma-delimited tokens are AND’d as usual.
  • Example: AssignmentStatusID==1||AssignmentStatusID==2 returns records where status is 1 or 2. Here we may also use single pipe, as we’re filtering on 2 values of the same field.
  • Example: AssignmentStatusID==1||CustomerID==56700 returns records where status is 1 or customer is 56700.
• page is the number of page to return
• pageSize is the number of items returned per page. Default page size is 200.

GET /Customer

// sort by rowVersion descending, then by companyLastname
?sorts=-rowVersion,companyLastname

// filter by regemployeeIdExt equals to 1234,
// and by notes that contains the phrase 'some note'                    
&filters=regemployeeIdExt==1234, Notes@=some note,     

// get the first page
&page=1                                                

// get 10 customer records per page
&pageSize=10

Operator| Meaning
--------|--------
==      | Equals
!=      | Not equals
>       | Greater than
<       | Less than
>=      | Greater than or equal to
<=      | Less than or equal to
@=      | Contains 
_=      | Starts with
!@=     | Does not contain
!_=     | Does not start with

Special filters

IsNull: Use the IsNull filter if you need to find records where a certain property is null. Example query:

https://<api-base-url>/api/Customer?Filters=IsNull==CustomerIdext&Page=1&Pagesize=50

IsNotNull: Use the IsNotNull filter if you need to find records where a certain property is not null. Example query:

https://<api-base-url>/api/Customer?Filters=IsNotNull==CustomerIdext&Page=1&Pagesize=50

More example queries

https://<api-base-url>/api/Customer?Filters=RegemployeeIdext==2707720,rowVersion>349734047,(CompanyLastname|ContactFirstname)@=John,notes_=this is a test&Sorts=rowVersion&Page=1&Pagesize=50

The above example filters on

• regemployeeIdExt equal to 2707720
• rowVersion greater than 349734047
• companyLastname OR contactFirstname contains John (john would also work as the value search is case-insensitive)
• notes starts with this is a test

The result is sorted by rowVersion, and page 1 with 50 records is shown.

Here is an example of how to do filtering with IN(). The IN(..) operator is not supported, but we can use the OR (|) operator instead:

https://<api-base-url>/api/Customer?Filters=customerId==114961|114962|114963&Sorts=rowVersion&Page=1&Pagesize=50

The above example finds records where customerId is either 114961, 114962 or 114963.

Here is an example using the OR (||) operator to filter across different fields:

https://<api-base-url>/api/Assignment?Filters=AssignmentStatusID==1||AssignmentStatusID==2,rowVersion>349734047&Page=1&Pagesize=50

The above example finds assignments where the status is 1 or 2, AND rowVersion is greater than 349734047.
The || separates two full expressions inside a single filter token and OR’s them. The comma-separated rowVersion>349734047 is AND’d with the OR group as usual.