Search by Month/Year

Search by Month/Year

  

Hi,

I have search field that allows user to search a period of time.

Start Month / Start Year to End Month / End Year.

The data i have is in date (20/08/2017). May i know how do i filter the date range?

Thanks

Hi Jace,


You can use the Date functions to convert your input into dates so that in your query you can use the filter "in between" and search between dates.

https://success.outsystems.com/Documentation/10/Reference/Logic/Built-in_Functions/Date_and_Time?origin=d


Shashank...

 

Hi, 

thanks for the help.

I facing one problem, my combo box for month is Jan,Feb, March, April...., how can i convert to the value for the month??

Hi Jace,

Is this combo filled dynamically or you have values pre-defined?

You can fill it with Value and Options, where values are 1,2,3....and Options as Jan, Feb, Mar. So while you get the value you can easily convert it into Month.


Shashank...

Hi,

The conversion is the other way around but the mechanism should be the same.

https://www.outsystems.com/forums/discussion/13046/date-conversion/

Best regards,

Marcelo

Shashank Diwan wrote:

Hi Jace,

Is this combo filled dynamically or you have values pre-defined?

You can fill it with Value and Options, where values are 1,2,3....and Options as Jan, Feb, Mar. So while you get the value you can easily convert it into Month.


Shashank...

Hi, 

The values are predefined (static entity). What do you mean by value and options?


Hello Jace,

Please, find attached an example.
Hope it helps.

https://eduardojauch.outsystemscloud.com/SearchYearMonth/Home.aspx?_ts=636410019138092330

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hello Jace,

Please, find attached an example.
Hope it helps.

https://eduardojauch.outsystemscloud.com/SearchYearMonth/Home.aspx?_ts=636410019138092330

Cheers,
Eduardo Jauch

Hi,

Thanks for the oml. May i know how you get the month number (source identifier attribute)


Hi,

I used the order attribute in the static entity. To be able to use it, I had to create the aggregate and two calculated fields, one for the month name, to use as the "show" field, and thus enabling the selection of the field to return on selection. Than a second calculated, with the order, that is the value to return on selection.

Cheers

Eduardo Jauch

Hi Eduardo Jauch,

Yes, i managed to figure out. :) Now my next question is how to get the value and put it into new date function to compare the dates.

Hi,

If the filter is by month and year only, you don't need to create a new date. I have an example in the aggregate filter.

Trying to create a new date will be a problem for the end filter, because you will have to discover how many days the month have (possible, but annoying)

Just using the month and year is easy. 

Cheers

Eduardo Jauch

Eduardo Jauch wrote:

Hi,

If the filter is by month and year only, you don't need to create a new date. I have an example in the aggregate filter.

Trying to create a new date will be a problem for the end filter, because you will have to discover how many days the month have (possible, but annoying)

Just using the month and year is easy. 

Cheers

Eduardo Jauch

Hi Eduardo,

Noted. But i faced one weird problem. When i include the filter, all my course table return empty row even though i haven't select anything.


Hi Jace,

Sorry, the filter that I did didn't take into account if you don't set the filter.
In this case, the following should do the trick:

(
  StartYear = 0 
  or 
  (
    Year(User.Creation_Date) > StartYear 
    or 
    (
      Year(User.Creation_Date))= StartYear 
      and 
      Month(User.Creation_Date) >= StartMonth
    )
  )
)
and
(
  EndYear = 0
  or
  (
    Year(User.Creation_Date) < EndYear 
    or 
    (
      Year(User.Creation_Date) = EndYear 
      and 
      Month(User.Creation_Date) <= EndMonth
    )
  )
)

In the case above, I'm assuming that if you want to use a filter (start or end), you must provide both the month and the year.

But i PREFER the solution bellow:
Use something more straight forward, like comparing dates, you have just to prepare the startDate and endDate and do something like this in the filter:

(useStartDate = False or User.Creation_Date >= startDate)
and
(useEndDate = False or User.Creation_Date < endDate)

In this case, useStartDate and useEndDate are booleans that will turn on/off the filters depending on what the user wants to use.

To set "startDate" is very easy.

startDate = NewDate(startYear, startMonth, 1)

To set "endDate" is more trick, because you don't know the number of days of the months, so, a trick, and this is way we used the "<" instead of <=, is to add a month to the endDate using 1 like this:

endDate = AddMonths(NewDate(endYear, endMonth, 1), 1)

And this is much easier to use, indeed :)

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hi Jace,

Sorry, the filter that I did didn't take into account if you don't set the filter.
In this case, the following should do the trick:

(
  StartYear = 0 
  or 
  (
    Year(User.Creation_Date) > StartYear 
    or 
    (
      Year(User.Creation_Date))= StartYear 
      and 
      Month(User.Creation_Date) >= StartMonth
    )
  )
)
and
(
  EndYear = 0
  or
  (
    Year(User.Creation_Date) < EndYear 
    or 
    (
      Year(User.Creation_Date) = EndYear 
      and 
      Month(User.Creation_Date) <= EndMonth
    )
  )
)

In the case above, I'm assuming that if you want to use a filter (start or end), you must provide both the month and the year.

But i PREFER the solution bellow:
Use something more straight forward, like comparing dates, you have just to prepare the startDate and endDate and do something like this in the filter:

(useStartDate = False or User.Creation_Date >= startDate)
and
(useEndDate = False or User.Creation_Date < endDate)

In this case, useStartDate and useEndDate are booleans that will turn on/off the filters depending on what the user wants to use.

To set "startDate" is very easy.

startDate = NewDate(startYear, startMonth, 1)

To set "endDate" is more trick, because you don't know the number of days of the months, so, a trick, and this is way we used the "<" instead of <=, is to add a month to the endDate using 1 like this:

endDate = AddMonths(NewDate(endYear, endMonth, 1), 1)

And this is much easier to use, indeed :)

Cheers,
Eduardo Jauch

Hi,

Thanks. How to set the useStartDate or useEndDate? You mention that it is used to turn on/off the filter, how do we know to turn on or off the filter? Anyway my combo box is Jan, Feb, March, how do i assign the monthNumber 1,2,3 to the local variable instead? Appreciate if there is example to help please?


Hello jace

the useStartDate and useEndDate are set on your logic, depending on what the user does, like, if it set the filters like chose a start year and start month, than you set the useStartDate to true annd useEndDate to false.

Regarding the monthNumber, you can find how to do this in the OML I sent you previously. I used the Order of the static entity.

Cheers,
Eduardo Jauch