by John Mark Osborne, <jmo@best.com>
Date Range Finds
BONUS: RANGE.FP3
Finding on a date range is easy. Just enter find mode, type in a starting date, type an ellipse (...), type an ending date and then perform the find. For example, to find all the dates between January and March of 1997 type the following into a date while in find mode:
1/1/97...3/31/97
Scripting this same find is not as straightforward.
The first idea that might come to mind is to use the Set Field script command. Unfortunately, Set Field has problems setting fields to values that don't match the formatting of the field. For instance, let's say a script enters find mode and sets a date field to the following formula:
Set Field ["Date Field", "1/1/97...3/31/97"]
The result that showed in the date field would be:
4/11/0004
That's because Set Field attempts to format the result of the formula to the field type and the result is some attempt from FileMaker to make sense of the formula in reference to the format of a date. Even separating the dates and the ellipse into separate Set Field commands will not help.
The only solution is to use one of the pasting script commands like Paste Literal or Paste Result. The reason these commands work is because they handle entry into a field much like typing from the keyboard instead of trying to set the contents of the field at a lower level.
Paste Literal can simply paste the date range mentioned at the beginning of this article while Paste Result requires a more complicated formula but can achieve much more exciting results. The bonus file that comes along with this article is taken from the upcoming Scriptology workbook and CD due out in September from ISO Productions and covers how to perform date ranges based on the current date stored in the computer. This works well for scripts that locate all records within a certain date range of the current date. For instance, let's say a script needs to locate all of the appointments within a certain date range based on the date today. Or, maybe overdue invoices need to be located. The point is that a database solution is more likely to have the need to find date ranges using a date that is variable.
In fact, the bonus file includes scripts that find records with dates less than and greater than the current date. However, the rest of this article will focus on how the date range portion of the bonus file works. Here is the formula from the Date Range script:
DateToText(Status(CurrentDate) - Days Past) & "..." & DateToText(Status(CurrentDate) + Days Future)
The Days Past and Days Future global number fields are not needed but add to the versatility of the formula. They allow the ability to change the number of days in the past and the future that the script will search without modifying the script. This makes it easy for the developer to modify the script but also gives the ability for another script to change the range searched or even for the user to change the range in a preferences area.
Also, notice that the DateToText function is used around each date. If the dates are not converted to text then the act of concatenating them with the ellipse will convert them into numbers representing the dates. The numbers that are pasted represent the number of days since 1/1/0001 and will not work for the date range find.
The rest of the formula is just basic adding, subtracting and concatenation. To see what is pasted into find mode by the script, perform the script and then use the Modify Last Find menu item under the Select menu.
## END ##