How do I get my filter to work if the date picker component has no value?

[Jake_Hamilton]

Hello

I am building my own filter for my table, all is working okay until I try filter by date.

I want the table to filter by the date when entered into the component (this is achieved by using AND "Visit_Date" = {{date1.value}}).

But when the date picker has no value nothing shows in the table, it is almost like I am making it required. How do I make it so if the date1 value is null, it shows all results?

Please see screenshots below:

This screenshot is showing all the table results (have removed the date line in the query so I can show what I want to always be pulled through from the table)

This one is showing the query with the date line and Oct 19, 2023 entered in the date1 component - 2 results showing as expected

Now if I add the date query back in and have the date1 component value as nothing, this shows:

Hope this makes sense!

Thanks

[lamh_bytecode.io]

Here is a little trick I used sometime for this situation.

Basically if value is valid then use value, else field equals itself. The {{ date1.value || moment().format() }} essentially substitute today's date into the output if date1.value is blank, but since it's in the value is valid case, it's never get used.

[Jake_Hamilton]

Hi Iamh

Thank you for the response. I have tried applying this to my code but unfortunately can't get it to work.

I will admit, I am a bit of a noob (in the learning process). I have copied your format (I assume incorrectly) and this is the result:

Value of the date component is still currently empty for this screenshot.

Any more advice would be appreciated.

[lamh_bytecode.io]

Oh try this

AND "Visit_Date" = CASE WHEN {{ !!date1.value }} THEN {{ date1.value || moment().format() }} ELSE "Visit_Date" END

replace date1 with your date component's ID

[Jake_Hamilton]

You are the man, it worked! Can you explain the process if possible so I can learn for next time?

[lamh_bytecode.io]

As I was typing out the explanation, I realized the logic can be shorten to this:
{{ !date1.value }} OR "Visit_Date" = {{ date1.value || moment().format() }}

this is simpler to explain. The issue here is PostgreSQL evaluates the syntax first before running so "Visit_Date" = {{ date1.value }} becomes "Visit_Date" = '' during evaluation and it doesn't like that hence the error you've been getting.

the trick is {{ date1.value || moment().format() }} it will return a non empty value that satisfied the check. If date1.value is empty/null/undefined then return moment().format() - essentially now() timestamp.

However, moment().format() will not matter since {{ !date1.value }} is true when no selection so all records will return.