How to Filter Data in a Retool Table Component

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.

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.

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.

1-- PostgreSQL example with ILIKE:
2SELECT id, first_name, last_name, email, status, created_at
3FROM users
4WHERE
5  ({{ searchInput.value }} = '' OR
6   first_name ILIKE {{ '%' + searchInput.value + '%' }} OR
7   last_name ILIKE {{ '%' + searchInput.value + '%' }} OR
8   email ILIKE {{ '%' + searchInput.value + '%' }})
9ORDER BY created_at DESC
10LIMIT 500;
11
12-- MySQL equivalent:
13SELECT id, first_name, last_name, email, status, created_at
14FROM users
15WHERE
16  ({{ searchInput.value }} = '' OR
17   LOWER(first_name) LIKE LOWER({{ '%' + searchInput.value + '%' }}) OR
18   LOWER(email) LIKE LOWER({{ '%' + searchInput.value + '%' }}))
19ORDER BY created_at DESC
20LIMIT 500;

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

1-- Combined text search + status dropdown filter:
2SELECT id, first_name, last_name, email, status, created_at
3FROM users
4WHERE
5  ({{ searchInput.value }} = '' OR
6   first_name ILIKE {{ '%' + searchInput.value + '%' }} OR
7   email ILIKE {{ '%' + searchInput.value + '%' }})
8  AND
9  ({{ statusFilter.value }} = '' OR status = {{ statusFilter.value }})
10ORDER BY created_at DESC
11LIMIT 500;

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.

1-- Adding date range to the combined filter:
2SELECT id, first_name, last_name, email, status, created_at
3FROM users
4WHERE
5  ({{ searchInput.value }} = '' OR
6   first_name ILIKE {{ '%' + searchInput.value + '%' }} OR
7   email ILIKE {{ '%' + searchInput.value + '%' }})
8  AND
9  ({{ statusFilter.value }} = '' OR status = {{ statusFilter.value }})
10  AND
11  ({{ !dateRangeFilter.value.start }} OR
12   created_at >= {{ dateRangeFilter.value.start }}::date)
13  AND
14  ({{ !dateRangeFilter.value.end }} OR
15   created_at <= {{ dateRangeFilter.value.end }}::date + INTERVAL '1 day')
16ORDER BY created_at DESC;

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

1// Transformer: filteredUsers
2// Dependencies: query1.data, searchInput.value, statusFilter.value
3
4const rows = query1.data;
5const search = searchInput.value?.toLowerCase() ?? '';
6const status = statusFilter.value ?? '';
7
8return rows.filter(row => {
9  const matchesSearch = !search ||
10    row.first_name?.toLowerCase().includes(search) ||
11    row.last_name?.toLowerCase().includes(search) ||
12    row.email?.toLowerCase().includes(search);
13
14  const matchesStatus = !status || row.status === status;
15
16  return matchesSearch && matchesStatus;
17});

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.

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

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' }}

1// Transformer: activeFilterCount
2let count = 0;
3if (searchInput.value) count++;
4if (statusFilter.value) count++;
5if (dateRangeFilter.value?.start) count++;
6return count;