Date: Fri, 29 Mar 2024 00:24:26 +0000 (UTC) Message-ID: <1855712008.10837.1711671866171@911f0a1bad02> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10836_608867962.1711671866171" ------=_Part_10836_608867962.1711671866171 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Servoy provides high-level filtering functionality which may be = applied to any database table using the addTableFilterParam method= of the databaseManager API. Table filters have the following properties:= p>
There are only two ways to circumvent a table filter:
# by issuing a=
custom SQL query String through the getDataSetByQuery method of the databa=
seManager API. (Note: the version of getDataSetByQuery that takes a QueryBu=
ilder object as first parameter DOES take into account applicable TableFilt=
ers
# by using the rawSQL plugin
Therefore, if one wishes to maintain the effects of a filter, it is impo= rtant to remember to modify queries with an appropriate SQL WHERE clause in= case any of the above two cases apply
Data Provider Name - This is the left-hand operand. It is the name o= f a single column by which to filter. When filtering an entire server conne= ction, only tables which contain the named column will be filtered.
Operator - The following operators are supported
=3D |
Only records whose column equals the specified value |
< |
Only records whose column is less tha= n the specified value |
> |
Only records whose column greater tha= n the specified value |
>=3D |
Only records whose column greater tha= n or equals the specified value |
<=3D |
Only records whose column less than o= r equalsthe specified value |
!=3D |
Only records whose column does not eq= ual the specified value |
^ |
Only records whose column value is nu= ll |
LIKE |
Only records whose column matches using the <= strong>SQL LIKE construct (use wildcard '%' characters) |
IN |
Only records whose column value is in (using = the SQL IN construct) a list of values |
# |
Modifier, used to make case-insensitive queri= es |
|| |
Modifier used to concatenate two conditions w= / a logical OR |
Operators and modifiers may be combined, producing more complex conditio= ns. For example #^||!=3D would translate to: is null OR case-insensitive no= t equals
When using the IN operator, one should provide an array of values or a S= tring, which may be used as a sub select for the SQL IN clause.
Example This is a simple example which filters records = in a products table based on the criterion that the status is not discontin= ued
var succes= s =3D databaseManager.addTableFilterParam('crm_server','products','product_= status','!=3D',globals.STATUS_DISCONTINUED,'productfilter');=20
Example This example shows a two filters using the= IN operator
// Filter = products within an array of product codes var success =3D databaseManager.addTableFilterParam('crm', 'products', 'pro= ductcode', 'in', [120, 144, 200]); // Filter orders using a subselect var success =3D databaseManager.addTableFilterParam('crm', 'orders', 'count= rycode', 'in', 'select country code from countries where region =3D "Europe= "')=20
Example This example shows how to filter an entire serv= er connection by passing <null> for the table name. This is ideal for= multi-tenant architectures as an entire server connection can be filtered = by a single expression, i.e. the current company
// all tab= les that have the companyid column should be filtered var success =3D databaseManager.addTableFilterParam('crm', null, 'companyid= id', '=3D', globals.currentCompanyID)=20