Filtering table in JS (caching settings, how to trigger transformer, and use values in JS code query)

Thanks for Retool, it’s really cool and you can definitely be proud of it.

I’m having trouble with something simple though. I have a table and need to add custom filters as separate UI elements - very similar to what’s done in the intro video. However, the DB query is expensive and slow so I cannot rerun it every time these UI elements are changed (e.g. value selected in multi-select). Instead, I’d like to rerun it in JS, which is much much faster, providing a snappy user experience.

I’ve tried a few approaches, but keep running into walls… Here’s what I’ve tried:

1. Transformer on query + caching the query
Enable caching on the query and attach a transformer to the query output directly to filter it there. Ideally, when the UI elements trigger the query, it’ll reuse the SQL results, but rerun the JS transformer. However, that doesn’t happen and it reruns the SQL.

2. Separate transformer
I wrote a simple JS transformer for filtering (see below) and use the output of this transformer as the input for the table. I actually like this approach of keeping the JS filtering separate and triggering that independently. However, there doesn’t seem to be any way to trigger this transformer from the UI elements so while the filtering works one time, it cannot be updated.

3. Query with run JS code resource
I tried making a query with resource “Run JS Code” and copy the code from (2) there. However, for some reason this JS code cannot reference values from other elements (e.g. {{filterName.value}}) and I get a syntax error Missing semicolon.

How do I do the table filtering in JS? Also, how do I trigger a transformer or use values from UI elements in a JS code query?

// Extract dataset and filter values
const nameSearch = {{filterName.value}}.toLowerCase()
const selectedLanguages = new Set({{filterLanguage.value}})
const selectedApps = new Set({{filterApps.value}})
const influencers = {{fetchInfluencers.data}}
const columnNames = Object.keys(influencers)

// Prepare output as separate arrays (expected by retool)
let output = {}
for (const k of columnNames) {
  output[k] = []
}

// Filter data
for (const row of formatDataAsArray(influencers)) {
  const { name, language, games } = row
  
  // Rows are only allowed if they fulfill all criteria
  if (nameSearch && !name.toLowerCase().includes(nameSearch)) continue
  if (selectedLanguages.size && !selectedLanguages.has(language)) continue
  if (selectedApps.size) {
    // Compute intersection to check if influencer has at least 1 app
    const overlapSize = games.filter(a => selectedApps.has(a)).length
    if (overlapSize === 0) continue
  }

  // Add to each column (stored independently in retool)
  for (const k of columnNames) {
 		output[k].push(row[k])
  }
}

return output
1 Like

Hey there @bfelbo and thanks for the kind words :slight_smile:

So my first inclination would be to add a “filter table” button, and only re-run your query with the filters when that button is clicked.

A couple of other things:

  • The table component has built in filtering functionality that’s just client side filters, so your query won’t re-run
  • The reason you’re getting that error is that in Run JS Code queries, you don’t need to use {{ }}
  • Transformers aren’t queries, so they don’t “trigger” - you just reference the .value property!

Let me know if this helps!

Thanks for the quick reply, Justin!

The table component has built in filtering functionality that’s just client side filters, so your query won’t re-run

I tried looking for this, but didn’t find anything else besides the filter icon in the bottom right of the table UI, which can’t handle our needs (e.g. games column has 1+ overlap with games multiselect). It’d be awesome to handle built-in JS filtering functionality! Is there another way to setup a JS filter, e.g. in the settings? If so, can you share how to use it?

The reason you’re getting that error is that in Run JS Code queries, you don’t need to use {{ }}

I see, thanks! If it’s not too hard, might make sense for you to provide a better error message in that case :slight_smile:

Transformers aren’t queries, so they don’t “trigger” - you just reference the .value property!

I’m not sure I understand what you’re saying, but I guess I should use a Run JS Code query here instead.

Yep, we’re going to be working on better error messaging a bunch soon :slight_smile:

Re: filters - yep, you’re right. the .filters property of the table is read only, which can be pretty confusing. We’ve seen enough questions on the community around this so I think it makes sense for us to work on in the future :slight_smile:

Re: transformers - any time you reference transformer.value the transformer runs. So the reference is the trigger, basically.

1 Like

Another vote for this, please. We are trying to achieve an app with global filters and even JSON w SQL slows down too quickly with conditional WHERE clauses that have a couple of options within.

Making the ‘filters’ property writeable would probably go a way to solving this.

1 Like