Filtering

In addition to using _start and _limit to control the set of rows returned you can specify one or more filters. Filters are specified as arrays of parameters; each filter contains the following elements:

Parameter	Mnemonic	Use
_ff[]	Filter Field	The name of the field, in the same format as ordering and grouping
_ft[]	Filter Type	A comparison operator. See list below
_fc[]	Filter Criteria	The (value) data to match, UTF-8 encoded as everything else is. These can be comma-separated when ‘eq’ comparisons are used resulting in the equivalent of an ‘IN ()’ clause. Since you can specify multiple values for filter criteria separated by a comma, you must escape a comma by prefixing it with a backslash if you are looking for a string containing a comma.

In addition to the array of parameters you may also specify the matching mode through the _fm parameter. There are currently three possible value types:

Parameter	Value type
_fm=and	The filter only includes rows that match all of the conditions specified by _ff[], _ft[] and _fc[]. This is also the default if _fm is not specified.
_fm=or	The filter includes any rows that match the conditions specified by _ff[], _ft[] and _fc[].
_fm= boolean expression	The filter only includes rows that match the conditions specified by _ff[], _ft[] and _fc[] according to the boolean expression.

As a special case, if you only have a single filter, you can omit the square brackets from the filter parameters to pass _ff[], _ft[] and _fc[].

Alternate filter syntax

Rather than passing over _ff[], _ft[] and _fc[] separately you can combine them into a parameter called _filter_json which is a JSON representation of an array of filters, each filter being an array of three items: filter field, filter type, filter criteria:

_filter_json=[["name","ct","Hello World"],["object_ref","eq","CASE-1234"]]

Boolean expressions in filtering

The boolean expression for the _fm parameter can include any of:

• NOT: boolean not (you can also use ! to mean NOT)
• AND: boolean and
• OR: boolean or
• XOR: boolean exclusive or
• parentheses for grouping
• numbers referring to the filters

For example:

    (1 AND 2) OR 3  

would require that only rows that match filters 1 and 2, or that match filter 3 are returned.

Filtering Types

The list of Filtering Types will grow and is currently:

Operator	Meaning	Data types
bg	Begins with	string
blank	Is blank	string, integer, float, currency, date, time, datetime
ct	Contains	string
eq	On	date
eq	At	time, datetime
eq	Equals	boolean, string, integer, float, currency
false	Is false	boolean
ge	Greater than or equal	integer, float, currency
ge	On or after	date, time, datetime
ge_today	Is on or after today	date, datetime
gt_today	After today	date, datetime
gt	Greater than	integer, float, currency
gt	After	date,time, datetime
le	Less than or equal	integer, float, currency
le	On or before	date, time, datetime
lt	Less than	integer, float, currency
lt	Before	date, time, datetime
le_today	Is on or before today	date, datetime
lt_today	Before today	date, datetime
nbg	Does not begin with	string
nct	Does not contain	string
ne	Not equal	boolean, string, integer, float, currency
ne	Not on	date
ne	Not at	time, datetime
not_blank	Is not blank	string, integer, float, currency, date, time, datetime
today	Is today	date, datetime
true	Is true	boolean

For the blank, not_blank, true, false, today, le_today, lt_today, ge_today and gt_today filtering types, fc[] should be present but is ignored. If you are testing boolean values using 'eq' or 'ne' then use the values 0 to represent false or 1 to represent true.

An example filter to filter all names that start with 'james':

      _ff[]=name&_ft[]=bg&_fc[]=james 

Further limiting the filter to employer 'ibm':

      _ff[]=name&_ft[]=bg&_fc[]=james&_ff[]=employer[name]&_fc[]=ct&_fc[]=ibm     

As you can see above since arrays of parameters are being passed it is important to specify them grouped sensibly together.