Dynamic Filtering of Mapped Values

[tspecht]

Hello!

I am wondering if it is possible to dynamically filter the data I have from a SQL query into the mapped value of a component.

For example, I have a query with two columns, "category_id" and "Name". I would like to have the drop down show all Name" values, but only if they have a "category_id" of 2.

I attempted using a ternary but had no luck.

I also attempted to use a transformer, but could not seem to get the right syntax. Additionally, it is not ideal to have 10+ transformers for each dropdown.

[Oscar_Ortega]

Hi, you can do something like this

{{ table1.data.filter(item => item.role === 'Viewer') }}

[tspecht]

Looks like I do not have the ability to use the filter function in the component.

I am using a drop-down button and a mapped source directly from a query.

are you saying I need to put it into a table before filtering it?

[Oscar_Ortega]

a tricky/strange approach (i think transformers are way better)

{{
  query1.data.country.reduce((acc, country, index) => {
    if (country === 'Canada') {
      acc.push(index);
    }
    return acc;
  }, [])
}}

In data source im getting all indexex where country matches Canada

[Oscar_Ortega]

honestly i think there is a better way to do this but this was my approach

[brettp]

Hello @tspecht ,

Thank you for your question.

Yes, you can dynamically filter SQL query results for populating a dropdown component in Retool either by adjust the SQL query itself or using a JavaScript transformer.

Approach 1: Modify the SQL query. You can do this with a WHERE clause like so:

SELECT Name
FROM YourTable
WHERE category_id = 2

This will return only rows where category_id is 2, and specifically the Name column. The result can be used for your dropdown values. You could also use variables in your queries in Retool to allow the user to specify what category_id they are interested in.

Approach 2: Use JavaScript transformers in Retool. Assuming that your SQL data is stored in a query called getValues, follow these steps to add a transformer:

1. Click the + button in the Query Editor and select JavaScript transformer.
2. Name it filteredCategories.
3. Paste in the script:

const data = {{getValues.data}};
const filteredData = data.filter(row => row.category_id === 2);
return filteredData.map(row => row.Name);

4. Save the transformer.

Next, update your drop-down Values to {{filteredCategories.value}}. This way, the dropdown will show all Name values where category_id equals 2.

Please ensure that getValues, category_id and Name matches your actual SQL query and column names in your data.

However, if your SQL query produces a large amount of data, it's often recommended to handle filtering directly in SQL rather than with client-side JavaScript in Retool. This method is generally more efficient and reduces the number of transformers you need to create and maintain in Retool.

Hope this helps you make some progress is your project.

• Brett