Implementing Dynamic Count Cards with Toggle Filters in Retool

Queries and Resources
1

Hello all!

I'm currently working on an interactive dashboard in Retool and need some guidance on implementing a specific feature. I've attached a picture to illustrate my concept better.

Background: I have a main table sourced from Snowflake, displayed through a Retool query. This table contains thousands of records, each representing a person with fields like PERSON_ID, AGE, CITY, ZIP, and boolean flags such as IS_TALL, IS_FUNNY, IS_A_PROGRAMMER. The table supports server-side pagination, and there's a secondary query for the record count to manage this.

Requirement: My goal is to create a dynamic interface with three (eventually more) cards, each representing one of the boolean attributes (IS_TALL, IS_FUNNY, IS_A_PROGRAMMER). Each card should display the count of people in the entire dataset for whom the respective attribute is TRUE.

Additionally, I want these cards to act as toggle filters. For instance, if a user clicks on the IS_FUNNY card, the table should update to show only the records where IS_FUNNY is TRUE. Simultaneously, the counts on the IS_TALL and IS_A_PROGRAMMER cards should adjust to reflect the number of people who are both funny and tall, and funny and programmers, respectively.

Challenges:

  • How to set up these cards to dynamically display counts based on the entire dataset and update as filters are applied.
  • Ensuring that the table updates correctly when these filters are toggled.
  • Adjusting the counts on the other cards based on the applied filters.

I'm not sure what the best approach would be to implement this in Retool. Should I use JavaScript queries or is there a more straightforward way through Retool's native components and query configurations? Any insights, examples, or guidance on how to achieve this functionality would be greatly appreciated.

Thank you in advance for your help!

Mock-up examples:
Step 1:

1_unfiltered
1_unfiltered1283×617 89.9 KB

Step 2:
2_one_filter
2_one_filter1288×599 90.2 KB

Step 3:
3_two_filters
3_two_filters1279×608 91.1 KB

2

Hey there, in order to do this, you can use checkboxes in the cards. This lets users choose specific criteria they want to apply. The selected checkbox values will automatically be captured and used in your query. Your query's variables will change depending on which checkboxes are selected. This creates flexible filtering based on user choices. I've prepared an example app and attached it to this response, showcasing this functionality in action. You can explore the code to see how it works first hand.

Since you're using server side pagination, you'll want to cache your query results so that you can filter the entire data set. Here is more information about that.

Don't worry about complex queries. Here's an example of how dynamic values simplify filtering even with many options:

SELECT *
FROM DEMO_DATA.TESTTABLE
WHERE 
    (NOT {{ checkbox1.value }} AND NOT {{ checkbox2.value }} AND NOT {{ checkbox3.value }})
    OR
    ({{ checkbox1.value }} AND IS_FUNNY = true AND
       (NOT {{ checkbox2.value }} AND NOT {{ checkbox3.value }}))
    OR
    ({{ checkbox2.value }} AND IS_TALL = true AND
       (NOT {{ checkbox1.value }} AND NOT {{ checkbox3.value }}))
    OR
    ({{ checkbox3.value }} AND IS_A_PROGRAMMER = true AND
       (NOT {{ checkbox1.value }} AND NOT {{ checkbox2.value }}))
    OR
    ({{ checkbox1.value }} AND {{ checkbox2.value }} AND IS_FUNNY = true AND IS_TALL = true AND
       NOT {{ checkbox3.value }})
    OR
    ({{ checkbox1.value }} AND {{ checkbox3.value }} AND IS_FUNNY = true AND IS_A_PROGRAMMER = true AND
       NOT {{ checkbox2.value }})
    OR
    ({{ checkbox2.value }} AND {{ checkbox3.value }} AND IS_TALL = true AND IS_A_PROGRAMMER = true AND
       NOT {{ checkbox1.value }})
    OR
    ({{ checkbox1.value }} AND {{ checkbox2.value }} AND {{ checkbox3.value }} AND
       IS_FUNNY = true AND IS_TALL = true AND IS_A_PROGRAMMER = true)

dynamic-query.json (36.6 KB)