Hi i tried to create a date filter like this
for date column in db im using {{ moment.tz("America/Panama").format() }}
but the filter doesnt works
SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */
AND ({{ !textInput6.value }}
OR no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */
AND ((fecha >= {{ dateRange1.value.start}} and fecha <= {{ dateRange1.value.end}}) OR ({{dateRange1.value.start == ''}} AND {{dateRange1.value.end == ''}})) / DATE FILTER/**
Loom | Free Screen & Video Recording Software | Loom if you see in this video it works when i put like from december 5th to dec 6 and it shows rows with december 5, if i put december 5 to dec 5, doesnt show anything
what im doing wrong?
thank you
Hey @agaitan026! Hmm, I'm testing a similar query and using the same start and end dates seem to be working for me.
Would you mind sharing a screenshot of the data that your listaRegistrosCombustible query is returning?
1 Like
here is the original query:
and with filter:
and query (without date range)
SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */
AND ({{ !textInput6.value }}
OR no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */
Thank you for sharing that! Would it be alright if I stepped into your app to take a look around? If so, would you mind DM'ing me a link to your app? If not, no worries at all!
1 Like
done query filtered is named: listaRegistrosCombustible_Filtro
i saw you are using datepicker? i cant see that on retool im using now this:
SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}}
WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR (idcolaborador = {{ select1.value }}) / filtro por colaborador */
AND ({{ !textInput6.value }}
OR no_factura ILIKE {{ textInput6.value }}) /* filtro por colaborador */
AND ({{ !(dateRange1.value.end&&dateRange1.value.start) }}
OR (fecha <= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}
AND fecha >= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}} )))
its working with this
SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */
AND ({{ !textInput6.value }}
OR no_factura ILIKE {{'%' + textInput6.value + '%'}}) /* filtro por colaborador */
AND ({{ !(dateRange1.value.end&&dateRange1.value.start) }}
OR (fecha <= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}
AND fecha >= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}} ))
but if i put only Start Date, still shows everything, any chance to modify to accept when user only select Start date?
thank you
Ah, so when the user only selects Start date, you'd like it to use that date for the Start and End, or you'd like to show everything >= Start date?
Of course!
1 Like
both could be possible? i mean if user put start date and not end date, should shows >=, if user put both start and end should show too, this is my current query that works only when i put both start and end
SELECT * FROM {{formatDataAsArray(listaRegistrosCombustible.data)}} WHERE
({{!select1.value}} /* si esta en blanco muestra todo /
OR idcolaborador = {{ select1.value }}) / filtro por colaborador */
AND ({{ !textInput6.value }}
OR no_factura ILIKE {{'%' + textInput6.value + '%'}}) /* filtro por colaborador */
AND ({{ !(dateRange1.value.end&&dateRange1.value.start) }}
OR (fecha <= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}
AND fecha >= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}} ))
so if i select 2022-12-05 its showing 2022-12-04
How does this look? 
I just changed your line 8 to no longer require a start AND end date (line 8 now says if there's no start value, show everything. If there is a start value, we can use the filter!)
1 Like
awesome, works perfect, thank you
Woohoo! So happy to hear that 
1 Like