Docs
ServoyCloudSupportSamplesOpen SourceCommunity
  • Home
  • GUIDES
    • Platform Overview
    • Get Servoy
    • Get Started
      • Chapter 0
      • Chapter 1
      • Chapter 2
      • Chapter 3
      • Chapter 4
      • Chapter 5
    • Get Help
      • SuLA Instructions
      • SuLA Terms
    • Develop
      • Application Design
        • Forms
          • Layout Management
            • Simple Layout
            • Fully Responsive
              • Bootstrap 12-Grid
              • Flexbox
              • Custom Div
              • Collapsible Container
              • Simple Collapsible
              • Center Container
              • Inline Group Container
            • Abstract Form
          • Form Containers
            • Form Container
            • Tab Panel
            • Split Pane
            • Collapse
            • Accordion Panel
            • Form Component
            • List Form Component
          • Form Inheritance
          • Form Components
        • UI Components
          • Buttons and Text
            • Badge
            • Button
            • Data Label
            • Dropdown
            • Image
            • Label
          • Grids
            • Data Grid
            • Power Grid
            • Table
          • Input Controls
            • Calendar
            • Calendar Inline
            • Checkbox
            • Choice Group
            • Combobox
            • Group Buttons
            • Html Area
            • Input Group
            • Rating
            • Slider
            • Switch
            • Spinner
            • TextArea
            • Textbox
            • TextBoxGroup
            • Type Ahead
            • select2tokenizer
          • Media
            • Carousel
            • Embedded YouTube
            • File upload
            • Image label
            • Lightbox Gallery
            • MultiFile Upload
            • PDF JS Viewer
            • PDF Viewer
          • Mobile
            • Native Data List
            • Native Select
          • Navigation
            • Breadcrumbs
            • Navbar
            • sidenav
          • Visualization
            • DBTreeview
            • Progress Bar
            • Treeview
            • ChartJS
              • Data Labels Plugin
              • Funnel Chart
              • OutLabels Plugin
              • TreeMap
          • Specialized Components
            • Smart Document Editor
            • Servoy PDF Viewer
            • Fullcalendar Component
              • Fullcalendar
              • Fullcalendar Events
              • Migrating calendar from Smart and Web client to NG client
            • Fullcalendar Component 2
            • Canvas
            • Google Charts
            • Google Maps
            • svySignature
            • svywebcam
        • Styling and Themes
          • Default Theme Properties
          • CSS / LESS
          • Component Variants
          • Font Icons
        • Data Modeling
          • Databases
            • Tables
              • Columns
              • Calculations
              • Aggregations
              • Methods
              • Events / Triggers
              • Table security
            • Views
            • Procedures
          • Relations
          • Value Lists
          • Menus
          • In-Memory Databases
          • View Datasource
        • Modular Design
      • Programming Guide
        • Scripting the UI
          • Scripting Forms
          • Interacting with Components
          • Navigation
          • Windows, Dialogs and Popups
          • Events Manager
        • Working with Data
          • Concepts
          • Loading
          • Sorting
          • Iterating
          • Searching
            • Find mode
            • Query Builder
            • SQL String
          • Filtering
          • Editing
          • Validation
        • Automation and Scheduling
          • Batch Processor
          • Headless API
          • Scheduler API
        • Creating REST APIs
        • Consuming Webservices
        • Working with Files
          • File Plugin Basics
          • Images
          • Excel
          • PDF
          • JSON
        • Browser Utilities
          • Notifications
            • Web Notifications (Toastr)
            • Web Notifications (Native)
            • Block UI
          • Session Management
          • Keyboard Utilities
          • NG Utils
        • Sending and Receiving Email
        • Servoy Jasper Reports
          • Input Type
          • Display Mode
          • Output format
          • Jaspe Reports Viewer
        • Extensions
          • Modules
            • svyPhonegap
              • First Use Guide
              • Barcode Scanner
              • Browser
              • Camera
              • File
              • Fingerprint
              • Location
              • Network Interface
              • Phonegap
              • Printer
              • Push Notifications
            • svyAPI
              • Getting Started
            • svyLookup
              • Custom Templates
              • API Documentation
              • svyLookup v1
                • API Documentation v1.0.0
                • Base Form svyLookupTable v1.0.0
                • Base Form AbstractLookup v1.0.0
            • svyNavigation
              • API Documentation
                • API svyNavigation
                • API svyNavigationHistory
                • API svyNavigationUX
            • svyPopupFilter
              • Simple Filter Pickers
              • Custom Templates
              • API Documentation
                • API svyToolbarFilter
                • API svyPopupFilter
              • API Documentation v1
                • API svyToolbarFilter v1
                • API svyPopupFilter v1
            • svySearch
            • svySecurity
              • Overview
              • Token-based Auth and SSO (NEW!)
              • API Documentation
              • Getting Started
              • Tenant replication
              • Security Management Console
                • Classic Security Management Console
              • svySecurityUX
              • svyProperties
              • API Doc svyProperties
            • svyUtils
              • Log Manager
              • Log Manager Appenders
              • Smart Doc Editor Utils
              • Custom Dialogs
              • Excel Utils
                • Full API Reference
              • Crypto Utils
      • Security
        • Authentication
        • Permissions
          • UI Permissions
          • Data Permissions
          • Runtime Permissions
        • Audit Logging
        • OAuth
        • JWT
      • Multi-Language Support
      • Mobile Development
        • Getting Started
        • Mobile Design
        • Native Mobile Integrations
        • Building a Native Binary for the App Store
      • Native Desktop Development
        • NGDesktop UI plugin
        • NGDesktop Utils
        • NGDesktop File Plugin
    • Deploy
      • WAR Deployment
      • Docker Deployment
      • Server Configuration
        • Server Settings
        • Server Plugins
        • Database Servers
          • Locks
          • Transactions
          • Oracle
          • SQLServer
        • Client Sessions
        • Batch Processors
        • License Management
        • Monitoring
          • Server Log
          • Browser Console Log
          • Websocket Log
          • Query Performance
          • Client Performance
          • Client Event Tracing
          • Monitoring Servoy with JMX
      • NGClient deployment
      • Troubleshooting
      • Other Topics
        • Security: Cross-site Scripting
        • Running in an iFrame
    • Extend
    • Contribute
  • Reference
    • Servoy Core
      • Object Model
        • Database Server
          • Table
            • Column
            • Calculation
            • Method
            • Aggregation
          • View
          • Procedure
        • Solution
          • Form
          • Form Component
          • Relation
          • Relation Item
          • Table Node
          • Value List
          • Menu
          • Menu item
          • In-Memory Data Source
          • View Foundset Data Source
          • Variable
          • Column Info
          • Media
          • Layout Container
          • Method
        • i18n Message Key
      • Developer API
        • Globals
        • Forms
          • RuntimeForm
            • controller
            • containers
              • RuntimeContainer
            • elements
              • Component
              • RuntimeAccordionPanel
              • RuntimeBean
              • RuntimeButton
              • RuntimeCalendar
              • RuntimeCheck
              • RuntimeCombobox
              • RuntimeComponent
              • RuntimeDataButton
              • RuntimeDataLabel
              • RuntimeGroup
              • RuntimeHtmlArea
              • RuntimeImageMedia
              • RuntimeInsetList
              • RuntimeLabel
              • RuntimeListBox
              • RuntimePassword
              • RuntimePortal
              • RuntimeRadio
              • RuntimeChecks
              • RuntimeRadios
              • RuntimeRectangle
              • RuntimeRtfArea
              • RuntimeSpinner
              • RuntimeSplitPane
              • RuntimeTabPanel
              • RuntimeTextArea
              • RuntimeTextField
              • RuntimeWebComponent
        • JS Lib
          • Array
          • BigInt
          • Boolean
          • Date
          • Function
          • IterableValue
          • Iterator
          • JSON
          • Map
          • Set
          • Math
          • Namespace
          • Number
          • Object
          • Promise
          • QName
          • RegExp
          • Special Operators
          • Statements
          • String
          • XML
          • XMLList
        • Application
          • APPLICATION_TYPES
          • CLIENTDESIGN
          • DRAGNDROP
          • ELEMENT_TYPES
          • LOGGINGLEVEL
          • NGCONSTANTS
          • UICONSTANTS
          • CSSPosition
          • JSDimension
          • JSDNDEvent
          • JSEvent
          • JSLogger
          • JSLogBuilder
          • JSPoint
          • JSUpload
          • JSWindow
          • JSBounds
          • Renderable
          • UUID
        • SolutionModel
          • ALIGNMENT
          • ANCHOR
          • BEVELTYPE
          • CURSOR
          • DEFAULTS
          • FONTSTYLE
          • MEDIAOPTION
          • PAGEORIENTATION
          • PRINTSLIDING
          • SCROLLBAR
          • TITLEJUSTIFICATION
          • TITLEPOSITION
          • UNITS
          • JSBean
          • JSButton
          • JSCalculation
          • JSComponent
          • JSDataSourceNode
          • JSField
          • JSForm
          • JSLabel
          • JSMedia
          • JSMethod
          • JSPart
          • JSPortal
          • JSRelation
          • JSRelationItem
          • JSStyle
          • JSTab
          • JSTabPanel
          • JSValueList
          • JSVariable
          • JSTitle
          • JSChecks
          • JSCombobox
          • JSHeader
          • JSInsetList
          • JSList
          • JSPassword
          • JSRadios
          • JSText
          • JSTextArea
          • JSWebComponent
          • JSLayoutContainer
        • Database Manager
          • QUERY_COLUMN_TYPES
          • SQL_ACTION_TYPES
          • JSColumn
          • JSDataSet
          • JSBaseRecord
          • JSBaseSqlRecord
          • JSBaseFoundset
          • JSBaseSQLFoundset
          • JSFoundSet
          • JSFoundSetUpdater
          • JSRecord
          • JSRecordMarker
          • JSRecordMarkers
          • JSTable
          • JSTableFilter
          • MenuFoundSet
          • MenuItemRecord
          • QBAggregate
          • QBAggregates
          • QBCase
          • QBCaseWhen
          • QBColumn
          • QBColumns
          • QBCondition
          • QBFunction
          • QBFunctions
          • QBGroupBy
          • QBJoin
          • QBJoins
          • QBLogicalCondition
          • QBParameter
          • QBParameters
          • QBPart
          • QBResult
          • QBSearchedCaseExpression
          • QBSelect
          • QBSort
          • QBSorts
          • QBTableClause
          • QBWhereCondition
          • ViewFoundSet
          • ViewRecord
        • Events Manager
          • EVENTS_AGGREGATION_TYPES
          • EventType
        • Menus
          • JSMenu
          • JSMenuItem
        • JSMenuDatasource
        • Utils
        • Client Utils
          • JSBlobLoaderBuilder
        • History
        • Security
        • i18n
        • ServoyException
          • DataException
        • JSUnit
        • Datasources
          • DBDataSource
          • DBDataSourceServer
          • JSConnectionDefinition
          • JSDataSource
          • MemDataSource
          • MenuDataSource
          • SPDataSource
          • SPDataSourceServer
          • ViewDataSource
        • JSViewDatasource
        • servoyDeveloper
        • enum
    • Extensions
      • UI Components
        • Buttons and Text
          • Badge
          • Button
          • Data Label
          • Drop Down
          • Image
          • Label
        • Form Containers
          • Accordion Panel
          • Collapse
          • Form Container
          • Form Component Container
          • List Form Component
          • Default Loading Indicator
          • Error Bean
          • Portal
          • Responsive Container
          • Servoy Default Navigator
          • Slider
          • Split Pane
          • TabPanel
        • Grids
          • Data Grid
          • Power Grid
          • Table
        • Input Controls
          • Calendar
          • Calendar Inline
          • Checkbox
          • Choice Group
          • Combobox
          • Floatinglabel Calendar
          • Floatinglabel Combobox
          • Floatinglabel Textarea
          • Floatinglabel Textbox
          • Floatinglabel Type Ahead
          • Group Buttons
          • Html Area
          • Input Group
          • Rating
          • Select2tokenizer
          • Slider
          • Spinner
          • Switch
          • TextArea
          • TextBox
          • TextBox Group
          • Type Ahead
        • Media
          • Carousel
          • Embedded YouTube
          • File upload
          • Image label
          • Lightbox Gallery
          • MultiFile Upload
          • PDF JS Viewer
          • PDF Viewer
        • Mobile
          • Native Data List
          • Native Select
        • Navigation
          • Breadcrumbs
          • Navbar
          • sidenav
        • SmartDocEditor
          • Smart Document Editor
        • Visualization
          • Canvas
          • Chart
          • Custom List
          • DBTreeview
          • Foundset List
          • Fullcalendar
          • Gauge
          • Google Maps
          • Kanban
          • Progress Bar
          • Treeview
      • Server Plugins
        • amortization
          • AmortizationCalculation
          • Polynomial
        • clientmanager
          • Broadcaster
          • JSClientInformation (cm)
        • excelxport
        • file
          • JSFile
          • JSProgressMonitor
        • headlessclient
          • JSClient
        • http
          • HTTP_STATUS
          • Cookie
          • DeleteRequest
          • GetRequest
          • HeadRequest
          • HttpClient
          • HttpClientConfig
          • OptionsRequest
          • PostRequest
          • PutRequest
          • Response
          • TraceRequest
          • PatchRequest
          • JSFileUpload
        • images
          • JSImage
        • jwt
          • Algorithm
          • Builder
          • JWTClaims
        • mail
          • Attachment
          • MailMessage
        • maintenance
          • JSClientInformation
          • JSColumnObject
          • JSServer
          • JSTableObject
        • mobileservice
          • OfflineDataDescription
        • oauth
          • ClientAuthentication
          • CustomApiBuilder
          • OAuthProviders
          • OAuthRequest
          • OAuthResponse
          • OAuthResponseBinary
          • OAuthResponseJSON
          • OAuthResponseText
          • OAuthService
          • OAuthServiceBuilder
          • OAuthTokenExtractors
          • RequestType
        • pdf_output
        • rawSQL
        • RestWs plugin
        • rest_ws
          • WsContents
          • WsCookie
          • WsRequest
          • WsResponse
        • scheduler
        • serialize
        • textxport
          • DataProviderExport
          • TabExporter
        • udp
          • JSPacket
        • XmlReader
          • XmlNode
      • Browser Plugins
        • Block UI
        • Block UI (ref)
        • Dialogs Plugin
        • Idle
        • Idle (ref)
        • Key Listener
        • Key Listener (ref)
        • NGDesktop File
        • NGDesktop File (ref)
        • NGDesktop UI (ref)
        • NGDesktop Utils (ref)
        • NG Utils (ref)
        • Office Javascript API for Servoy
        • Phonegap
        • Web Notifications (Native)
        • Web Notifications (Toastr)
        • Window Plugin
        • Font Awesome
      • Modules
        • Smart Doc Editor Utils
        • svyAPI
          • Getting Started
        • svyLookup
          • Custom Templates
          • API Documentation
          • svyLookup v1
            • API Documentation v1.0.0
            • Base Form svyLookupTable v1.0.0
            • Base Form AbstractLookup v1.0.0
        • svyLookupExample
        • svyNavigation
          • API Documentation
            • API svyNavigation
            • API svyNavigationHistory
            • API svyNavigationUX
        • svyNavigationUX
        • svyNavigationUXSample
        • svyPopupFilter
          • Simple Filter Pickers
          • Custom Templates
          • API Documentation
            • API svyToolbarFilter
            • API svyPopupFilter
          • API Documentation v1
            • API svyToolbarFilter v1
            • API svyPopupFilter v1
        • svyProperties
        • svySearch
        • svySecurity
          • Overview
          • Token-based Auth and SSO (NEW!)
          • API Documentation
          • Getting Started
          • Tenant replication
          • Security Management Console
            • Classic Security Management Console
          • svySecurityUX
          • svyProperties
          • API Doc svyProperties
        • svySecurityConsole
        • svySecurityUX
        • svyUtils
          • Log Manager
          • Log Manager Appenders
          • Smart Doc Editor Utils
          • Custom Dialogs
          • Excel Utils
          • Full API Reference
          • Crypto Utils
        • svyUtils$Excel
        • svyUtils$NGClient
        • svyUtils$customDialogs
        • svyUtils$logManagerAppenders
        • svyUtils$tableGrid
      • Layout
        • Boostrap 12grid layout
          • Collapsible Container
          • Simple Collapsible
          • Center Container
          • Inline Group Container
          • Flexbox Layout
      • Solutions
        • Sample Application
          • Tutorial Part 1
          • Tutorial Part 2
      • Packages
        • UI Component Packages
          • Advanced Renderers
          • Bootstrap Components
          • Bootstrap Extra Components
          • Canvas
          • Chart JS
          • Fullcalendar Component
          • googlemaps
          • kanban
          • Servoy Core
          • Servoy Extra Components
          • Servoy NG-Grids
          • Servoy PDF Viewer
          • smartDocumentEditor
        • Browser Plugin Packages
          • Block UI
          • Core NG only Services
          • Key Listener
          • NGDesktop File
          • NGDesktop UI
          • NGDesktop Utils
          • Web Notifications
          • Idle Web Service
    • Servoy Developer
      • Menu
        • File
        • Edit
        • Source
        • Refactor
        • Navigate
        • Search
        • Project
        • Actions
        • Run
        • Window
        • Help
      • Toolbar
      • Solution Explorer
        • Resources
          • Database Servers
            • Database Server
              • Procedures
              • Tables
              • Views
          • Security
          • i18n
        • All Solutions
          • Solution
          • Active Solution
            • Scopes
              • Scope
                • Variables
            • Forms
              • Working Set
              • Form
                • Controller
                • Variables
                • Elements
                • Relations
            • Form Components
              • Form Component
            • Relations
              • Relation
            • ValueLists
              • ValueList
            • Menus
              • Menu
                • MenuItem
            • Media
              • Folder
              • File
            • DataSources
              • In Memory DataSources
                • In Memory DataSource
              • View Foundsets DataSources
                • View Foundset DataSources
            • Servoy Packages
              • Package
            • Modules
              • Module
        • Solution Explorer Contextual List
      • Views
        • Command Console
        • Console
        • Problems
        • Tasks
        • Bookmarks
        • Search
        • Form Hierarchy
        • Help
        • JUnit
        • Outline
        • Profiler
        • Properties
        • Project Explorer
        • Call Hierarchy
      • Editors and Wizards
        • Property configurator for columns
        • Relation Editor
        • Text Format Editor
        • Date Format Editor
        • Number Format Editor
        • Database Synchronization Wizard
        • Security Editor
        • SQL Editor
        • i18n Editor
        • File Import Wizard
        • File Export Wizard
        • WAR Export Wizard
        • NGDesktop Export Wizard
        • Table Editor
        • Metadata Synchronization Wizard
        • Database Server Connection Editor
        • Text Property Editor
        • Valuelist Editor
        • Form Editor
          • Parts of the Form Editor
          • Designing a Form
          • Layout Tools
          • Using Containers
          • Creating and Using Forms
          • Using Servoy Beans
          • Using Shapes
          • Form Editor Subtabs
          • [Tips and Shortcuts][reference/servoy-developer/object-editors/form-editor-tips_and_shortcuts.md]
          • Editong a Responsive LayoutForm
          • CSS Positioning
        • Form Hierarchy
        • Component Properties Editor
        • Variable Editor
        • Method Selection Wizard
        • New Solution Wizard
        • Application Event Types Editor
        • New Form Wizard
        • Form Dataproviders Configurator
        • Servoy Resource Locator
        • Datasource Selection Wizard
        • Sorting Fields Wizard
        • Scripting Editor
          • Getting Started-Overview of Script Editor
          • Code Writing Features
          • Code Navigation
          • Code Rewriting Tools
          • Refactoring
          • Code Formatting
          • Script Editor Tips and Shortcuts
          • Unit Tests
        • Theme Editor
          • General Properties
          • Sidenav Properties
          • Navbar Properties
          • Tabs Properties
          • Windows Properties
          • Dialogs Properties
          • Tables Properties
          • Breadcrumb specific style Properties
          • Brand colors Properties
          • Validations Properties
          • Margins and Padding Properties
        • Editor Selection
        • Run Configurations
        • Debug Configurations
        • Externalize Strings Wizard
        • Font Chooser Wizard
        • Color Chooser Wizard
        • Project Properties Configurator
        • Move Wizard
        • External Tools Configurations
        • About Servoy Developer
        • Servoy Developer Installation Details
        • Eclipse Marketplace Wizard
        • Install Available Software Wizard
        • Cheat Sheet Selection Wizard
        • Servoy Developer Help Wizard
        • Switch to Editor Wizard
        • Find Actions Wizard
        • Customize Perspective Configurator
        • Quick Search Wizard
        • Search Wizard
        • Encoding Wizard
        • Task Wizard
        • Find - Replace Wizard
        • Properties Editor
        • New Project Wizard
        • New Method Wizard
        • New Menu Wizard
          • New MenuItem Wizard
        • Menu Editor
        • ServoyMenu Selection Wizard
        • JSMenu compatible component selector
        • Permissions Configuration Dialog
      • Project File Structure
        • Servoy Installation Directory
          • servoy.properties
        • Workspace Folder
          • Resources Directory
            • Database Information (.dbi) Files
          • Solution Folder
      • Package Manager
      • Preferences
      • Property Types for components / services
      • Debugger
        • Debug Explorer
        • Breakpoints
          • Breakpoint Properties
        • Variables
        • Expressions
        • Interactive Console
    • Application Server
    • Servoy Cloud
      • Cloud Control Center
        • Home
          • Setup Namespace
          • User profile
            • My Profile
            • Support
        • Download IDE
        • Application Overview
          • Applications
            • Pipelines
              • Jobs
                • Packages
                  • Commits
                  • Artifacts
                  • Configuration
                  • Quality reports
                  • Build markers
                • Job Configuration
                  • Build and Deploy or Build
                  • Mobile
                  • Desktop
            • Environments
              • Metrics
                • Users
                • Databases
                  • Metrics
                • System Health
                • Log Activity
                • App performance
                • Query performance
              • Cloud reporting
            • Security
              • Permissions
              • Tenants
            • Reports
            • Security Login Designer
              • Single Sign-On
                • Sign in with Google
                • Sign in with Microsoft
          • Code Repositories
        • Project Management
          • Tickets
          • Backlog
          • Active Sprints
        • User Management
          • Users
          • Roles
        • Administration
          • Security
            • ServoyCloud Roles
          • Credentials
          • Settings
        • Add-Ons
      • Database Backup
      • E2E Cypress Testing
        • Using Cypress
      • On-Premise Deployment
      • Privacy policy (ServoyAI)
      • FAQ
      • Migrate to Servoy Cloud
    • Extensions Developement
      • Component and Services
        • Component Development
        • Service Development
        • Tools and Dev Tips to use for Component/Service developement
        • Manifest (.mf file)
        • Specification (.spec file)
          • Property Types
            • Array property types
            • Custom object property types
            • Findmode property type
            • Foundset property type
            • Tags
        • Directives And Filters
          • Sablotabsequence
        • Console (serverside)
        • ServoyApi (serverside)
      • Serverside Plugins
        • Data Convertors and Validators
        • UI Convertors
  • Release notes
    • Release notes
      • 2025.03
      • 2024.03 (LTS)
      • 2023.03 (LTS)
      • 2025.03 (Servoy Cloud)
      • 2024.12
      • 2024.12 (Servoy Cloud)
      • 2024.09
      • 2024.09 (Servoy Cloud)
      • 2024.06
      • 2024.06 (Servoy Cloud)
      • 2024.03 (Servoy Cloud)
      • 2023.12
      • 2023.12 (Servoy Cloud)
      • 2023.09
      • 2023.09 (Servoy Cloud)
      • 2023.06
      • 2022.03 (LTS)
      • 2022.12 What's new
      • 2022.12
      • 2022.09 What's new
      • 2022.09
      • 2022.06 What's new
      • 2022.06
      • 2021.03.3 (LTS)
Powered by GitBook
On this page
  • Overview
  • Foundset Filters
  • Add Foundset Filters
  • Create Table Filter Parameter and Apply to Foundset
  • Set Multiple Foundset Filters at the Same Time
  • Remove a Named Foundset Filter
  • Table Filters
  • Add Table Filters
  • Create Table Filters
  • Set Multiple Table Filters at the Same Time
  • Remove a Named Table Filter
  • Filtering Methods
  • Logical Expression
  • QueryBuilder
  • Table of Operators
  • Naming Filters
  • Removing Filters
  • Examples of Filters using Logical Expressions
  • Foundset Filter Orders Between Two Dates Using Form Variables
  • Foundset Filter Orders Based on a List of Customers Using IN Operator with array
  • Foundset Filter Orders Based on a List of Customers Using IN Operator with custom queries
  • Foundset Filter Customers Where CompanyName Starts with “big” (Case-Insensitive)
  • Table Filter: Where company = or IN companies
  • Using the Data Broadcast Flag:
  • Examples of Filters using QueryBuilder
  • Foundset Filter Using QueryBuilder and Aggregates
  • Foundset Filter Using QueryBuilder and Functions
  • Foundset Filter - Orders with QueryBuilder where Ship City is [A OR B] AND NOT C
  • Best Practices
  • Additional Links

Was this helpful?

  1. GUIDES
  2. Develop
  3. Programming Guide
  4. Working with Data

Filtering

PreviousSQL StringNextEditing

Last updated 9 months ago

Was this helpful?

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.

var success = foundset.addFoundSetFilterParam('shipcity', '=', 'Berlin', 'cityFilter');
foundset.loadAllRecords(); // to make the filter effective

Here are the API methods related to foundset filters:

Add Foundset Filters

There are several ways to add a Foundset filer:

  • 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.

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
    query.or
        .add(query.columns.shipcity.eq('Amersfoort'))
        .add(query.columns.shipcity.eq('Amsterdam'))
);
var success = foundset.addFoundSetFilterParam(query, 'cityFilter');
if (success) {
    foundset.loadAllRecords(); // to make the filter effective
} else {
    application.output('Failed to add foundset filter');
}

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
    query.or
        .add(query.columns.shipcity.eq('Amersfoort'))
        .add(query.columns.shipcity.eq('Amsterdam'))
);
var success = foundset.addFoundSetFilterParam(query, 'cityFilter');
if (success) {
    foundset.loadAllRecords(); // to make the filter effective
} else {
    application.output('Failed to add foundset filter');
}

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.

var success = foundset.addFoundSetFilterParam('customerid', '=', 'BLONP');
if (success) {
    foundset.loadAllRecords(); // to make the filter effective
} else {
    application.output('Failed to add foundset filter');
}

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.

var success = foundset.addFoundSetFilterParam('customerid', '=', 'BLONP', 'custFilter');
if (success) {
    foundset.loadAllRecords(); // to make the filter effective
} else {
    application.output('Failed to add foundset filter');
}

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:

// Filter on messages table where messagesid > 10
var filter1 = foundset.createTableFilterParam('messagesid', '>', 10);

Create Filters with IN Conditions:

// Filter on product codes
var filter2 = foundset.createTableFilterParam('productcode', 'in', [120, 144, 200]);

// Use "sql:in" to interpret the value as a custom query
var filter3 = foundset.createTableFilterParam('countrycode', 'sql:in', 'select countrycode from countries where region = "Europe"');

Create Filters with Modifiers:

// Filter on companies where companyname is null or equals-ignore-case 'servoy'
var filter4 = foundset.createTableFilterParam('companyname', '#^||=', 'servoy');

// Filter where the value is null
var filter5 = foundset.createTableFilterParam('verified', '=', null);

Apply Multiple Filters to the Foundset:

// Apply multiple filters at the same time, previous filters with the same name are removed
var success = foundset.setTableFilters('myfilters', [filter1, filter2, filter3, filter4, filter5]);
if (success) {
    foundset.loadAllRecords(); // to make the filters effective
} else {
    application.output('Failed to apply table filters');
}

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:

// Create a number of filters
var filter1_10 = foundset.createTableFilterParam('customerid', '=', 10);

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipcity.eq('Amersfoort'));
var filter2 = foundset.createTableFilterParam(query);

// Apply multiple filters at the same time, previous filters with the same name are removed
var success = foundset.setFoundSetFilters('myfilters', [filter1_10, filter2]);
if (success) {
    foundset.loadAllRecords(); // to make the filters effective
} else {
    application.output('Failed to set foundset filters');
}

Update One of the Filters:

var filter1_11 = foundset.createTableFilterParam('customerid', '=', 11);

var success = foundset.setFoundSetFilters('myfilters', [filter1_11, filter2]);
if (success) {
    foundset.loadAllRecords(); // to make the filters effective
} else {
    application.output('Failed to update foundset filters');
}

Remove Filters by Setting Them to an Empty List:

var success = foundset.setFoundSetFilters('myfilters', []); // To remove the filters, an empty list is passed
if (success) {
    foundset.loadAllRecords(); // to make the removal effective
} else {
    application.output('Failed to remove foundset filters');
}

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:

var success = foundset.removeFoundSetFilterParam('custFilter'); // removes all filters with this name
if (success) {
    foundset.loadAllRecords(); // to make the removal effective
} else {
    application.output('Failed to remove foundset 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.

var success = databaseManager.addTableFilterParam('my_server', 'products', 'companyid', '=', globals.currentCompanyID);

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.

var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', globals.currentCompanyID)

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.

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
    query.or
        .add(query.columns.shipcity.eq('Amersfoort'))
        .add(query.columns.shipcity.eq('Amsterdam'))
);
var success = databaseManager.addTableFilterParam(query);
if (success) {
    application.output('Table filter added successfully');
} else {
    application.output('Failed to add table filter');
}

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.

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
    query.or
        .add(query.columns.shipcity.eq('Amersfoort'))
        .add(query.columns.shipcity.eq('Amsterdam'))
);
var success = databaseManager.addTableFilterParam(query, 'cityFilter');
if (success) {
    application.output('Table filter added successfully');
} else {
    application.output('Failed to add table filter');
}

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.

// some filters with in-conditions
var success = databaseManager.addTableFilterParam('datasources.db.example_data.products.getDataSource()', 'productid', 'in', [120, 144, 200]);
if (success) {
    application.output('Table filter added successfully');
} else {
    application.output('Failed to add table filter');
}

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.

// filter on messages table where messagesid>10, the filter has a name so it can be removed using databaseManager.removeTableFilterParam()
var success = databaseManager.addTableFilterParam('datasources.db.admin.messages.getDataSource()', 'messagesid', '>', 10, 'higNumberedMessagesRule')
if (success) {
    application.output('Table filter added successfully');
} else {
    application.output('Failed to add table filter');
}

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.

// Filter on product codes
var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200]);
if (success) {
    application.output('Table filter added successfully');
} else {
    application.output('Failed to add table filter');
}

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.

// Filter on companies where companyname is null or equals-ignore-case 'servoy'
var success = databaseManager.addTableFilterParam('crm', 'companies', 'companyname', '#^||=', 'servoy', 'companyNameFilter');
if (success) {
    application.output('Table filter added successfully');
} else {
    application.output('Failed to add table filter');
}

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:

var query = datasources.db.admin.messages.createSelect();
query.where.add(query.columns.messagesid.gt(10));
var filter = databaseManager.createTableFilterParam(query);

Apply Filter:

// Apply multiple filters at the same time, previous filters with the same name are removed
var success = databaseManager.setTableFilters('myfilters', [filter]);
if (success) {
    application.output('Table filters applied successfully');
} else {
    application.output('Failed to apply table filters');
}

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:

// Filter on messages table where messagesid > 10
var filter = databaseManager.createTableFilterParam('datasources.db.example_data.messages.getDataSource()', 'messagesid', '>', 10);

Apply Filter:

var success = databaseManager.setTableFilters('myfilters', [filter]);
if (success) {
    application.output('Table filters applied successfully');
} else {
    application.output('Failed to apply table filters');
}

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().

// Apply multiple filters at the same time, previous filters with the same name are removed
var filter1 = databaseManager.createTableFilterParam('crm', 'companies', 'companyname', '#^||=', 'servoy');
var filter2 = databaseManager.createTableFilterParam('crm', 'orders', 'countrycode', 'sql:in', 'select countrycode from countries where region = "Europe"');
var success = databaseManager.setTableFilters('myfilters', [filter1, filter2]);
if (success) {
    application.output('Table filters applied successfully');
} else {
    application.output('Failed to apply table filters');
}

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:

// Create a number of filters
var query1_nl = datasources.db.crm.companies.createSelect();
query1_nl.where.add(query1_nl.columns.countrycode.eq('nl'));
var filter1_nl = databaseManager.createTableFilterParam(query1_nl);

var filter2 = databaseManager.createTableFilterParam('example', 'orders', 'clusterid', '=', 10).dataBroadcast(true);

Apply Multiple Filters at the Same Time:

// Apply multiple filters at the same time, previous filters with the same name are removed
var success = databaseManager.setTableFilters('myfilters', [filter1_nl, filter2]);
if (success) {
    application.output('Table filters applied successfully');
} else {
    application.output('Failed to apply table filters');
}

Update One of the Filters:

var query1_us = datasources.db.crm.companies.createSelect();
query1_us.where.add(query1_us.columns.countrycode.eq('us'));
var filter1_us = databaseManager.createTableFilterParam(query1_us);

var success = databaseManager.setTableFilters('myfilters', [filter1_us, filter2]);
if (success) {
    application.output('Table filters updated successfully');
} else {
    application.output('Failed to update table filters');
}

Remove Filters by Setting Them to an Empty List:

var success = databaseManager.setTableFilters('myfilters', []);
if (success) {
    application.output('Table filters removed successfully');
} else {
    application.output('Failed to remove table filters');
}

Remove a Named Table Filter

In this example, we will remove a previously defined table filter using the removeTableFilterParam method.

var success = databaseManager.removeTableFilterParam('admin', 'highNumberedMessagesRule');
if (success) {
    application.output('Table filter removed successfully');
} else {
    application.output('Failed to remove table filter');
}

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:

Operator
Description

=

Only records whose column equals the specified value

<

Only records whose column is less than the specified value

>

Only records whose column greater than the specified value

>=

Only records whose column greater than or equals the specified value

<=

Only records whose column less than or equals the specified value

!=

Only records whose column does not equal the specified value

^

Only records whose column value is null

LIKE

Only records whose column matches using the SQL LIKE construct (use wildcard % characters)

IN

Only records whose column value is in (using the SQL IN construct) a list of values

BETWEEN

Only records whose column value is (inclusive) between a list of 2 values

#

Modifier, used to make case-insensitive queries

||

Modifier used to concatenate two conditions; a logical OR

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:

    var success = foundset.addFoundSetFilterParam('shipcity', '=', 'Berlin', 'cityFilter');
    foundset.loadAllRecords(); // to make param(s) effective
  • Table Filter Filter records in a products table based on the criteria that the status is not discontinued, named productfilter:

    var success = databaseManager.addTableFilterParam('xx_server','products','product_status','!=',globals.STATUS_DISCONTINUED,'productfilter');
    foundset.loadAllRecords(); // to make param(s) effective

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

    var success = foundset.removeFoundSetFilterParam('cityFilter');// removes all filters with this name
    foundset.loadAllRecords(); // to make param(s) effective
  • Table Filter

    var success = databaseManager.removeTableFilterParam('xx_server', 'productfilter'); // removes all table filters with this name
    foundset.loadAllRecords(); // to make param(s) effective

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:

// Setting the form variables for the date range
globals.startDate = '2023-01-01'; // Example start date
globals.endDate = '2023-12-31';   // Example end date

Add the Filter:

var success = foundset.addFoundSetFilterParam('orderdate', 'between', [globals.startDate, globals.endDate], 'dateFilter');
foundset.loadAllRecords(); // to make the filter effective

Remove the Filter (if needed):

var success = foundset.removeFoundSetFilterParam('dateFilter');
foundset.loadAllRecords(); // to make the removal effective

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:

globals.countries = ["France", "USA", "Netherlands"]; // Example list of shipping countries

Add the Filter:

var success = foundset.addFoundSetFilterParam('shipping_country', 'in', globals.countries, 'countryFilter');
foundset.loadAllRecords(); // to make the filter effective
  • a valuelist

Considering countries a valuelist containing country names: Define the countries list from the valuelist:

var filter_countries = application.getValueListItems('order_countries').getColumnAsArray(1);

Add the Filter:

var success = foundset.addFoundSetFilterParam('shipping_country', 'in', filter_countries, 'countryFilter');
foundset.loadAllRecords(); // to make the filter effective

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

success = foundset.addFoundSetFilterParam("shipping_country", "sql:in", "select shipping_country from orders where shipping_country in ('France', 'USA', 'Netherlands')");
foundset.loadAllRecords(); // to make param(s) effective

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:

var success = foundset.addFoundSetFilterParam('companyname', 'like', '#big%', 'companyFilter');
foundset.loadAllRecords(); // to make the filter effective

Remove the Filter (if needed):

var success = foundset.removeFoundSetFilterParam('companyFilter');
foundset.loadAllRecords(); // to make the removal effective

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:

function onSolutionOpen() {
    // Apply the filter to all tables that have the companyid column
    var success = databaseManager.addTableFilterParam('xx_server', null, 'companyid', 'IN', globals.companyIDs, 'companyFilter');
}

Remove the Table Filter (if needed):

function removeCompanyFilter() {
    var success = databaseManager.removeTableFilterParam('companyFilter');
}

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:

var filter = databaseManager.createTableFilterParam('example', 'orders', 'clusterid', '=', 10).dataBroadcast(true);
if (filter) {
    application.output('Table filter with dataBroadcast flag set successfully');
} else {
    application.output('Failed to create table filter with dataBroadcast 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:

var query = datasources.db.example_data.orders.createSelect();
var subquery = query.joins.add('db:/example_data/customers', JSRelation.INNER_JOIN, 'c');
subquery.on.add(query.columns.customerid.eq(query.joins.c.columns.customerid));
query.result.add(query.columns.customerid);
query.result.add(query.columns.customerid.count, 'order_count');
query.groupBy.add(query.columns.customerid);
query.having.add(query.columns.customerid.count.gt(10)); // Example: more than 10 orders

// Filter customers based on the subquery
var filter = foundset.createTableFilterParam(query);

Add the Filter:

var success = foundset.setFoundSetFilters('customerOrderFilter', [filter]);
foundset.loadAllRecords(); // to make the filter effective

Remove the Filter (if needed):

var success = foundset.removeFoundSetFilterParam('customerOrderFilter');
foundset.loadAllRecords(); // to make the removal effective

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:

var query = datasources.db.example_data.orders.createSelect();
var subquery = query.joins.add('db:/example_data/customers', JSRelation.INNER_JOIN, 'c');
subquery.on.add(query.columns.customerid.eq(query.joins.c.columns.customerid));
query.result.add(query.columns.customerid);
query.result.add(query.columns.orderamount.sum, 'total_order_amount');
query.groupBy.add(query.columns.customerid);
query.having.add(query.columns.orderamount.sum.gt(1000)); // Example: total order amount greater than 1000

// Filter customers based on the subquery
var filter = foundset.createTableFilterParam(query);

Add the Filter:

var success = foundset.setFoundSetFilters('orderAmountFilter', [filter]);
foundset.loadAllRecords(); // to make the filter effective

Remove the Filter (if needed):

var success = foundset.removeFoundSetFilterParam('orderAmountFilter');
foundset.loadAllRecords(); // to make the removal effective

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:

var query = datasources.db.example_data.orders.createSelect();
var orCondition = query.or
    .add(query.columns.shipcity.eq('A'))
    .add(query.columns.shipcity.eq('B'));
query.where.add(orCondition)
    .add(query.columns.shipcity.not.eq('C'));

Add the Filter:

var success = foundset.addFoundSetFilterParam(query, 'advancedCityFilter');
foundset.loadAllRecords(); // to make the filter effective

Remove the Filter (if needed):

var success = foundset.removeFoundSetFilterParam('advancedCityFilter');
foundset.loadAllRecords(); // to make the removal effective

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:

  • Searching Guide: QueryBuilder Guide

  • SvyPopupFilter: Combines Filters into a Reusable UI Pattern

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.

Reference for

Reference for

addFoundSetFilterParam(query)
addFoundSetFilterParam(query, name)
addFoundSetFilterParam(dataprovider, operator, value)
addFoundSetFilterParam(dataprovider, operator, value, name)
createTableFilterParam(dataprovider, operator, value)
setFoundSetFilters(filterName, tableFilters)
removeFoundSetFilterParam(name)
Foundset Filter
addTableFilterParam(query)
addTableFilterParam(query, filterName)
addTableFilterParam(datasource, dataprovider, operator, value)
addTableFilterParam(datasource, dataprovider, operator, value, filterName)
addTableFilterParam(serverName, tableName, dataprovider, operator, value)
addTableFilterParam(serverName, tableName, dataprovider, operator, value, filterName)
createTableFilterParam(query)
createTableFilterParam(datasource, dataprovider, operator, value)
createTableFilterParam(serverName, tableName, dataprovider, operator, value)
setTableFilters(filterName, tableFilters)
removeTableFilterParam(serverName, filterName)
Table Filter