Filter query where select component is null or select component.value

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

1 Like

Can you try putting that clause in parentheses?

WHERE ( {{ !select3.value }} OR customer_name = {{select3.value}} )

I did. but it’s still not working.

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”

1 Like

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?

1 Like

@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.