Hi guys,
I know this topic has been covered in the “Select Component” documentation and some other post. Yet I still have the issue after following all the steps.
I want to filter my query by the value of an input text component and the value of a dropdown component. I have catered the {{!dropdownComponent.value}} scenario but the query does not return any rows when the dropdown is empty.
Much appreciated for your help.
Oliver
Hey @oliver.ng and welcome to the forum! It looks like your issue might relate to the bcn_input part of your query? You’ve added in the null handler for the select_customer component but not the bcn_input one.
@justin Thanks. Not really. I simplified the view to have only a table (hooked up with a query called “test”) and a dropdown select. Rows are returned in the table when dropdown is not null. The null handler somehow doesn’t work and no rows are returned when dropdown is null.
Oliver
Can you try putting that clause in parentheses?
WHERE ( {{ !select3.value }} OR customer_name = {{select3.value}} )
This is interesting, and I’ve been able to repro locally with BigQuery. They basically don’t want to handle null values at all. The reason this isn’t working as it does in the docs is that you’re using = instead of ILIKE, the latter of which can handle null values. So there are a couple of ways around this: you could set a default value for the select component, or find a way to handle the null in SQL.
Something like this should work:
WHERE {{ select1.value ? customer_name = select1.value : true }}
Thanks. is there a way to show “select all” in a dropdown?
The reason I don’t want to hide the clear button of the dropdown is because I want “null = select all”
You could add a button next to it, and then use that button to trigger a JS query that uses select1.setValue() to set it to all values.
sorry really no luck at all in processing null values (I have tried various if null then x with JS and query).
I notice the following message keeps popping up whenever there is null.
message:"Parameter types must be provided for null values via the ‘types’ field in query options.
Where is the “types” field and where is the “query options”, please?
@oliver.ng this is a BigQuery error - I don’t think it maps to anything in Retool. It could be a weird setting on your end, but it’s not something I’m seeing when I repro this locally.
I'm having a very similar problem. Not sure how to optionally filter bq tables using a potentially null input value.
I tried something like WHERE {{ select1.value ? customer_name = select1.value : true }}
but (using your example) customer_name isn't defined in the curlies. I also have a join in the query, so I would have something like a.customer_name which really made the javascript unhappy.
@atorres makes sense that this isn't working, since we're converting the output of those curly braces to a string, so BQ probably thinks you're saying something like WHERE "some_random_string". We really should put together a few best practices in our docs here (that's on me).
To make sure I understand what you're trying to do, you've got a select component, and you want logic along the lines of:
I think the workaround might be (unfortunately) to use regex:
WHERE REGEXP_CONTAINS(customer_name, {{ select1.value | .* }})
Does this work?
@justin @oliver.ng I have found a solution that works for this problem in a query I was running.
Use the ?? (or ||) to provide a default value of the correct type.
For example: WHERE {{!select1.value}} OR {{select1.value ?? ''} = customer_name