In my role as a technical account manager at Retool, I help engineering teams navigate the complexities of building efficiency-driven internal applications. Lots of these teams want to build data-exploration tools, but they often face challenges in setting up the right UI and UX for hierarchical data filtering. Retool makes this problem easier to tackle thanks to our intuitive interface and the freedom to use JavaScript wherever needed.
While our Table component features powerful filtering capabilities right out of the box, introducing cascading filters can initially seem tricky. It’s not! In this guide, I’ll shed some light on this process. My goal is to guide you through setting up cascading filters step by step, so you'll be well-equipped to make dynamic data exploration more manageable for your engineers.
Let's explore how to implement cascading data filtering with Retool.
In general, cascading filtering is a technique that allows you to narrow down data based on layers of selection criteria.
In Retool, it’s no different. In practice, you can combine several native Retool components, such as the Select dropdown and the Table component, to create this dynamic filtering experience. However, while dropdown filters in Retool do not natively update, you can easily implement this additional functionality in JavaScript. That is what we are going to look at now.
With cascading filters in Retool, users can progressively narrow down their data views by selecting values in a hierarchical manner. For example, say your database's custom form collects information about a company's employees' locations. With cascading filters, selecting a country can dynamically update the available options for selecting states, which in turn updates the available options for selecting cities. This hierarchical filtering allows users to efficiently navigate and explore large datasets, focusing on relevant subsets of data for analysis and decision-making.
Check out a version of the Retool application that we’ll build in this guide so you can explore it and get to understand how cascading filters in Retool work. For example, if you select the country "USA," you’ll notice that the State filter updates to only states in the USA. If you select both the USA and France, for example, the states list includes those in both countries.
The structure of the data is important when implementing cascading filters in Retool. In this example, we’ll use a dataset containing information about the population of cities in the United States and several countries in Europe. (Side note; Retool has recently moved into a new office in London). The dataset is structured as follows:
1[
2 { country: "USA", state: "California", city: "Los Angeles", population: 3990456 },
3...
4]
5Each row in this dataset is a city, and each city has a country, state, and population. The country and state are hierarchical, meaning that each state belongs to a country. This hierarchical structure is what enables us to implement cascading filters.
You could query the data from a database, or use Retool Database, but in this instance we're going to use a Variable—the data is small enough that it's not worth the overhead of a database.
In the Retool application, we use three text input components, one for each filter. We also use a Table component to display the filtered data. This Table component is configured to present the data from the dataset we created previously (if you need help taking your data out of Google Sheets, we've got you covered).
The core use case for cascading filtering is to enable users to refine their data views by selecting values in a layered approach. In this example, users can select a country, then a state, and finally a city. To achieve this, the available options for each filter must be updated based on the selection in the previous filter. In Retool, this JavaScript logic can be implemented using a JavaScript query, which in this app is called "transformData," though the naming of your queries is up to you.
This is the complete JavaScript code that I used for updating the available options for the various filters. The code is annotated with comments to explain the logic.
Even though this JavaScript is specific to our country, state, and city hierarchy example, the general approach can be applied to any dataset with hierarchical data. The code is structured in three parts:
- Retrieve the unique options for each filter.
- Obtain the user selections from the filters, such as the selected country and state.
- Update the other filters based on the user selections.
1// the variable contains the dataset
2const data = geoData.value;
3
4// the selected country & state
5const selectedCountry = countrySelect.value;
6const selectedState = stateSelect.value;
7
8// get the unique options for the country filter
9const getCountries = () => [...new Set(data.map((item) => item.country))];
10// get the unique options for the state filter, based on the selected country
11const getStates = (selectedCountries) => {
12 let states = data
13 .filter((item) => selectedCountries.includes(item.country))
14 .map((item) => item.state);
15 return [...new Set(states)];
16};
17// get the unique options for the city filter, based on the selected country & state
18const getCities = (selectedCountries, selectedStates) => {
19 let cities = [];
20
21 if (selectedStates.length === 0) {
22 cities = data
23 .filter((item) => selectedCountries.includes(item.country))
24 .map((item) => item.city);
25 } else {
26 cities = data
27 .filter(
28 (item) =>
29 selectedCountries.includes(item.country) &&
30 selectedStates.includes(item.state)
31 )
32 .map((item) => item.city);
33 }
34
35 return [...new Set(cities)];
36};
37
38// return the unique options for each filter and pass in the selections from the previous filters
39return {
40 countries: getCountries(),
41 states: getStates(selectedCountry),
42 cities: getCities(selectedCountry, selectedState),
43};Now that we have the logic for updating the available options for each filter, we can configure the components to use this logic. We’ll begin with the country filter and then apply the same logic to the state and city filters.
The data that powers the country filter originates from the "transformData" query. Whenever you change your filter value (in this case change to a different country), the query will run again, updating the available options for the other filters. Here’s the JavaScript code for the country filter. The event handler is shown at the bottom of the screenshot below.
1const getCountries = () => [...new Set(data.map((item) => item.country))];
2const getStates = (selectedCountries) => {
3 let states = data
4 .filter((item) => selectedCountries.includes(item.country))
5 .map((item) => item.state);
6 return [...new Set(states)];
7};The final step is to update the default filters in the Table component. This can be done by using the new "Default filters" option, which makes it easy to set up filters on the Table component. The Default filters are configured to use the values from the country, state, and city filters. The "intersects" operator can be used when you send an array of filter options, while the "includes" operator is used when you send a single value.
You’re now ready to set up cascading filters in Retool, creating the foundation for an application that allows you to filter through hierarchical data sets. The strength of this approach lies in its use of Retool’s out-of-the-box components and the flexible implementation of filtering logic via JavaScript. It's simple but effective, and you can apply the general practices here to many use cases across departments. An HR team can use this app to filter through a workplace hierarchy such as department, team, and employee. A support team can use it to filter through customer type to service level and issue. With an OpenAI API, you can even take your tables to the next level with AI-powered filtering.
I hope this step-by-step explanation serves as a useful blueprint as you create cascading filters for your projects. You can download the app as a JSON file and import this into your own Retool instance, too.
Ready to build your first Retool table? Get started for free and see cascading filters in action.
Reader
