Filter by Date Optional

Ignition
1

Hi, I’m trying to filter a table, but I want an optional filter by date. I have a custom property in my table called “dateFilter”, this is the expression:

if({Root Container.checkbox.selected}=1,"and date between '{Root Container.startDate.date}' and '{Root Container.endDate.date}'","and 1=1")

When the checkbox =1 the custom property equals “and date between ‘{Root Container.startDate.date}’ and ‘{Root Container.endDate.date}’” and when the checkbox =0 the custom property equals “1=1”

I have this query in my table

SELECT * FROM tags WHERE  tag LIKE '%{Root Container.tagFilter.text}%' {Root Container.Table.dateFilterwhere} ORDER BY fecha DESC

When I set the checkbox to 1 the filter it seems to work but its not show any data in my table.

2

You might have to use the ‘dateFormat’ function to get the dates into a format your database likes.

3

The expression thinks your property bindings are part of the string, you need to pull the property bindings out of the string. Also the checkbox selected evaluates to true or false so you can just use the property, you don’t have to say =1.

Try -

if({Root Container.checkbox.selected},"and date between '" + {Root Container.startDate.date} + "' and '" + {Root Container.endDate.date} + "'","and 1=1")
4

[quote=“Pat”]The expression thinks your property bindings are part of the string, you need to pull the property bindings out of the string. Also the checkbox selected evaluates to true or false so you can just use the property, you don’t have to say =1.

Try -

if({Root Container.checkbox.selected},"and date between '" + {Root Container.startDate.date} + "' and '" + {Root Container.endDate.date} + "'","and 1=1")

Thanks Pat, that solved my problem, when I pull the property bindings out of the string it worked. Thanks!