Table's client side filtering outside of table

[mpmohi]

Hi,
Retool's built-in filtering for table is great but they are baked into the table. When we have some complex queries to generate the table (and there are many) is there any way we can implement that same incredible searching from other UI for example a text box ? is this even possible to achieve ?

Thanks.

[mpmohi]

Table.setFilter() is here!?

Ok, I just discovered Table.setFilter(). Had to refresh the docs to see it so I don’t know when it dropped. Thought I’d throw out some lessons learned from playing with it for a bit. First, there is an error in the docs. You use operator not operation to set your comparer. Second, it is not reactive. I tried to set the filters property using the value of a dropdown and changing the dropdown did not change the filter. Now onto the good news. Rather than requerying your dataset you can filter i…

setFilter documentation

[Screenshot 2022-08-15 225135] Just use the 'Run Script' and use this formular style to set your filters as an array.

[bradlymathews]

I have moved on from using setFilter() and now do all of my filtering using JSON with SQL queries.

https://docs.retool.com/docs/querying-via-sql

I use a query to get all of my records as you normally would (say qryPossibleVenues).

Then I create a JSON with SQL query can call it something like qryPossibleVenues_Filtered and use that as the source for my table.

You can then do pretty standard Where clauses to do you filtering

select * from {{qryPossibleVenues.data}}
where (({{selFilterLeg.value || null}} IS NULL OR leg={{selFilterLeg.value}}) OR ({{selFilterLeg.value || -1}} = -1 AND leg is null))
AND ({{selFilterStatus.value || null}} IS NULL OR status={{selFilterStatus.value}})
AND ({{txtFilterVenue.value || null}} IS NULL OR name like {{'%' + txtFilterVenue.value + '%'}})
AND ({{swHideDeadVenues.value || false}} = false or not status=5)

This is easier to program, easier to reason with and you can do almost any kind of filtering you can imagine.

The only gotcha is you need to make sure your source query is an Array of Objects so depending on where you get your data from you may need to do

select * from {{formatDataAsArray(qryPossibleVenues.data)}}

Or add this to your source query's transformer (which I usually do)

[mpmohi]

Hey Mathews ,
you are a life saver. Now I was trying to use JsonQL first and pretty sure saw documentation but could not figure out how should I update my table with the result. As you mentioned the trick is to use the filtered query as source.

Thanks @bradlymathews

[agaitan026]

even with postgresql you use that to filter? what is the difference to have only one query with all those filters?

[bradlymathews]

Yes, the original database or API that was used to get the source data is irrelevant. As long as it is an array of object JSON with SQL can query it.