Sunday, October 29, 2017

Filemaker Finding a Range of Dates

Finding a range of dates in Filemaker is not new. It is available in Filemaker Helps but since it is quite unusual, it is worth repeating.

To find a date range, you need to enter Find mode. At the date field enter the date range like .. then press enter. Note that the date entered is the system date format not the displayed date format as the display date format may not be the same as system date format.

To do a date range find in Script is slightly more complicated. You need to create two date type global fields.  These global fields is for user to enter the search date range.

First enter a script step to start find without definding the find requests. Set the pause off. Then set variable to the field with the two global fields as start and end date like below

Enter Find Mode []
Insert Calculated Result [Select; mytable::mydatefield; mytable::GlobalStartDate &"..." & mytable::GlobalEndDate]
Perform Find

There are instances where your date range is not taken from user's input. In that case you have to modify the script a bit.  Get rid of the 2 global fields and create two variables that contains date values. Here I use static date values. You can use variables to define the date values.

Set Variable [$startDate; Date( 8; 1; 2017)]
Set Variable [$endDate; Date( 8; 31; 2017)]
Enter Find Mode []
Insert Calculated Result [Select; mytable::mydatefield; $startDate &"..." & $endDate]
Perform Find

Normally the date range is by month. It is easy to modify the above script to search by month. 

Set Variable [$startDate; Date( 8; 1; 2017)]
Set Variable [$endDate; Date( 8+1; 0; 2017)]
Enter Find Mode []
Insert Calculated Result [Select; mytable::mydatefield; $startDate &"..." & $endDate]
Perform Find






No comments:

Post a Comment