Filter search drop downs by relevant values

A neat trick to help your users is to dynamically filter your search drop down values with only relevant options.

Here is my use case: Each client has an approved list of products to order. The list can be long. The order entry people need a way to filter the list down to find groups of products so they can enter the quantity that is being ordered.

Each product has a town_name linked to it and there are about 400 different towns. If the approved list only has 5 different towns, why list all towns in your filter drop down? Sure you could use a text search instead but this is another option which also has the added benefit of informing the user which towns are available. Not to mention the type ahead feature of the drop down. So I always use a drop down if there is a defined set, even if the list is long.

First you need a query that gets all of the client's approved products. Be sure to include a text value for the town_name, not just the town_id that you should actually have stored in your table.

Something like:

--qryLineItemsSelect
select li.*, p1.product_name, p1.bundleable, pt1.product_type,  p1.product_type_id as product_type_id,  t.town_id, t.town_name
from line_items li
Join products p1 on p1.sku=li.sku
Join product_types pt1 on p1.product_type_id=pt1.product_type_id
join towns t on p1.town_id=t.town_id
where invoice_id={{tblInvoices.selectedRow.data.invoice_id}}
order by t.town_name, pt1.view_order, p1.product_name

Make sure you transform your query to Array to the following JSON SQL query will work:

image

We now need to create a JSON SQL query to fill our drop down with all towns in the result.

-- qryTownsInLineItems
select distinct town_id, town_name 
from {{qryLineItemsSelect.data}}

And we need to revert the query back to object as the drop down can't handle arrays at the present time:

image

Now its time to create your drop down and wire it to the query:

image

Now we need a JSON SQL query to return our filtered results which will populate our table (notice that Retool often needs you to work from the top down rather than bottom up.)

-- qryLineItemsSelectFiltered
select * from {{qryLineItemsSelect.data}}
where {{selSearchTown.value || null}} IS NULL OR town_id={{selSearchTown.value}}

We are finally ready for the table component that will display the results:

image

That is all there is to it. Everything is pretty standard local JSON SQL Filtering Pattern except inserting that qryTownsInLineItems in the middle of the everything and properly configuring it.

2 Likes