Table.setFilter() is here!?

Queries and Resources
1

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 it like this query that is triggered by my selProductTypeRight drop down:

tblProductsRight.setFilters([
  {columnName: "product_type_id", filterValue: String(selProductTypeRight.value), operator: "equals"}
])

To clear the filters set to an empty array: tblProductsRight.setFilters([])

After using setFilters() they will be in the filters list you see when clicking the filters icon.

setFilters() replaces any filters that are already set.

It seems to be about 3x faster than requerying the database in my application.

That’s all I have now.

7 Likes
2

Hey! Thanks for the feedback, I will get those docs fixed, and see what can be done about the reactivity issue you encountered.

3

And one note on setFilters() replacing the current filters, you should be able to do something like

table1.setFilters([...table1.filters ,{columnName:"id", operator:"equals", filterValue:"20"}])

to add additional filters without losing the filters that are already set!

1 Like
4

Thanks for the tip on adding a filter. Here is my code for doing multiple filters. I think I need to remove a filter if one of my parameters is empty so I have to rebuild it for every change anyway. If anyone has a better way, I am all ears!:

var filters = []
if (selProductTypeRight.value) {
  filters.push({columnName: "product_type_id", filterValue: String(selProductTypeRight.value), operator: "equals"})
}
if (selTownRight.value) {
  filters.push({columnName: "town_id", filterValue: String(selTownRight.value), operator: "equals"})
}
if (txtProductNameRight.value) {
  filters.push({columnName: "product_name", filterValue: txtProductNameRight.value, operator: "contains"})
}
tblProductsRight.setFilters(filters)

A couple more notes.

It looks like there is currently no way to set the filterStackType (or or and searches).

The one thing I cannot currently do with filters which I often do in my SQL queries is: product_type=x and (product_name like y or product_description like y). File that in feature requests though that seems like it would require a significant (read: breaking) architecture change within the filtering module.

And a little UI filter bug I noticed while playing. If you change the filter type in the UI it does not change the filtered results. If you add a filter or remove an existing filter the filtering is triggered with the new filter type.

5

I will look into adding an additional input to the function to set this, so you could do something like

table1.setFilters([{columnName:"id", operator:"equals", filterValue:"20"},{columnName:"id", operator:"equals", filterValue:"12"}], "or")

Yeah, unfortunately this is not possible within the current architecture, but we are working on improving a lot of our components, so there is a possibility of a Table V2 in the future with additional functionality like this!

2 Likes
6

Hi Mark and Bradlymathews, Can you guys please enhance my knowledge by telling me the way to apply a filter for "is empty" or "is not empty".

I have tried it many ways but found no success.
Like
t_ibor.setFilters([
{columnName: "COUPON_PF", operator: "is empty", filterValue: "" }
])

or

t_ibor.setFilters([
      {columnName: "COUPON_PF", operator: "is empty"}
    ])
7

I believe the operators are isNotEmpty or isEmpty, and the filtervalue is just an empty string ""

1 Like
8

Thanks mark it is done by the line of code
t_ibor.setFilters([{columnName: "COUPON_PF", operator: "isNotEmpty", filterValue: "" }])

3 Likes
9

This thread has been a huge help in building client-side filtering - thanks!

Here's what I have:

var filters = []
if (owner_filter.value) {
  filters.push({columnName: "customer_owner", filterValue: String(owner_filter.value), operator: "contains"})
}
customers.setFilters(filters)

This works fine for Dropdown components (single value), but I can't get it work for MultiSelect (multiple values)

I can't tell whether the issue is in the filterValue or the operator syntax - the docs aren't clear - Scripting Retool

All help appreciated!

10

Hi @wilgrace,

I believe that there are two issues with this:

  1. filterValue should be a single string, so for a multi-select component you will need to add multiple filters. Something like the following would probably work here
if (owner_filter.value.length>1) {
  owner_filter.value.forEach(f => {
    filters.push({columnName: "customer_owner", filterValue: f, operator: "contains"})
  })
}
customers.setFilters(filters, "or")
  1. When passing multiple filters, you should set the filterStackType. FilterStackType must be either "and" or "or" (defaults to "and" if no value set):
setFilters([{ columnName: "name", filterValue: "max", operator: "contains" }, { columnName: "name", filterValue: "john", operator: "contains" }], "or")
11

Ah that's great, thanks so much Mark - I didn't know if it was a limitation in setFilters but clearly it was a limitation in my JS skillz :slight_smile:

Thanks also to your support team who've been great - Victoria this time round

12

No problem! Always happy to help!

13

I've hit this limit on combining and and or parameters across multiple filters...

Is there any update on this, or any known workaround?

fyi Mark, you helped me here with my multi-select filter using FileStackType or - thanks again

14

Hey Wil,

I am not sure I understand. Do you mean that it is not possible to use both and and or filters at the same time? If so, yes, this is currently a limitation of the table filters 😞 Usually we recommend implementing more complex filters outside of the table UI at this time, but I have taken a note of this as an item to address in the upcoming table overhaul 👍

15

Yeah sorry, that was badly explained - it's as you say

No worries, I'll look to try to move some of the filters from the table UI to the server-side

16

Hey, just wondering if you ever came around to adding the capability to use both and and or filters at the same time?

17

Hey @Muin!

There is a new table filter in development that will support more broad customizability, but I don't have a timeline on when it will launch yet. You'll be able to arbitrarily create filter groups in the UI there, and is likely to look similar to this when live:

1474×1020 62.6 KB

3 Likes
18

@alex-w any update on the new table filter option that you had mentioned was in development back last year? Was this added? Thanks.

2 Likes
19

Hi, same question, any follow on this feature from 18 months ago? Thank you! :slight_smile:

20

Hey @Rada! Are you looking for something like our .setFilter() function?

https://docs.retool.com/apps/web/guides/components/table#filterstack

Or are you looking for more filter options in the UI?

https://docs.retool.com/apps/web/guides/components/table#sort-filter-and-customize-data-presentation