How to use setFilterStack to filter values in an array

[Nikki_Ip]

Hi, I am new to Retool. I have a form which intends to filter the table according to the selection.

I tried to use the multiselect component to filter the sales regions so that users can select multiple regions at once, and it returns an array with values, e.g. ["UK", "USA", "EU"]
const searchSalesRegionsMulti = multiselect3.value;

The useFilterStack works well on filtering a single value, but I am struggling with how to filter values in an array. I tried to use isOneOf, but it looks like it is not working.

table1.setFilterStack({
  filters: [
      {
        filters: [
          { columnId: "SalesRegions", operator: "isOneOf", value: searchSalesRegionsMulti },
        ],
        operator: "and",
      }
]
});

Display and edit data with the Table component | Retool Docs

[mckenna]

Hi @Nikki_Ip! That looks right to me -- as a side note it looks like you don't need the extra layer of filters -- something like

table1.setFilterStack({
  filters: [{ columnId: "SalesRegions", operator: "isOneOf", value: searchSalesRegionsMulti }]
});

should work the same (you can also use the setFilter API to set an individual filter -- it's a bit more ergonomic!)

Where are you calling this API? Your issue might come from the fact that it's only able to set the filter at the time it's run, and it won't update automatically when multiselect3.value changes

Have you considered using default filters for this functionality? I think it would achieve what you're looking for here in a simpler way:

[Nikki_Ip]

Thanks for the insight! I would definitely love to use the default filter if that suits my case.

However, I've tried isOneOf and includes, but none of it works... I wondered if that would have something to do with the original data, which is stored in Google Sheets as a string. For example: SalesRegions: "UK, EU, South Africa"
I split the data in the table, so it can be shown as tags. Should I transform the data to an array when it gets pulled instead? What would be the best practice in this case?

For the default filter
This is not returning the expected result. I selected the UK (single value as a test), which only 2 rows have the selected value, but it returned all rows. The same thing happened when I selected more than one option.

For the setFilterStack Method
I've tried to apply that formula along with my other filters, which return no rows.

This is what the formula looks like with all the single-value filters, which works fine.

const searchDeliveryBusinessUnitSingle = select17.value;
const searchSalesRegions = select19.value; //<--


table1.setFilterStack({
  filters: [{
      filters: [
      { columnId: "DeliveryBusinessUnits", operator: "includes", value: searchDeliveryBusinessUnitSingle },
    { columnId: "SalesRegions", operator: "includes", value: searchSalesRegions }, 
        ],
        operator: "and",
      }]
});

                      
return table1;

Then, I tried to switch to the multi-select, which has multi-values. When I select value(s) for Sales Regions, after clicking submit the table returns no rows.

const searchDeliveryBusinessUnitSingle = select17.value;
const searchSalesRegionsMulti = multiselect3.value; //<--

table1.setFilterStack({
  filters: [{
      filters: [
      { columnId: "DeliveryBusinessUnits", operator: "includes", value: searchDeliveryBusinessUnitSingle },
    { columnId: "SalesRegions", operator: "includes", value: searchSalesRegionsMulti }, 
        ],
        operator: "and",
      }]
});

                      
return table1;

[mckenna]

Ah I missed earlier that it was for a Tags column, not Tag! Try the intersects operator -- it will compare arrays to arrays (isOneOf compares a string value to an array)

[Nikki_Ip]

It works! Thank you @mckenna !

[MiguelOrtiz]

Hi @mckenna,

I tried replicating the same, but somehow it is not working:

From the above screenshot (have removed the screenshot as it contained sensitive info) you can see the filter stack, and the actual array in the selected row (which contains the same value), however my table returns blank.

Not sure what I'm doing wrong, would love your help on this.

Thanks

[Tess]

Hi @MiguelOrtiz,

Can you try using includes instead of intersects? Also, try 17 as a string

[mckenna]

There also seems to be a few other filters being applied with the "and" operator, so it won't show any rows if they also don't satisfy the other conditions. Might be good to double check those!

[MiguelOrtiz]

Hi @Tess ,

Changing the select values to string did the trick:

May I ask why is this the case?

Thanks!