Filtering and sorting

Choose fields and operators

Use field System names from the module. A qualified table.field is accepted only if it matches the field's own table. Otherwise a filter returns INVALID_FILTER.

contains, starts_with and ends_with treat % and _ literally.

How conditions combine

Conditions in and must all match. The conditions in or form one group, (x OR y OR …), which is ANDed with the rest. Each entry in groups is built the same way, wrapped in brackets, and ANDed with the rest.

So a filter is always an AND of parts, where each part is a condition or an OR-group of conditions. Groups can't be ORed with each other.

Worked example 1

"status is Open or Pending, and amount over 1000":

{ "and": [ {"field":"amount","operator":"gt","value":1000} ],
  "or":  [ {"field":"status","operator":"eq","value":"Open"}, {"field":"status","operator":"eq","value":"Pending"} ] }

The same thing is shorter with in: {"field":"status","operator":"in","value":["Open","Pending"]}.

Worked example 2

"priority 1 or (stage is Late and nobody is assigned)". This is A OR (B AND C), which is the same as (A OR B) AND (A OR C). Write it as two groups, each with an or list:

{ "groups": [
    { "or": [ {"field":"priority","operator":"eq","value":1}, {"field":"stage","operator":"eq","value":"Late"} ] },
    { "or": [ {"field":"priority","operator":"eq","value":1}, {"field":"assigned_to","operator":"is_null"} ] } ] }

For anything more complex, run two queries and merge the results by id.

Filter cookbook

Use these as the filters object in a query. Match field names to your module first.

Priority is 1

{"and":[{"field":"priority","operator":"eq","value":1}]}

Priority is at most 2

{"and":[{"field":"priority","operator":"lte","value":2}]}

Title contains laptop

{"and":[{"field":"title","operator":"contains","value":"laptop"}]}

Title starts with Laptop

{"and":[{"field":"title","operator":"starts_with","value":"Laptop"}]}

Priority is 1 or 2

{"and":[{"field":"priority","operator":"in","value":[1,2]}]}

Needed by has a value

{"and":[{"field":"needed_by","operator":"is_not_null"}]}

Sort your results

Use module field names. direction is asc by default or desc. If no sort field is valid, the API returns INVALID_SORT.

{"sort":[{"field":"needed_by","direction":"asc"}]}

Sorting by a date field doesn't require sending a date value. For date, datetime, decimal and boolean filter values, use values as shown in the Designer.