Filtering
Overview
Data filtering in Servoy is a powerful mechanism to control which records are visible to users, ensuring that only relevant data is displayed based on specific criteria. This capability is crucial for maintaining data security, enforcing business rules, and enhancing user experience by showing only pertinent data. Servoy provides two primary levels of data filtering: Foundset Filters and Table Filters. Each serves distinct purposes and is suited to different use cases, allowing for both temporary, user-driven filtering and persistent, session-wide data constraints.
Foundset Filters vs. Table Filters Foundset filters and table filters serve different purposes in Servoy's data management. Foundset filters are temporary and apply to individual foundsets, making them ideal for dynamic, user-driven filtering based on session-specific input. For example, a user can filter orders shipped to a specific city within their current session. In contrast, Table filters apply globally to entire tables or server connections and persist for the session duration, ensuring consistent data visibility rules across all users and sessions. They are best used for enforcing global business rules or permissions, such as restricting data access to only show products available for a specific company. Understanding these differences helps developers choose the appropriate method for filtering data based on their application's needs.
Foundset Filters
Foundset filters provide a way to temporarily restrict data visibility based on user input. These filters are useful when a user needs to apply specific criteria to view a subset of data without affecting other users or the entire application. These filters are ideal for creating dynamic user experiences where data visibility changes based on user interactions. Foundset filters can be layered to refine data visibility further.
Use Case: Temporary filters based on user input.
Foundset filters apply only to an individual foundset. They are valid until removed.
Example:
Filter orders where shipcity
is Berlin
.
Here are the API methods related to foundset filters:
Add Foundset Filters
There are several ways to add a Foundset filer:
addFoundSetFilterParam(query) In this example, we will add a filter to a foundset to show orders where the ship city is either "Amersfoort" or "Amsterdam". This filter will be permanent for the user session.
addFoundSetFilterParam(query, name) In this example, we will add a named filter to a foundset to show orders where the ship city is either "Amersfoort" or "Amsterdam". The filter is given a name
cityFilter
for easy removal later. This filter will be permanent for the user session and can be removed by name.
In this example, we will add a filter to a foundset to show orders where the customerid equals a specific value. This filter will be permanent for the user session and multiple filters can be added to the same dataprovider.
In this example, we will add a named filter to a foundset to show orders where the customerid equals a specific value. This filter will be permanent for the user session, and multiple filters can be added to the same dataprovider.
Create Table Filter Parameter and Apply to Foundset
In this example, we will create various table filters and apply them to a foundset. Multiple filters can be applied at the same time using foundset.setTableFilters()
.
Create a Simple Filter:
Create Filters with IN Conditions:
Create Filters with Modifiers:
Apply Multiple Filters to the Foundset:
Set Multiple Foundset Filters at the Same Time
In this example, we will create and apply multiple foundset filters at the same time. If the filters already exist with the same filter name, they will be removed and replaced with the new set of filters.
Create and Apply Multiple Filters:
Update One of the Filters:
Remove Filters by Setting Them to an Empty List:
Remove a Named Foundset Filter
In this example, we will remove a previously defined foundset filter using its given name and then reload the foundset to make the removal effective.
Remove the Named Filter:
Table Filters
Table filters apply constraints across entire tables or server connections, ensuring that only specific records are accessible throughout the session. These filters are crucial for enforcing business rules, such as multi-tenant data segregation or ensuring compliance with data visibility policies. Table filters are powerful for implementing business rules and data security at a broader level. These filters ensure consistent data visibility rules across the entire application session.
Use Case: Apply filters to entire sessions to enforce rules or permissions (e.g., only show data for a specific company).
Table filters apply to all foundset instances and datasets based on the filtered table/datasource. They are valid for the session duration or until programmatically removed.
Example: Restrict data access to only show products available for a specific company.
Filtering an entire server connection
This is ideal for multi-tenant architectures as an entire server connection can be filtered by a single expression, by passing null
for the table name.
Example:
All tables that have the companyid
column should be filtered.
Here are the API methods related to table filters:
Add Table Filters
There are several ways to add a Table filer:
In this example, we will add a table filter based on a query to all foundsets based on a table. This ensures that only records matching the query condition are visible.
In this example, we will add a table filter based on a query to all foundsets based on a table. The filter will be named for easy identification and removal.
In this example, we will add a table filter based on a datasource, dataprovider, operator, and value. This ensures that only records matching the filter condition are visible.
In this example, we will add a table filter based on a datasource, dataprovider, operator, value, and filter name. This ensures that only records matching the filter condition are visible.
In this example, we will add a table filter based on a server name, table name, dataprovider, operator, and value. This ensures that only records matching the filter condition are visible.
In this example, we will add a table filter based on a server name, table name, dataprovider, operator, value, and filter name. This ensures that only records matching the filter condition are visible.
Create Table Filters
There are several ways to create a Table filer:
In this example, we will create a table filter based on a query. This filter can be applied to all foundsets based on a table.
Create a Filter with a Query:
Apply Filter:
In this example, we will create a table filter that can be applied to all foundsets based on a table. Multiple filters can be applied at the same time using databaseManager.setTableFilters()
.
Create a Filter:
Apply Filter:
In this example, we will create and apply multiple table filters that can be applied to all foundsets based on a table. Multiple filters can be applied at the same time using databaseManager.setTableFilters()
.
Set Multiple Table Filters at the Same Time
In this example, we will apply multiple table filters to all the foundsets that are affected by the filters using databaseManager.setTableFilters()
.
Create Multiple Filters:
Apply Multiple Filters at the Same Time:
Update One of the Filters:
Remove Filters by Setting Them to an Empty List:
Remove a Named Table Filter
In this example, we will remove a previously defined table filter using the removeTableFilterParam
method.
Filtering Methods
Filters remain in effect until removed and can be used in concert with other.
Both foundset and table filters can be implemented using two approaches:
Logical Expression
This approach is ideal for straightforward filters. It covers most use cases.
A filter will contain a logical expression which is evaluated on behalf of records in the filtered foundset(s)/table(s). Only records, for which the expression evaluates to true, will be returned by any queries issued to the filtered foundset(s)/table(s). At runtime, the filter will be translated into an SQL WHERE
clause and appended to the query of any foundset which is bound to the filtered table(s).
An expression contains the following components:
Data Provider Name - This is the left-hand operand. It is the name of a single column by which to filter. In a table filter, when filtering an entire server connection, only tables which contain the named column will be filtered.
Operator - The following operators are supported
Data Provider Value - This is the right-hand operand and should evaluate to a literal value to be compared with the named column.
You can find examples of filters using Logical Expressions here.
QueryBuilder
This approach is ideal for advanced filters. It is useful for for scenarios where filters need to involve multiple columns, SQL logical operators, functions, aggregates, or nested conditions. The QueryBuilder (QB) approach is flexible and allows for complex filtering logic.
Query Builder used in Foundset Filters The table of the query has to be the same as the foundset table.
You can find examples of filters using QueryBuilder here.
Table of Operators
The following operators can be used for both foundset and table filters:
Operators and modifiers may be combined, producing more complex conditions. For example #^||!=
would translate to: is null OR case-insensitive not equals
.
When using the IN
operator, one should provide an array of values or a String, which may be used as a sub select for the SQL IN
clause.
Filters with in-conditions
can be used with arrays or with custom queries.
Naming Filters
When adding a table filter parameter, a filter name may be used to allow for the later removal of a named filter. Multiple parameters or conditions may be set using the same filter name. In this case, all parameters may be removed at the same time. It is recommended to name all filters so they can be easily removed later.
Multiple filters can be "stacked" on a foundset or datasource, and results will be constrained by all applied filters.
Examples:
Foundset Filter Filter orders where shipcity = X, named
cityFilter
:Table Filter Filter records in a products table based on the criteria that the status is not discontinued, named
productfilter
:
The loadAllRecords
method ensures that the foundset is reloaded with the new filter applied.
Removing Filters
Remove a named filter from a foundset / table.
Examples:
Foundset Filter
Table Filter
The loadAllRecords
method is used to refresh the foundset without the filter.
Examples of Filters using Logical Expressions
Foundset Filter Orders Between Two Dates Using Form Variables
In this example, we will add a filter to a foundset to display orders between two dates specified by form variables globals.startDate
and globals.endDate
.
Set the Form Variables:
Add the Filter:
Remove the Filter (if needed):
Foundset Filter Orders Based on a List of Customers Using IN Operator with array
In this example, we will filter a foundset to show orders based on a list of shipping countries. The list of shipping countries is stored in:
a form variable
Define the Form Variable:
Add the Filter:
a valuelist
Considering countries
a valuelist containing country names:
Define the countries list from the valuelist:
Add the Filter:
Foundset Filter Orders Based on a List of Customers Using IN Operator with custom queries
In this example, we will filter a foundset to show orders based on a list of specific shipping countries
Foundset Filter Customers Where CompanyName Starts with “big” (Case-Insensitive)
In this example, we will filter a foundset to show customers where the company name starts with "big", using a case-insensitive comparison.
Add the Filter:
Remove the Filter (if needed):
Table Filter: Where company = or IN companies
In this example, we will add a table filter to ensure that only records related to specific companies are shown. This filter will be applied on solution startup using the onOpen
event.
Considering globals.companyIDs
an array of Companies.
Apply the Table Filter on Startup:
Remove the Table Filter (if needed):
In this example, the addTableFilterParam
method is used to apply a table filter that restricts data to only those records where companyid
is in the list specified by globals.companyIDs
. This filter is added during the solution's startup by calling the onSolutionOpen
function, which is assigned to the onOpen
event of the solution. To remove the filter, the removeTableFilterParam
method is used.
Using the Data Broadcast Flag:
Set the Data Broadcast Flag: In this example, we will set the dataBroadcast
flag for a table filter to reduce data broadcast events for clients having a data broadcast filter set for the same column with a different value.
IMPORTANT
dataBroadcast
flag is only supported for simple filters using the in
or =
operators.
Create a Filter with the Data Broadcast Flag:
In this example, the dataBroadcast
method is used to set the dataBroadcast
flag to true
for a filter created using createTableFilterParam
. This filter will help reduce data broadcast events for clients with a data broadcast filter set for the same column with a different value.
Examples of Filters using QueryBuilder
Foundset Filter Using QueryBuilder and Aggregates
In this example, we will use QueryBuilder to create a foundset filter that shows customers who have placed more than a specified number of orders. This example uses an aggregate function to count the number of orders per customer.
Create the QueryBuilder with Aggregate:
Add the Filter:
Remove the Filter (if needed):
In this example, the QueryBuilder is used to construct a query that aggregates order data to count the number of orders per customer. The having
clause filters customers based on the aggregate condition (e.g., customers with more than 10 orders). The createTableFilterParam
method creates a filter based on this query, and setFoundSetFilters
applies the filter to the foundset. The loadAllRecords
method ensures the foundset is reloaded with the new filter applied. To remove the filter, removeFoundSetFilterParam
is used followed by loadAllRecords
to refresh the foundset without the filter.
Foundset Filter Using QueryBuilder and Functions
In this example, we will use QueryBuilder to create a foundset filter that shows customers whose last order amount is greater than a specified value using the SUM function to calculate the total order amount.
Create the QueryBuilder with Functions:
Add the Filter:
Remove the Filter (if needed):
In this example, the QueryBuilder is used to construct a query that aggregates order data to calculate the total order amount for each customer using the SUM
function. The having
clause filters customers based on the aggregate condition (e.g., customers with a total order amount greater than 1000). The createTableFilterParam
method creates a filter based on this query, and setFoundSetFilters
applies the filter to the foundset. The loadAllRecords
method ensures the foundset is reloaded with the new filter applied. To remove the filter, removeFoundSetFilterParam
is used followed by loadAllRecords
to refresh the foundset without the filter.
Foundset Filter - Orders with QueryBuilder where Ship City is [A OR B] AND NOT C
In this example, we will use the QueryBuilder (QB) to filter a foundset to show orders where the ship city is either 'A' or 'B' and not 'C'.
Create the QueryBuilder Condition:
Add the Filter:
Remove the Filter (if needed):
In this example, the QueryBuilder
is used to construct a condition where the shipcity is either 'A' or 'B', and not 'C'. The addFoundSetFilterParam
method applies this filter to the foundset, and loadAllRecords
ensures that the foundset is reloaded with the new filter applied. To remove the filter, removeFoundSetFilterParam
is used followed by loadAllRecords
to refresh the foundset without the filter.
Best Practices
Always Name Filters: This allows for easy management, especially when you need to remove or modify them.
Use Stacked Filters: Combine multiple filters to achieve the desired level of data restriction.
Test Filters Thoroughly: Ensure that filters apply the correct constraints and do not unintentionally hide necessary data.
Additional Links
The following articles are recommended for additional reading:
Reference for Foundset Filter
Reference for Table Filter
Searching Guide: QueryBuilder Guide
SvyPopupFilter: Combines Filters into a Reusable UI Pattern
Last updated