How can I use one table to filter another table?

The table called table_key is the key/legend for the other table called table_final_results:

I want the user to be able to click on one of the cells in the Key/Legend table on the left (called table_key) and have the right table (called table_final_results) filter the rows based on what they clicked.

I have it color coded, so I can actually us this logic:

If table_key.color_hex.value is #DB0C41, then select only rows in table_final_results that have table_final_results.color_hex.value as #DB0C41.

Note: table_final_results is populated with the rows from a JSON with SQL query called final_results.

I need assistance with the SQL and JavaScript and all the queries and transformers to make it work. Something like this should work, but it doesn't:

Any ideas?

Hey Bon!

I think this should work. I'd recommend using a JS transformer + .filter() actually, instead of Query JSON with SQL. Then, you can use {{transformer.value}} in your table.

Quick note. If the color_hex column is a custom column, it won't show up in table.selectedRow.data. Custom column data might show up in table.columnMappers, and then you could use the selectedRow.index to grab the right value from columnMappers (lmk if you need help here!)

I need a few more screenshots to be able to adequately follow what you're doing, I think.

I did try a transformer, but I get NO results when I try to preview it:


^^^^^^ see that big empty space where data should be...?

I am also using the column that is not shown, but that shouldn't make a difference, right?

Also, color_hex is not a Retool custom column, I created it in a query:
image

@victoria, It looks like I have the same thing you do, but it doesn't work:

Can you please include more screenshots in your explanation. I can't tell which table is which and I don't know how my queries compare to yours.

Thanks!

Hey Bon! Sorry about the confusion, hopefully this helps :slight_smile:

  • table3 (top) just has the Key values
  • table2 (bottom) shows either the filtered data (based on table3) or all data (from getActors) if there's no filtered data yet (ie when there's no selected row on table3)

It looks like your filter is using one =, which is declarative. In order to compare, you'll need to use == (non strict type checking, ie "3" == 3 returns true) or === (strict type checking, ie "3" === 3 returns false). One of Javascript's many, delightful quirks. :sweat_smile:

As a quick note, I used formatDataAsArray(data) because getActors returns an object of arrays, and we can't use the .filter() method on an object.

formatDataAsArray(data) will convert an object of arrays into an array of objects! If your final_results.data is already an array of objects, no need to use it. You can just return data.filter(etc).

Thanks @victoria. Alas, I don’t need clarification on where your tables are shown on your app or even the syntax used (but thank you for those details - good to know!). Rather, I need clarification on how your objects align with my objects.

I think this is correct:

victoria.table3 = bon.table_key

victoria.table2 = bon.table_final_results

victoria.getActors = bon.final_results

victoria.table3.Key = bon.table_key.color_hex

victoria.getActors.actor_id = bon.final_results.color_hex

Is this right?

Perfect! I'll definitely make my names easier to follow next time.

Looking at your transformer again, row.color_hex == {{table_key.selectedRow.data.color_hex}} should work as the filter clause! Is that transformer returning any data?

Hi @victoria

I am trying something very similar in order to filter a custom template column. Thus I am using recordUpdates as you suggested:

image

I am not sure the syntax to reference the custom column however to determine if the checkbox is active. This gives me an "Unexpected template string" error:

let data= {{table1.recordUpdates}};
return data.filter(row=>row.`Custom Column 1` == true);

How do I reference the custom column in dot format given the spaces in the name?

Hi @rkaplan, have you tried using row['Custom Column 1']? This one worked on a test one I did.

Thank you @jocen

Yes that worked perfectly