How to create a date filter with a dropdown field for months and another for a list of years?

Question

Hi, 
 My question is how you could create a dropdown with the list of months and another dropdown with a list of years and when selecting a value, filter by date. I currently have a date field in my database. I have created an expression rule with a series of filters and one of them is the date with the operator between and the value: 
 {datetime(year(ri!date), month(ri!date), day(ri!date), "00:00:00"),datetime(year(ri!date), month(ri!date), day (ri!date), "23:59:59")}. 
 But how could I do it with a dropdown for the year and another for the month? Thank you very much for the help.

José Manuel Ojeda · Accepted Answer

Hi! Thanks for your help. Currently I used this code and It's working: 
 a!queryFilter(
 field: "fhAlta",
 operator: "between",
 value:
 if(isleapyear(ri!year),
 {
 datetime(ri!year, ri!month, 1, 00, 00, 00),
 datetime(ri!year, ri!month,if(ri!month=2, 29, if(or(ri!month=4,ri!month=6,ri!month=9,ri!month=11),30,31)), 23, 59, 59)
 },
 {
 datetime(ri!year, ri!month, 1, 00, 00, 00),
 datetime(ri!year, ri!month,if(ri!month=2, 28, if(or(ri!month=4,ri!month=6,ri!month=9,ri!month=11),30,31)), 23, 59, 59)
 }),
 applyWhen: not(isnull(ri!month))
 ),
 a!queryFilter(
 field: "fhAlta",
 operator: "between",
 value:{
 datetime(ri!year, 1, 1, 00, 00, 00),
 datetime(ri!year, 12, 31, 23, 59, 59),
 },
 applyWhen: not(isnull(ri!year))
 ), 
 I useed the function isleapyear and one condition for change the number of days of February.

sahilb8348 · Answer

Hello Jose, 
 
 What i Understand is that in the table you ae having 1 date field, and in the interface, you want to have 2 dropdowns of month and year on that basis need to fetch the data? right? 
 
 if this is the case why you are not having complete date filter on the screen means user can select two dates and you can fetch the data between those 2 dates. Because if we need to fetch the data on the basis of the month and year then there are 2 ways to 
 1. We need to fetch all the data and then to get the expected result we need to manipulate the date column by expression rule to fetch the month and year from the date column only because from query entity directly we can't query month and year. 
 2. When user is selecting the month and year, we can append the start day of that month as 01 always and end date 30/31 depend on the month, after that we can query the data from the table. 
 like: Use select Month: April and Year 2022 
 pass this value in expression rule to create the date: get the dates as {StartDate: 01/04/2022, End Date: 30/04/2022} 
 Query in query entity Like { 
 1st Way: 
 filter: a!queryFilter( field:"datefield", operator:"between", value:{StartDate,EndDate} ), 2nd Way logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: " datefield ", operator: ">=", value: StartDate ), 
 a!queryFilter( field: "datefield", operator: "<=", value: End Date ) 
 }, ignoreFiltersWithEmptyValues: true ),

How to create a date filter with a dropdown field for months and another for a list of years?

Top Replies