# Dynamic Filtering Patterns in OutSystems Aggregates

- Tool: OutSystems
- Difficulty: Intermediate
- Time required: 30-40 min
- Compatibility: OutSystems 11 and ODC
- Last updated: March 2026

## TL;DR

Add filters in the Aggregate's Filters tab using OutSystems expression syntax. For dynamic filters driven by user input, bind filter conditions to screen variables. Use the pattern `FilterVar = "" OR Entity.Attribute = FilterVar` to make filters optional — when the user leaves a field empty, all rows pass through.

## Filtering vs Joining vs Grouping

Filters narrow WHICH rows an Aggregate returns. Joins determine HOW entities are combined. Group By summarizes rows into aggregate statistics. These are three distinct operations and this tutorial covers only filters.

OutSystems Aggregate filters are equivalent to SQL WHERE clauses. They use OutSystems expression syntax — not JavaScript, not SQL. Multiple filter conditions in the Filters tab are ANDed together automatically. For OR conditions, write a single expression using the OutSystems `or` operator.

## Before you start

- An Aggregate already created with at least one entity source (see outsystems-aggregate-tutorial)
- A screen with at least one Input widget for user search input
- Screen-level local variables to hold filter values

## Step-by-step guide

### 1. Add a static filter condition in the Filters tab

Open your Aggregate → click the Filters tab (second tab, after Sources).

Click 'Add filter' (the + icon). The Expression editor opens.

For a static filter — always filtering to active employees:
`Employee.IsActive = True`

Click OK. The filter appears in the Filters list with a checkbox (enabled by default).

For a static date filter:
`Employee.HireDate >= #2020-01-01#`

Date literals in OutSystems use #yyyy-MM-dd# format. DateTime literals: #2020-01-01 00:00:00#.

Multiple filters in the list are AND'd: the query returns rows matching ALL conditions simultaneously.

**Expected result:** Filters tab shows 'Employee.IsActive = True' as the first filter condition. Preview pane immediately updates to show only active employees.

### 2. Create local variables to hold dynamic filter values

Dynamic filters read their values from screen variables at runtime.

In Service Studio, open your screen → right-click the screen name in the Interface tab → Add Local Variable. Name it 'SearchName' with Data Type = Text, Default Value = "".

Add more variables as needed:
- 'FilterDepartmentId' (Department Identifier, Default = NullIdentifier())
- 'FilterIsActive' (Boolean, Default = True)
- 'FilterHireDateFrom' (Date, Default = NullDate())
- 'FilterHireDateTo' (Date, Default = NullDate())

These variables are what your Input widgets will bind to, and what your Aggregate filters will read from.

**Expected result:** Screen has local variables SearchName (Text), FilterDepartmentId (Department Identifier), FilterIsActive (Boolean), FilterHireDateFrom (Date), FilterHireDateTo (Date) visible in the Interface tab tree.

### 3. Add a dynamic text search filter with the optional pattern

In the Aggregate's Filters tab, click Add filter. Write this expression:

`SearchName = "" or Index(ToLower(Employee.FirstName + " " + Employee.LastName), ToLower(SearchName), 0) >= 0`

This is the optional filter pattern:
- When SearchName = "" (empty): the first condition is true, so ALL rows pass through
- When SearchName has a value: Index() searches for the string within the full name (case-insensitive)

The Index() function returns the position of the search string (-1 if not found, >= 0 if found).

Alternative using Contains-style pattern:
`SearchName = "" or Employee.LastName like "%" + SearchName + "%"`

Note: the 'like' operator is available in OutSystems expressions for Aggregate filters. Index() is preferred for case-insensitive search.

```
/* Dynamic text search — optional filter pattern */

/* Search by full name (case-insensitive) */
SearchName = "" or Index(ToLower(Employee.FirstName + " " + Employee.LastName), ToLower(SearchName), 0) >= 0

/* Search by last name only */
SearchName = "" or Index(ToLower(Employee.LastName), ToLower(SearchName), 0) >= 0

/* Email contains search (partial match) */
SearchEmail = "" or Index(ToLower(Employee.Email), ToLower(SearchEmail), 0) >= 0

/* Exact match (faster — uses index) */
SearchName = "" or Employee.LastName = SearchName
```

**Expected result:** Filters tab shows the dynamic text search expression. With SearchName = "", preview shows all employees. Type a name in SearchName (preview uses current variable values) to see filtering.

### 4. Add optional dropdown and date range filters

Add a second filter for Department:
`FilterDepartmentId = NullIdentifier() or Employee.DepartmentId = FilterDepartmentId`

When FilterDepartmentId = NullIdentifier() (default), all departments pass. When a specific department is selected, only that department's employees appear.

Add date range filters (two separate filter conditions):
`FilterHireDateFrom = NullDate() or Employee.HireDate >= FilterHireDateFrom`
`FilterHireDateTo = NullDate() or Employee.HireDate <= FilterHireDateTo`

All three filters work together: the Aggregate returns employees matching ALL active filter conditions (text search AND department AND date range).

In the screen, bind Input widgets:
- Text Input → Variable: SearchName → OnChange: Client Action to Refresh Data
- Dropdown → Variable: FilterDepartmentId → OnChange: Refresh Aggregate
- DatePicker → Variable: FilterHireDateFrom → OnChange: Refresh Aggregate

```
/* Multiple optional filter conditions in Filters tab */
/* All conditions are AND'd automatically */

/* Condition 1: Text search */
SearchName = "" or Index(ToLower(Employee.FirstName + " " + Employee.LastName), ToLower(SearchName), 0) >= 0

/* Condition 2: Department dropdown */
FilterDepartmentId = NullIdentifier() or Employee.DepartmentId = FilterDepartmentId

/* Condition 3: Hire date from */
FilterHireDateFrom = NullDate() or Employee.HireDate >= FilterHireDateFrom

/* Condition 4: Hire date to */
FilterHireDateTo = NullDate() or Employee.HireDate <= FilterHireDateTo

/* Condition 5: Active status (always applied — not optional) */
Employee.IsActive = FilterIsActive
```

**Expected result:** Filters tab shows five filter conditions. With all variables at default values, all active employees appear. Selecting a department filters the list. Setting date ranges further narrows results.

### 5. Trigger Aggregate refresh on user input

Filters in the Aggregate read variable values at the time the Aggregate runs. Changing a variable does NOT automatically re-run the Aggregate — you must trigger a refresh.

For a search-as-you-type pattern:
1. Select the Search Input widget on screen
2. In Properties → Events → OnChange → click the lightning bolt → New Client Action
3. Name it 'OnSearchChange'
4. In the action flow: Start → Refresh Data (drag from Toolbox, select GetEmployees) → End

For a 'Search' button click:
1. Button → OnClick → New Client Action
2. Action flow: Start → Assign StartIndex = 0 (reset pagination) → Refresh Data: GetEmployees → End

For multiple filters triggering at once, one Refresh Data node is sufficient — OutSystems re-runs the entire Aggregate with all current variable values.

```
/* Client Action: OnSearchChange */
/* Start
   → Refresh Data: GetEmployees
   → End */

/* Client Action: OnFilterApply (button click) */
/* Start
   → Assign: StartIndex = 0          (reset to page 1)
   → Refresh Data: GetEmployees
   → End */

/* Client Action: OnClearFilters */
/* Start
   → Assign: SearchName = ""         (clear text)
   → Assign: FilterDepartmentId = NullIdentifier()
   → Assign: FilterHireDateFrom = NullDate()
   → Assign: FilterHireDateTo = NullDate()
   → Refresh Data: GetEmployees
   → End */
```

**Expected result:** Typing in the search box immediately updates the list (search-as-you-type). Selecting a department from the dropdown re-filters the list. Clicking 'Clear Filters' resets all variables and refreshes.

### 6. Write an OR filter expression for multi-field search

To search across multiple fields simultaneously (e.g., name OR email), combine conditions within a single filter expression using the `or` keyword:

`SearchName = "" or Index(ToLower(Employee.FirstName + " " + Employee.LastName), ToLower(SearchName), 0) >= 0 or Index(ToLower(Employee.Email), ToLower(SearchName), 0) >= 0`

This is a single filter condition (one entry in the Filters tab) using multiple `or` sub-conditions — NOT three separate filter entries (which would AND them).

For complex OR logic spanning multiple entities (e.g., search employee name OR department name), keep it as one expression:
`SearchName = "" or Index(ToLower(Employee.LastName), ToLower(SearchName), 0) >= 0 or Index(ToLower(Department.Name), ToLower(SearchName), 0) >= 0`

```
/* Single filter expression with OR conditions */
/* Multi-field search: matches name OR email OR department */

SearchName = ""
or Index(ToLower(Employee.FirstName + " " + Employee.LastName), ToLower(SearchName), 0) >= 0
or Index(ToLower(Employee.Email), ToLower(SearchName), 0) >= 0
or Index(ToLower(Department.Name), ToLower(SearchName), 0) >= 0

/* Key rule:
   Multiple filter ENTRIES in the Filters tab = AND logic
   Multiple conditions in ONE filter entry = you control AND/OR with operators */
```

**Expected result:** A single search input searches across first name, last name, email, and department name simultaneously. TrueChange shows no errors for the OR filter expression.

## Complete code example

File: `EmployeeSearch_FilterReference.outsystems`

```outsystems
/* =====================================================
   Screen: EmployeeSearch
   Aggregate: GetFilteredEmployees
   Local Variables (screen-level):
   ===================================================== */

SearchName        Text                   Default: ""
FilterDeptId      Department Identifier  Default: NullIdentifier()
FilterStatus      Boolean                Default: True
FilterHireFrom    Date                   Default: NullDate()
FilterHireTo      Date                   Default: NullDate()
StartIndex        Integer                Default: 0
MaxRecords        Integer                Default: 20

/* =====================================================
   Aggregate: GetFilteredEmployees — Filters tab
   ===================================================== */

/* Filter 1: Text search (name or email) */
SearchName = ""
or Index(ToLower(Employee.FirstName + " " + Employee.LastName), ToLower(SearchName), 0) >= 0
or Index(ToLower(Employee.Email), ToLower(SearchName), 0) >= 0

/* Filter 2: Department (optional dropdown) */
FilterDeptId = NullIdentifier() or Employee.DepartmentId = FilterDeptId

/* Filter 3: Hire date from (optional) */
FilterHireFrom = NullDate() or Employee.HireDate >= FilterHireFrom

/* Filter 4: Hire date to (optional) */
FilterHireTo = NullDate() or Employee.HireDate <= FilterHireTo

/* Filter 5: Active status (always applied) */
Employee.IsActive = FilterStatus

/* Aggregate properties */
Max Records:  MaxRecords   /* bound to variable */
Start Index:  StartIndex   /* bound to variable */

/* =====================================================
   Client Action: OnSearchChange (Input OnChange event)
   ===================================================== */
/* Start → Assign StartIndex = 0 → Refresh Data: GetFilteredEmployees → End */

/* =====================================================
   Client Action: ClearFilters (Clear button OnClick)
   ===================================================== */
/* Start
   → Assign SearchName = ""
   → Assign FilterDeptId = NullIdentifier()
   → Assign FilterHireFrom = NullDate()
   → Assign FilterHireTo = NullDate()
   → Assign StartIndex = 0
   → Refresh Data: GetFilteredEmployees
   → End */

/* =====================================================
   Screen expression: result count label
   ===================================================== */
"Found " + IntegerToText(GetFilteredEmployees.Count) + " employees"
```

## Common mistakes

- **Adding multiple filter conditions in the Filters tab expecting OR logic** — undefined Fix: Multiple filter entries in the Filters tab are always AND'd together. For OR conditions across multiple fields, write a single filter entry with the `or` keyword inside one expression. Example: `SearchName = "" or Employee.LastName = SearchName or Employee.Email = SearchName` — this is one filter entry, not three.
- **Changing a filter variable but not refreshing the Aggregate** — undefined Fix: Variable changes do not automatically re-run the Aggregate. You must add a Refresh Data node in a Client Action triggered by user input (OnChange event, button click). Wire every filter input widget's OnChange event to a Client Action containing Refresh Data: YourAggregate.
- **Using a filter without the optional pattern, breaking the 'show all' state** — undefined Fix: A filter like `Employee.DepartmentId = FilterDeptId` with FilterDeptId = NullIdentifier() returns zero results (no employee has a NullIdentifier department). Always wrap optional filters: `FilterDeptId = NullIdentifier() or Employee.DepartmentId = FilterDeptId`. This way, when no department is selected, all employees appear.
- **Performing text search on a joined entity column without checking the join type** — undefined Fix: If you add a text filter on Department.Name but the join is With or Without (LEFT JOIN), employees without a department will have Department.Name = "" — a text search like `SearchName = "" or Index(Department.Name, SearchName, 0) >= 0` may still return them because Department.Name = "" makes the Index() return -1 (not >= 0). Make the department join Only With if you want to exclude employees without departments from text searches.

## Best practices

- Always use the optional filter pattern (`FilterVar = NullValue() or Condition`) — this avoids writing separate Aggregates for 'filtered' and 'unfiltered' states. One Aggregate handles both.
- Reset StartIndex to 0 whenever a filter changes — if a user is on page 3 and changes the search term, they should jump back to page 1 of the new results.
- Put the most restrictive, indexed filters first in the Filters tab — OutSystems passes filters to SQL in the order they appear. Filters on indexed columns (like IsActive or DepartmentId) are faster than full-text searches and should come first.
- Use NullDate() and NullIdentifier() as default values for optional filter variables — never use 0 or empty strings for non-text types, as these values may match real data.
- For high-traffic search screens, prefer exact-match and starts-with patterns over contains (Index()) patterns. Index()-based contains searches do not use database indexes and require full table scans.
- Add a result count label ('Found X employees') using GetFilteredEmployees.Count — this gives users feedback that their filter is working and helps them refine their search.

## Frequently asked questions

### How do I add an OR condition between two filter entries in the Aggregate Filters tab?

You cannot add OR logic between separate filter entries — separate entries are always AND'd. To use OR logic, write a single filter entry containing both conditions joined with the `or` keyword: `Employee.LastName = SearchName or Employee.Email = SearchName`. This is one filter entry (one row in the Filters tab list) that uses internal OR logic.

### Can I disable a filter condition dynamically without removing it from the Filters tab?

Yes — use the optional filter pattern. Instead of removing the filter, write it as: `FilterVar = NullValue() or Entity.Attribute = FilterVar`. When FilterVar equals its default null value (NullIdentifier(), NullDate(), "", etc.), the entire condition evaluates to True and all rows pass through. This effectively disables the filter at runtime without any code changes.

### Why does my search filter work in preview but not on the deployed screen?

The Aggregate preview in Service Studio uses the variable's default value (usually empty). When deployed, the Aggregate reads the variable's actual runtime value. If the filter works in preview (empty SearchName = all results) but returns empty on screen, check that (1) the Refresh Data action is being triggered after the variable is set, and (2) the variable is properly bound to the Input widget's Variable property rather than just its Text property.

### How do I implement HAVING-style filtering to filter by aggregate results (e.g., departments with more than 5 employees)?

OutSystems Aggregate filters correspond to SQL WHERE clauses, which run before aggregation — you cannot filter by aggregate results (COUNT, SUM) using the Filters tab. For HAVING-equivalent logic, use Advanced SQL with a HAVING clause (see outsystems-aggregate-vs-sql), or filter the grouped result list in a For Each loop after fetching the data, building a new filtered list using a local variable of list type.

---

Source: https://www.rapidevelopers.com/outsystems-tutorial/outsystems-aggregate-filters
© RapidDev — https://www.rapidevelopers.com/outsystems-tutorial/outsystems-aggregate-filters