I am creating a building list page and want to be able to filter it using a dropdown list of all buildings.
On initial load, the default value of building_select.value is 'all' in which case I run the allBuildingsQuery.
When the building_select.value changes, I want to be able to run a different query that will accept the value of the building_select.value from the control to limit the results and setData in the same table.
The initial load works fine, but I'm not sure how to dynamically load one query or another always based on building_select.value.
One approach I had was a JS query that triggered one or the other, which would be triggered by any change in building_select.value:
// query name: queryController
if(building_select.value === "all") {
reconAllBuildings.trigger();
//resultTable.setData(reconAllBuildings.data); // not allowed here?
}
if(building_select.value !== 'all') { // check integer val too
reconByBldg.trigger();
//resultTable.setData(reconByBldg.data); // not allowed here?
}
When those queries are successful, their event handlers are used to setData() on resultTable (not the above commented out code).
I'm running into issues now where the event handler for building_select.value to trigger my JS query does not seem to work—initial load shows all tables, but a change in building_select.value does not trigger the JS query above.
What am I missing? Maybe a different controller concept? Different panels? Completely different tables that hide/show? Initially started out trying to make a single query work with this with params but it's difficult to use JS in queries to dynamically change initial full WHERE clauses...any help appreciated!
There is a simpler way to do this which uses a single query.
Put this into your Where clause and set the query to be your List's or Table's Data source to the query.
select *
from buildings
where ({{building_select.value || null}} IS NULL OR building_id={{building_select.value}})
This is a standard Retool pattern for getting all records if the select is empty and getting just the selected one if not. You can also use a similar technique for other component types to build a pretty sophisticated filtering query.
For instance:
select p.*, t.town_name, pt.product_type
from products p
join towns t on p.town_id=t.town_id
join product_types pt on p.product_type_id=pt.product_type_id
where ({{selSearchProductType.value || null}} IS NULL OR p.product_type_id={{selSearchProductType.value}})
AND ({{selSearchTown.value || null}} IS NULL OR p.town_id={{selSearchTown.value}})
AND ({{txtSearchProduct.value || null}} IS NULL OR (p.product_name like {{'%' + txtSearchProduct.value + '%'}} or p.product_description like {{'%' + txtSearchProduct.value + '%'}}) )
AND (1= {{chkLowStock.value === true ? 0: 1}} or (reorder_trigger > inventory_count) and reorder_trigger > 0)
AND (1= {{chkSearchUncounted.value === true ? 0: 1}} or (inventory_count = -1))
AND ({{dtSearchOlderThan.formattedString || null}} IS NULL OR p.last_inventory_check <{{dtSearchOlderThan.formattedString}})
1 Like
Thanks for the hint! That's a great method - I'm struggling to make it work and am getting the message could not determine data type of parameter $1. Is there some other default value or typing I can add?
Got it to work by using a default value of 0 instead of null or undefined, which I couldn't get the PostgreSQL parser to work with for some reason. Default value seemed to map to empty string by default and it didn't like 'string OR int' as that first param.
WHERE ({{buildingSelect.value}} = 0 OR inv.building_id={{buildingSelect.value}})
Glad you got it working. I deal with SQL Server and Postgres in equal amounts (the sample I gave was for SQL server) and the techniques can be a slightly different between the two.
