Filtering one table by another - simple question

Queries and Resources
1

I'm not far up the learning curve with Retool, so apologies if this is a simplistic question...I know it is. Hopefully it's an equally simple answer.

I have a table (housed in the Retool database) called Region that is in a parent-child relationship with one called Subregion. Very simple relation: Subregion's column Region_ID is declared as a foreign key into Region.ID. E.g.:

Region:
ID=1 [PK]
Name=California

Subregions:
ID=1 [PK]
Name=Napa
Region_ID=1 [FK]

ID=2 [PK]
Name=Mendocino
Region_ID=1 [FK]

...and so on.

Now, I'd like to display a table that shows the region names and another with the subregion names. When the user clicks on a region, the query underlying the Subregion table is updated/filtered to just show the subregions owned by that region. Easy-peasy...but I'm stumped.

What I thought would work is this:

qryRegions is the query underlying the Regions table on the form. SQL:

SELECT * FROM REGION
ORDER BY NAME;

qrySubregions is the query underlying the Subregions table. SQL is

SELECT
*
FROM
"Subregion"
WHERE
"Subregion"."Region_ID" = {{ qryRegions.data.ID }};

This SQL can be saved without error.

The last step was to create a handler on the Regions table component with these property settings:

Event: Select row
Action: Control query
Query: qrySubregions
Method: Trigger

If I understand this, this means "When a row is selected, refire qrySubregions." Since the Subregions SQL looks at the primary key of the Regions table, it should display only those subregions that belong to the region of the selected row. But I get no rows at all, regardless of the Region row selected. It simply doesn't work.

What am I missing here? This seems so basic, but after hours of delving through the docs and reading other posts I can't get it to work. Any guidance is MUCH appreciated! Thanks in advance.

2

Hi @edwardhamlin

In the where clause of the subRegion query you'll want to use the selected data from the Region table instead of the data from the Region query.

{{ tblRegions.selectedSourceRow.id }}
or
{{ tblRegions.selectedRow.id }}

The difference between the between these properties can be found Table (New) | Retool Component Library.

3

Beautiful! Works like a champ. I really appreciate the quick response and will read that documentation. Thank you.

1 Like