« [Article] Getting data from Filemaker to Servoy | Main | [News] Apple's iPhone to feature Servoy 3.5 »
June 28, 2007
[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:
- when you choose a date range the current range is automatically populated
- incrementally moving a range up
- 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
}
| Posted by David Workman on June 28, 2007 at 03:17 AM in Module | Permalink
Comments
All I can say is "Wow!" Thnaks for sharing this with the Servoy community, David. Your generosity is much appreciated. The fact that it doubles up as a great learning tool is a big bonus.
Quick question for the Abstraction King ;~): Since getday() gives you 0 on Sunday ... 6 on Saturday, how would you abstract the code so that the week range could start on a Monday instead of a Sunday?
Thanks again,
Ben
Posted by: Ben Savignac | Jun 29, 2007 5:45:26 AM
Not so king of abstraction this time around as I knew half the world would want this! Should have made a preference panel.
Fix: in the three methods above where the week comes into play, decrement the variable "dayNumber" by one when it gets initialized and that should do it.
In another not-so-abstracted note: I assumed the date format of "MM-dd-yyyy" throughout. Since 90% of the world in this case won't be using this date format I could have done better here too :( Nothing a find and replace won't cure.
Posted by: David Workman | Jun 29, 2007 9:14:16 AM
