Filtering table in retools frontend using search bar

Hello developers of retool, I have a pretty insightful question for any takers. I am making an application that uses a snowflake database as the backend data source and I am trying to leverage the retool frontend to filter my table whether then the snowflake table searching as input changes.

I know that common practice in retool with searchbars is a simple query like this:

select * from table_A where column_a like {{'%' + searchBar.value +'%'}}

My issue that I am noticing is that as you type in the searchbar, you are constantly making your snowflake query run as the input changes. I am working with a large dataset in my app (5000+ records) so this common approach severely slows down my app and creates lag. What I noticed using other filtering approaches (select/multiselect components) is that filtering the table directly from the frontend using the filter stack is better than using a where clause based filter and having the snowflake query constantly run.

  • Goal: My goal is to have a searchbar that will directly filter the table as you type without using the common approach of placing a where clause in the sql query. I want my datasource to pull all the data but the table filters on the front-end using the filter stack.

  • Steps: I have been playing around with the event handlers of the search bar but I am a bit unfamiliar with running javascript of this nature and manipulating the filter stack, so I need some help there.

To get an idea of the finished product I built this container that holds many different filters (mainly multiselect components) but # search, address, and zip code are the only filters that will use a search bar. Right now I have the search bars in a where clause in my sql query that populates the table I am using but I want to remove that use the search bar within the filter stack.

This is my current attempt of doing performing this myself.

current JS:
I used both the label (what is in photo) as well as the ID of the column and neither worked

current event handler:
This is the event handler for searchBar2, which is supposed to run the JS code as input changes

This currently does not work and I would highly appreciate suggestions that can lead to my desired output.

Progress Update:
fixed the JS since it was wrong and I have the table filtering correctly now

Updated JS:
table8.setFilter({columnId: "STORE_ID", value: searchBar2.value, operator: "=", id: "store_search_filter"})

1 Like

In this post can some point me to a post link from the forum that better explains the filter types for a table that comes from the documentation Display and edit data with the Table component | Retool Docs

There are less obvious filter choices like includes and isOneOf which I tried using for my search bar but does not work, so I am trying to better understand the more niche choices and its use cases.

Hi @jewis749, here are a couple:

Table Filter tooltip and operators table

How to use setFilterStack to filter values in an array

But feel free to ask any questions! :slightly_smiling_face:

Hi @jewis749,

I think for the searchbar what you're looking for is this option in your table settings:


This will search within all your columns. Just add a text input component and reference it there, so no JS needed =)

Hi @MiguelOrtiz,

So I know about the search term and the thing about that is that I find it to be good if you use one search bar to do everything. In my case I have 3 and I want each searchbar to be independent of its column, so I need one searchbar for one column.

Using retool's filter operators I want to perform something synonymous to this sql statement:

where column_name = '%' + {{searchbar.value}} + '%'

So that on the frontend of retool, as I type it will filter the records accordingly.

I answered my own question. I need to use the contains operator instead of '=' to achieve that behavior of trying to match the string to the data.

1 Like

Solution JS to achieve string based filtering:

Updated JS:
table8.setFilter({columnId: "STORE_ID", value: searchBar2.value, operator: "contains", id: "store_search_filter"})