# How to Filter Data in a Retool Table Component

- Tool: Retool
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

To filter data in a Retool table, enable the built-in filter bar in the Table Inspector, use SQL WHERE clauses with {{ }} parameters for server-side filtering, or filter the query result in a JavaScript transformer. For dynamic user-driven filters, bind a Text Input component to your SQL query's WHERE clause and re-run the query on each change.

## Three Ways to Filter Table Data in Retool

Retool gives you three distinct approaches to filtering table data, each suited to a different situation. The built-in filter bar is quickest for power users who want to filter ad hoc without any configuration. SQL WHERE clauses are best for server-side filtering on large datasets where loading all rows client-side is impractical. JavaScript transformer filtering is best when you already have data loaded and want lightweight client-side filtering without re-querying the database.

This tutorial walks through all three methods and shows how to combine them — for example, a SQL query with an ILIKE clause driven by a Text Input component, plus a transformer for secondary client-side filtering. Understanding the difference between client-side and server-side filtering is critical when working with large datasets.

## Before you start

- A Retool app with a Table component connected to a SQL or REST query
- The query returns a meaningful dataset (ideally 10+ rows to see filtering in action)
- Familiarity with Retool's {{ }} expression syntax
- For server-side filtering: access to a SQL resource (PostgreSQL, MySQL, etc.)

## Step-by-step guide

### 1. Enable the Built-in Filter Bar

Select the Table component on your canvas. In the Inspector panel, scroll to the Interaction section and toggle 'Allow filtering' to on. A filter icon appears in the table header. Users can click it to add filter rules per column, selecting operators like equals, contains, greater than, etc. This is entirely client-side — it filters the already-loaded data in the browser without re-querying.

**Expected result:** A filter icon appears in the table header, and clicking it reveals per-column filter controls.

### 2. Add a Text Input for Dynamic SQL Filtering

Drag a Text Input component onto your canvas from the component panel. Name it 'searchInput' (you can rename it in the Inspector's General section under 'Component name'). This component's value will drive your SQL WHERE clause. Set the placeholder text to something like 'Search by name...' in the Inspector's General section.

**Expected result:** A searchInput text field appears on the canvas ready to receive user input.

### 3. Write a Dynamic SQL Query with ILIKE

Open the query editor (bottom panel) and write a SQL query that uses {{ searchInput.value }} in the WHERE clause. Use ILIKE for case-insensitive partial matching on PostgreSQL, or LIKE with LOWER() on MySQL. Retool safely parameterizes {{ }} values to prevent SQL injection.

Enable 'Run when inputs change' in the query's Advanced settings so the query re-runs automatically when the user types.

```
-- PostgreSQL example with ILIKE:
SELECT id, first_name, last_name, email, status, created_at
FROM users
WHERE
  ({{ searchInput.value }} = '' OR
   first_name ILIKE {{ '%' + searchInput.value + '%' }} OR
   last_name ILIKE {{ '%' + searchInput.value + '%' }} OR
   email ILIKE {{ '%' + searchInput.value + '%' }})
ORDER BY created_at DESC
LIMIT 500;

-- MySQL equivalent:
SELECT id, first_name, last_name, email, status, created_at
FROM users
WHERE
  ({{ searchInput.value }} = '' OR
   LOWER(first_name) LIKE LOWER({{ '%' + searchInput.value + '%' }}) OR
   LOWER(email) LIKE LOWER({{ '%' + searchInput.value + '%' }}))
ORDER BY created_at DESC
LIMIT 500;
```

**Expected result:** The table re-queries and filters whenever the user types in searchInput.

### 4. Add a Dropdown Filter for Category/Status

For enumerated fields like status, a Select component is more user-friendly than a text input. Drag a Select component onto the canvas, name it 'statusFilter', and configure its options statically: [{ label: 'All', value: '' }, { label: 'Active', value: 'active' }, { label: 'Inactive', value: 'inactive' }]. Then add a condition to your SQL query that uses {{ statusFilter.value }}.

```
-- Combined text search + status dropdown filter:
SELECT id, first_name, last_name, email, status, created_at
FROM users
WHERE
  ({{ searchInput.value }} = '' OR
   first_name ILIKE {{ '%' + searchInput.value + '%' }} OR
   email ILIKE {{ '%' + searchInput.value + '%' }})
  AND
  ({{ statusFilter.value }} = '' OR status = {{ statusFilter.value }})
ORDER BY created_at DESC
LIMIT 500;
```

**Expected result:** Users can now filter by both a text search and a status dropdown simultaneously.

### 5. Add a Date Range Filter

For date-based filtering, use a Date Range Picker component (name it 'dateRangeFilter'). Add BETWEEN conditions to your SQL query using dateRangeFilter.value.start and dateRangeFilter.value.end. Always cast date strings to proper timestamps in SQL to avoid comparison issues.

```
-- Adding date range to the combined filter:
SELECT id, first_name, last_name, email, status, created_at
FROM users
WHERE
  ({{ searchInput.value }} = '' OR
   first_name ILIKE {{ '%' + searchInput.value + '%' }} OR
   email ILIKE {{ '%' + searchInput.value + '%' }})
  AND
  ({{ statusFilter.value }} = '' OR status = {{ statusFilter.value }})
  AND
  ({{ !dateRangeFilter.value.start }} OR
   created_at >= {{ dateRangeFilter.value.start }}::date)
  AND
  ({{ !dateRangeFilter.value.end }} OR
   created_at <= {{ dateRangeFilter.value.end }}::date + INTERVAL '1 day')
ORDER BY created_at DESC;
```

**Expected result:** Users can filter by date range in addition to text search and status.

### 6. Filter Client-Side Using a JavaScript Transformer

When you already have data loaded and do not want to re-query the database, use a standalone transformer (Code tab → + Add → Transformer) to filter the data. The transformer auto-executes whenever its dependencies change. Important: transformers are read-only — they can only return values, not trigger queries or set state.

Create a transformer named 'filteredUsers' and bind the Table's Data source to {{ filteredUsers.value }}.

```
// Transformer: filteredUsers
// Dependencies: query1.data, searchInput.value, statusFilter.value

const rows = query1.data;
const search = searchInput.value?.toLowerCase() ?? '';
const status = statusFilter.value ?? '';

return rows.filter(row => {
  const matchesSearch = !search ||
    row.first_name?.toLowerCase().includes(search) ||
    row.last_name?.toLowerCase().includes(search) ||
    row.email?.toLowerCase().includes(search);

  const matchesStatus = !status || row.status === status;

  return matchesSearch && matchesStatus;
});
```

**Expected result:** The table immediately updates as the user types or selects a filter, with no database round-trip.

### 7. Add a 'Clear Filters' Button

Add a Button component labeled 'Clear Filters'. In its event handler (Inspector → Interaction → Event handlers), add multiple 'Set component value' actions: set searchInput to empty string, set statusFilter to empty string, and set dateRangeFilter to null. You can chain these actions by clicking '+ Add action' under the same event handler.

```
// If using a JS Query for the clear action instead of multiple event handler actions:
await searchInput.setValue('');
await statusFilter.setValue('');
await dateRangeFilter.setValue(null);
// Note: setValue() is async — do not read the updated value immediately after.
```

**Expected result:** Clicking the button resets all filter controls and the table returns to showing all rows.

### 8. Show the Active Filter Count as a Badge

Add a Text component near your filters to display how many filters are active. Bind it to a transformer that counts non-empty filter values. This gives users clarity on whether filters are applied.

Text component value: {{ activeFilterCount.value + ' filter(s) active' }}

```
// Transformer: activeFilterCount
let count = 0;
if (searchInput.value) count++;
if (statusFilter.value) count++;
if (dateRangeFilter.value?.start) count++;
return count;
```

**Expected result:** A text label near the filters shows '2 filter(s) active' when the user has applied filters.

## Complete code example

File: `SQL Query: getUsersFiltered + Transformer: filteredUsers`

```javascript
-- SQL Query: getUsersFiltered
-- Enable: Run when inputs change
SELECT
  id,
  first_name,
  last_name,
  email,
  status,
  created_at
FROM users
WHERE
  (
    {{ searchInput.value }} = ''
    OR first_name ILIKE {{ '%' + searchInput.value + '%' }}
    OR last_name  ILIKE {{ '%' + searchInput.value + '%' }}
    OR email      ILIKE {{ '%' + searchInput.value + '%' }}
  )
  AND ({{ statusFilter.value }} = '' OR status = {{ statusFilter.value }})
ORDER BY created_at DESC
LIMIT 1000;

// -----------------------------------------------
// JS Transformer: filteredUsers (client-side fallback)
// Table Data source: {{ filteredUsers.value }}
const rows = getUsersFiltered.data;
const search = searchInput.value?.toLowerCase() ?? '';
const status = statusFilter.value ?? '';

return rows.filter(row => {
  const matchesSearch = !search ||
    row.first_name?.toLowerCase().includes(search) ||
    row.last_name?.toLowerCase().includes(search) ||
    row.email?.toLowerCase().includes(search);
  const matchesStatus = !status || row.status === status;
  return matchesSearch && matchesStatus;
});
```

## Common mistakes

- **Filtering causes zero results when the search input is empty** — undefined Fix: Add an OR condition: ({{ searchInput.value }} = '' OR field ILIKE ...). Without this guard, an empty string passed to ILIKE matches nothing.
- **Using a transformer to filter but forgetting it is read-only** — undefined Fix: Transformers cannot call query.trigger() or use setState(). If your filter logic needs to fire a database query, put the logic in a JS Query and trigger it from an event handler.
- **Not enabling 'Run when inputs change' on the SQL query** — undefined Fix: Go to the query's Advanced settings and enable 'Run when inputs change'. Without this, the query only runs once on page load and does not respond to user input changes.
- **Using client-side transformer filtering for a table with 50,000+ rows** — undefined Fix: Client-side filtering loads all rows into the browser first, which is slow and expensive. Use SQL WHERE clauses for server-side filtering on large datasets.

## Best practices

- Always add a fallback condition like {{ searchInput.value }} = '' so that an empty filter returns all rows rather than zero rows.
- Use ILIKE (PostgreSQL) or LOWER/LIKE (MySQL) for case-insensitive text search — never filter on exact case unless intentional.
- Enable 'Run when inputs change' on your query rather than requiring users to manually click a Search button for a smoother UX.
- Add LIMIT to filtered SQL queries — without a limit, a broad search on a large table can time out or cause performance issues.
- For large datasets (10,000+ rows), always use server-side SQL filtering instead of client-side transformer filtering to avoid loading all data into the browser.
- Transformers are read-only — if your filtering logic needs to trigger a query or update state, use a JS Query instead of a transformer.
- Debounce text input filters with the 'Debounce (ms)' setting in the Text Input's Interaction section to reduce the number of SQL queries fired while the user is typing.

## Frequently asked questions

### Does the Retool table built-in filter bar work with server-side data?

The built-in filter bar only filters data that is already loaded in the browser — it does not re-query the server. If your table has server-side pagination and only shows page 1, the filter bar only filters those rows. For full-dataset filtering, use SQL WHERE clauses driven by input components with 'Run when inputs change' enabled.

### How do I prevent SQL injection when using {{ }} in WHERE clauses?

Retool automatically parameterizes values inside {{ }} expressions in SQL queries, treating them as bound parameters rather than raw SQL. This prevents SQL injection. However, avoid wrapping {{ }} values in string concatenation on the SQL side — keep them as separate parameters.

### Can I filter a Retool table by multiple columns at once?

Yes. In SQL queries, add multiple AND conditions for each filter control. In transformer-based filtering, chain .filter() conditions or use logical AND inside a single .filter() callback. The built-in filter bar also supports multi-column filter rules via its Add Filter UI.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-filter-data-in-a-retool-table-component
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-filter-data-in-a-retool-table-component