Hi, is there a way to dynamic filter the visible rows in a table without having to retrieve data again?
I’m looking for a way to show and hide rows in the table, based on selection from different multiSelectLists without having to retrieve data again.
Hi @Helle! Welcome to the forum !
I think the best option here is to use a Query JSON with SQL query. Here's a quick example of Query JSON with SQL being used with to add a search field to Google Sheets data, but can be applied generally.
- Query unfiltered data from data source, most likely on page load
- Create a Query JSON with SQL:
In here, we select all of our unfiltered data (step 1), and then add a where clause from a text input which matches to a field in our data (author in this case). We also need to set it to 'Run automatically when inputs change'.
- We then set our table data to be the result of our Query JSON with SQL (step 2):
Typing into the text input will trigger our query and filter the data in the table. This is definitely a basic example, and you could add other Retool components to be included in the filtering query.
Thank you very much
I could use a Query JSON and then filter data in the transformer.
Thanks for this hint. But I've been faced with the wrong display data in the table - it looks like data pivoted and each row was a data column in the source dataset.
Is there any way to show it correctly?
Hi there! Could you try changing your data source from {{sqlQuery.data}}
into {{formatDataAsArray(sqlQuery.data)}}
?
This query type works best with arrays of objects, rather than objects of arrays which is how SQL queries return data in Retool!
Hi, I was able to set up the Query JSON with SQL query and the Preview shows the data in a table just like I wanted it, filtering properly. However when I set that as the data source for a table in my app, it shows no data. The Run button on the editor shows the second screen shot (not in table format).
I tried to switch to {{formatDataAsArray(sqlQuery.data)}}
but then both the Preview and the Run data disappears with no error messages. My original data came as a json from an API but it considered an object in Retool.
Hi @mups11! To make sure I'm following, your current issue is that showing {{ your_query_json_w_sql_query.data }} isn't populating your table? Would you mind sharing a screenshot of that query in the left panel? As a quick note, tables accept arrays of objects or objects of arrays in the data field!