[Module] Date range picker

by David Workman
Data Mosaic

Searching on date fields is one of the less intuitive aspects of Servoy. OK, maybe I am being a little too nice here so let me rephrase -- searching on date fields can downright be a pain in the ass for users.

There is no quick shortcut key combination for entering the current date, you have to know to put in the three dots (...) to specify a range (and most of the time the date field is sized to only fit one date so you can't see the whole string you are entering), if the backend field type has a time component (the default) the user has to know to enter the "#" symbol before a single date parameter to find everything for that date, and then there is the plethora of date formatting issues.

As most developers quickly find out, using dates in methods is not a straightforward task either. Javascript's date manipulation functions are a bit archaic and automatic typing apparently doesn't apply when it comes to dates. A string that looks like date won't be recognized as a date unless you specifically change its type to a date.

If all that isn't enough, 95% of the time why are your users searching a date field? They want to find records associated with today, yesterday, tomorrow, this week, last week, next week, a particular month -- quarter -- or even a year. Servoy's date picker is just not up to the task of making it easy for the user to accomplish these common date search tasks necessitating a lot of unwieldy typing on the part of the user.

MODULE: Date range picker

Data Mosaic is releasing a free and unlocked module that you can add to any solution that will give you a date picker with which you can easily choose any day, week, month, quarter or year. All the user has to do is specify which range they want to work with (day, week, month, quarter, year) and the current range is automatically selected. Then there are two controls to move the selection incrementally up or down.

Simple, easy...user pain removed.

Download it here. Instructions and demo on how to use it are included as part of the module.

Servoy bug warning

There is a huge work-around in this code that you will stumble across when you start looking under the hood (see the form "TEST_example_data". You can manually enter date range values (such as "06-01-2007...06-30-2007") into date fields in find mode with no problems. Try to do this assignment in a method and it will fail unless you name the field and then kick it in that pants like this:

controller.loadAllRecords()

var searchDate = date_to_search
var nextDate = new Date(searchDate)
nextDate.setDate(30)
var searchDate = utils.dateFormat(searchDate, "MM-dd-yyyy") + "..." + utils.dateFormat(nextDate, "MM-dd-yyyy")

controller.find()

date_to_search = searchDate

//you need this line for search to work in table, list and record view
elements.fld_date_to_search.requestFocus()

//you need this line to work in list and record view
elements.fld_date_to_search.replaceSelectedText("")

controller.search()

In Servoy 2.2.7 you could only assign a date range methodically to a date field in find mode on a form in table view. At least with version 3.5 our bugs are getting more consistent :) So I always used SQL queries to perform date range searches which made it too much work to make available a general release module such as this.

Code

There are three main pieces of code:

1. when you choose a date range the current range is automatically populated
2. incrementally moving a range up
3. incrementally moving a range down

These three methods are listed below. Note that there are many situations where you can use these concepts in your coding. At the very least, if you need some help on how to use dates then stepping through the code in this module will be extremely useful.

If you have any feedback, let me know!

METHOD: ACTION_range_set

//**********************************************************************
//
//    METHOD:     ACTION_range_set
//    PURPOSE:    choose day, week, month, quarter or year to filter by
//
//**********************************************************************


//set starting date range
switch (globals.MOSAIC_date_range_type) {
    case "Day" :
    
        globals.MOSAIC_date_range_entry = utils.dateFormat(new Date(), "MM-dd-yyyy") + ""
        
        //set tracker for previous and next
        globals.MOSAIC_date_range_track = new Date()
    
        break
            
    case "Week" :
    
        var today = new Date()
        
        var dayNumber = today.getDay()
        
        var weekDayStart = new Date()
        weekDayStart.setDate(today.getDate() - dayNumber)
        
        var weekDayEnd = new Date()
        weekDayEnd.setDate(today.getDate() + (7 - dayNumber))
        
        globals.MOSAIC_date_range_entry = utils.dateFormat(weekDayStart, "MM-dd-yyyy") + "..." + utils.dateFormat(weekDayEnd, "MM-dd-yyyy")
        
        //set tracker for previous and next
        globals.MOSAIC_date_range_track = weekDayStart
        
        
        break
        
    case "Month" :
        
        var monthStart = new Date()
        monthStart.setDate(1)

        var monthEnd = new Date(monthStart)
        monthEnd.setMonth(monthEnd.getMonth() + 1)
        monthEnd.setDate(monthEnd.getDate() - 1)
        
        //set the field
        globals.MOSAIC_date_range_entry = utils.dateFormat(monthStart, "MM-dd-yyyy") + "..." + utils.dateFormat(monthEnd, "MM-dd-yyyy")
        
        //set tracker for previous and next
        globals.MOSAIC_date_range_track = new Date().setDate(1)
        
        break
        
    case "Quarter" :
    
        //setup
        var quarterStart = new Date()
        quarterStart.setDate(1)
        var quarterEnd = null
        var month = quarterStart.getMonth()
        
        //1st quarter
        if (month == 0 || month == 1 || month == 2) {
            quarterStart.setMonth(0)
            quarterEnd = new Date(quarterStart)
            quarterEnd.setMonth(2)
            quarterEnd.setDate(31)        
        }
        //2nd quarter
        else if (month == 3 || month == 4 || month == 5) {
            quarterStart.setMonth(3)
            quarterEnd = new Date(quarterStart)
            quarterEnd.setMonth(5)
            quarterEnd.setDate(30)    
        }
        //3rd quarter
        else if (month == 6 || month == 7 || month == 8) {
            quarterStart.setMonth(6)
            quarterEnd = new Date(quarterStart)
            quarterEnd.setMonth(8)
            quarterEnd.setDate(31)    
        }
        //4th quarter
        else if (month == 9 || month == 10 || month == 11) {
            quarterStart.setMonth(9)
            quarterEnd = new Date(quarterStart)
            quarterEnd.setMonth(11)
            quarterEnd.setDate(31)    
        }
        
        //set the field
        globals.MOSAIC_date_range_entry = utils.dateFormat(quarterStart, "MM-dd-yyyy") + "..." + utils.dateFormat(quarterEnd, "MM-dd-yyyy")

        //set tracker for previous and next
        globals.MOSAIC_date_range_track = quarterStart
    
        break
        
    case "Year" :

        //start
        var yearStart = new Date()
        yearStart.setMonth(0)
        yearStart.setDate(1)
        
        //end
        var yearEnd = new Date(yearStart)
        yearEnd.setMonth(11)
        yearEnd.setDate(31)
        
        //set the field
        globals.MOSAIC_date_range_entry = utils.dateFormat(yearStart, "MM-dd-yyyy") + "..." + utils.dateFormat(yearEnd, "MM-dd-yyyy")
        
        //set tracker for previous and next
        globals.MOSAIC_date_range_track = yearStart        
        
        break
}

METHOD: ACTION_up

//**********************************************************************
//
//    METHOD:     ACTION_down
//    PURPOSE:    increment up by day, week, month, quarter or year
//
//**********************************************************************

switch( globals.MOSAIC_date_range_type ) {

    case "Day"    :
                
                var x = new Date()

                x = utils.dateFormat(globals.MOSAIC_date_range_entry, "MM-dd-yyyy")
                
                var y = x.getDate()
                
                x.setDate(y + 1)
                
                globals.MOSAIC_date_range_entry = utils.dateFormat(x, "MM-dd-yyyy")
                                
                break
                
    case "Week"    :
    
                //week
                var dayNumber = globals.MOSAIC_date_range_track.getDay()
                
                var weekDayStart = globals.MOSAIC_date_range_track
                weekDayStart.setDate((globals.MOSAIC_date_range_track.getDate() + 7) - dayNumber)
                
                var weekDayEnd = globals.MOSAIC_date_range_track
                weekDayEnd.setDate((globals.MOSAIC_date_range_track.getDate() + 7) + (7 - dayNumber))
                
                //set filter value
                globals.MOSAIC_date_range_entry = utils.dateFormat(weekDayStart, "MM-dd-yyyy") + "..." + utils.dateFormat(weekDayEnd, "MM-dd-yyyy")
                
                //set date incrementer
                var myDate = globals.MOSAIC_date_range_track
                myDate.setDate(globals.MOSAIC_date_range_track.getDate() + 7)
                
                globals.MOSAIC_date_range_track = myDate        
                
                break
                
    case "Month":
    
                //start
                var monthStart = new Date(globals.MOSAIC_date_range_track)
                monthStart.setMonth(monthStart.getMonth() + 1)
                
                //end
                var monthEnd = new Date(monthStart)
                monthEnd.setMonth(monthEnd.getMonth() + 1)
                monthEnd.setDate(monthEnd.getDate() - 1)
                                
                //set the search field
                globals.MOSAIC_date_range_entry = utils.dateFormat(monthStart, "MM-dd-yyyy") + "..." + utils.dateFormat(monthEnd, "MM-dd-yyyy")
                
                //set tracker global to use for week prev and next
                globals.MOSAIC_date_range_track = monthStart

                break
                
    case "Quarter" :
                            
                //setup
                var quarterStart = new Date(globals.MOSAIC_date_range_track)
                var quarterEnd = null
                var month = quarterStart.getMonth()
                
                //1st quarter
                if (month == 0 || month == 1 || month == 2) {
                    quarterStart.setMonth(3)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(5)
                    quarterEnd.setDate(30)        
                }
                //2nd quarter
                else if (month == 3 || month == 4 || month == 5) {
                    quarterStart.setMonth(6)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(8)
                    quarterEnd.setDate(31)    
                }
                //3rd quarter
                else if (month == 6 || month == 7 || month == 8) {
                    quarterStart.setMonth(9)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(11)
                    quarterEnd.setDate(31)    
                }
                //4th quarter
                else if (month == 9 || month == 10 || month == 11) {
                    quarterStart.setMonth(0)
                    var year = quarterStart.getYear()
                    quarterStart.setYear(year + 1)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(2)
                    quarterEnd.setDate(31)    
                }
                
                //set the field
                globals.MOSAIC_date_range_entry = utils.dateFormat(quarterStart, "MM-dd-yyyy") + "..." + utils.dateFormat(quarterEnd, "MM-dd-yyyy")
                
                //set counter global
                globals.MOSAIC_date_range_track = quarterStart                
            
                break
                    
    case "Year"     :
    
                //start
                var yearStart = new Date(globals.MOSAIC_date_range_track)
                yearStart.setMonth(0)
                yearStart.setYear(yearStart.getYear() + 1)
                
                //end
                var yearEnd = new Date(yearStart)
                yearEnd.setMonth(11)
                yearEnd.setDate(31)
                
                //set the field
                globals.MOSAIC_date_range_entry = utils.dateFormat(yearStart, "MM-dd-yyyy") + "..." + utils.dateFormat(yearEnd, "MM-dd-yyyy")
                
                //reset counter global
                globals.MOSAIC_date_range_track = yearStart    

                break
}

METHOD: ACTION_down

//**********************************************************************
//
//    METHOD:     ACTION_down
//    PURPOSE:    increment down by day, week, month, quarter or year
//
//**********************************************************************

switch( globals.MOSAIC_date_range_type ) {

    case "Day"    :
                
                var x = new Date()

                x = utils.dateFormat(globals.MOSAIC_date_range_entry, "MM-dd-yyyy")
                
                var y = x.getDate()
                
                x.setDate(y - 1)
                
                globals.MOSAIC_date_range_entry = utils.dateFormat(x, "MM-dd-yyyy")
                                
                break
                
    case "Week"    :
    
                //week
                var dayNumber = globals.MOSAIC_date_range_track.getDay()
                
                var weekDayStart = globals.MOSAIC_date_range_track
                weekDayStart.setDate((globals.MOSAIC_date_range_track.getDate() - 7) - dayNumber)
                
                var weekDayEnd = globals.MOSAIC_date_range_track
                weekDayEnd.setDate((globals.MOSAIC_date_range_track.getDate() - 7) + (7 - dayNumber))
                
                //set filter value
                globals.MOSAIC_date_range_entry = utils.dateFormat(weekDayStart, "MM-dd-yyyy") + "..." + utils.dateFormat(weekDayEnd, "MM-dd-yyyy")
                
                //set date incrementer
                var myDate = globals.MOSAIC_date_range_track
                myDate.setDate(globals.MOSAIC_date_range_track.getDate() - 7)
                
                globals.MOSAIC_date_range_track = myDate                
                
                
                break
                
    case "Month" :
    
                //start
                var monthStart = new Date(globals.MOSAIC_date_range_track)
                monthStart.setMonth(monthStart.getMonth() - 1)
            
                //end
                var monthEnd = new Date(globals.MOSAIC_date_range_track)
                monthEnd.setDate(monthEnd.getDate() - 1)    
                
                //set the search field
                globals.MOSAIC_date_range_entry = utils.dateFormat(monthStart, "MM-dd-yyyy") + "..." + utils.dateFormat(monthEnd, "MM-dd-yyyy")
                
                //set tracker global for next up or down
                globals.MOSAIC_date_range_track = monthStart
    
                break
                
    case "Quarter" :

                //setup
                var quarterStart = new Date(globals.MOSAIC_date_range_track)
                var quarterEnd = null
                var month = quarterStart.getMonth()
                
                //1st quarter
                if (month == 0 || month == 1 || month == 2) {
                    quarterStart.setMonth(9)
                    var year = quarterStart.getYear()
                    quarterStart.setYear(year - 1)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(11)
                    quarterEnd.setDate(31)        
                }
                //2nd quarter
                else if (month == 3 || month == 4 || month == 5) {
                    quarterStart.setMonth(0)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(2)
                    quarterEnd.setDate(31)    
                }
                //3rd quarter
                else if (month == 6 || month == 7 || month == 8) {
                    quarterStart.setMonth(3)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(5)
                    quarterEnd.setDate(30)    
                }
                //4th quarter
                else if (month == 9 || month == 10 || month == 11) {
                    quarterStart.setMonth(6)
                    quarterEnd = new Date(quarterStart)
                    quarterEnd.setMonth(8)
                    quarterEnd.setDate(31)    
                }
                
                //set the field
                globals.MOSAIC_date_range_entry = utils.dateFormat(quarterStart, "MM-dd-yyyy") + "..." + utils.dateFormat(quarterEnd, "MM-dd-yyyy")
                
                //set counter global
                globals.MOSAIC_date_range_track = quarterStart    

                break

    case "Year"        :
    
                //start
                var yearStart = new Date(globals.MOSAIC_date_range_track)
                yearStart.setMonth(0)
                yearStart.setYear(yearStart.getYear() - 1)
                
                //end
                var yearEnd = new Date(yearStart)
                yearEnd.setMonth(11)
                yearEnd.setDate(31)
                
                //set the field
                globals.MOSAIC_date_range_entry = utils.dateFormat(yearStart, "MM-dd-yyyy") + "..." + utils.dateFormat(yearEnd, "MM-dd-yyyy")
                
                //reset counter global
                globals.MOSAIC_date_range_track = yearStart    
                
                break

}