by Matt Petrowsky, <mattp@iso-ezine.com>
Finding Dates Older than or Within a Range
A smart method for generating date calculations when searching
BONUS: FRMTDY.FP3
PLATFORM: Mac/Win
Searching for date ranges is an integral part of many FileMaker Pro solutions. If the users of the solution are already FileMaker Pro experts, there's no problem. Experienced users are more than likely to know about the search symbols such as greater than (">"), less than ("<"), and range ("..."). Unfortunately, solutions are usually created expressly for users uninitiated to FileMaker. For these users, a more intuitive system is needed. This article presents just such a system.
There's Something You Should Know
This solution makes use of a few lesser known behaviors of FileMaker Pro. Before we begin, let's review them. When in Find mode, you can enter text into any field. Text entered in Find mode has no effect on data in the database. This allows you to use any field in your database temporarily for utilitarian functions such as making a selection via a pop-up menu. Also remember that a global field is not accessible while in Find mode since it would be pointless to search it. Because of this, when working in Find mode, regular text fields must be used where you would normally use global fields.
Another key piece of information is the fact that a value list can be assigned to a field on a layout by layout basis. A field on one layout can be formatted as a pop-up menu with an associated value list while the same field on another layout can be formatted as a standard data entry field. With this information in hand, we can move on to the next steps.
Putting the Solution Together
To get started, create a new search layout and add the fields that will be searched. You also need to add one additional text field -- pick any text field that exists in your database that will not be used in the search. The field will be formatted as a pop-up menu with the associated value list we will explore below. Next, add two buttons to your layout, a Cancel button and a Continue button. You may choose to format the layout to look like a search dialog box.
Create a script that navigates to this search layout, enters Find mode and sets the text field which is formatted as a pop-up menu to a default value (in our example, it will be "Any"). The important concept here is the use of the additional text field to capture a selection made from the pop-up menu and then clear the pop-up. The field is used to capture a selection that the user makes ONLY while in Find mode.
After the user has been taken to this search layout, the script is set to pause and wait for the user to input search criteria, including making a selection from the pop-up menu. The value list is as follows:
Any
-
Older than 1 week
Older than 2 weeks
Older than 3 weeks
-
Older than 1 month
Older than 2 months
Older than 3 months
Older than 4 months
Older than 5 months
Older than 6 months
-
Older than 1 year
Older than 2 years
Older than 3 years
-
Within 1 week
Within 2 weeks
Within 3 weeks
-
Within 1 month
Within 2 months
Within 3 months
Within 4 months
-
Within 1 year
Within 2 years
Within 3 years
Let's examine the solution using a particular search -- find all invoices "Within 2 months" of the current date. The value list is constructed using a particular pattern. This pattern allows a single script, Set Date Range, to automatically build the search criteria from the selected value.
First, notice that the information we want can be found in the last 2 words of each line. Using our example,
RightWords(Popup field, 2) = 2 months
Now we can use the Left function to pull from our RightWords function result. Our new function gives us the desired results:
Left(RightWords(Popup field,2),1) = 2
So, Is It Weeks, Months or Years?
The next part is both easy and fun. If you haven't used the PatternCount function, then get ready to. The PatternCount function identifies whether the pop-up selection has the word "week", "month" or "year" and also checks whether it has the word "Older" or "Within". This makes our script very flexible.
Let's say the selection was "Within 2 years". Our number function gives us 2 and PatternCount gives us a count of 1 for the word "year". One year equals 365 days so we multiply 365 times 2 to get the number of days to subtract from Status(CurrentDate). What remains is to find out whether we need to use greater than or less than. Using PatternCount again results in 1 for the word "Within" so we know we use the greater than symbol (">") for our search symbol.
This calculation is the heart of the Set Date Range script. Note the inclusion of the DateToText function; to perform these calculations, you subtract a number from a date and then must convert the resulting date to text for later pasting.
Case(
PatternCount(Contact, "Within"), "> ",
PatternCount(Contact, "Older"), "< ")
&
DateToText(Status(CurrentDate) - Left(RightWords(Contact, 2), 1)
*
Case(
PatternCount(Contact, "week"), 7,
PatternCount(Contact, "month"), 31,
PatternCount(Contact, "year"), 365
)
)
The default selection of the pop-up menu is set to "Any". Since the PatternCount function only watches for "Within" or "Older", the word "Any" means that the function will default with a null value of "" (empty text). This means that if no selection is made from the pop-up menu, then no date range is pasted into the target date field. The database will only search for those values that the user inputs into the search layout.
The Final Step
The calculation above is used in a Paste Result script step to set a target date field to the appropriate FileMaker find criteria. In our example file, we search the Creation Date field. The script looks at a selection made in the pop-up menu and creates the date range we need to use for the search. As soon as the paused script is resumed, we clear out the pop-up menu because it's not a field that we really want to search. We only used the field temporarily to aid the user in making a selection from a pop-up menu.
Broken down, the process is quite simple:
1. Enter into Find mode
2. Go to the special Find layout
3. Set the text field that will serve as your pop-up menu to have the "Any" option
4. Pause and wait for the user to input search criteria
5. Use the Set Date Range script to determine the date range to paste into the target date field (if the setting is "Any" then no date range criteria is set)
6. Since the text field with pop-up menu was used only temporarily, clear it
7. Perform the find with the specified criteria
One script and one pop-up menu with an associated value list is all that was used. It wasn't even necessary to add a field for this operation since you are using a field already in your database.
A Portable, Powerful and Expandable Solution
The two brief scripts and custom find layout can be added to any solution. The formula used in the Set Date Range script and the special value list can be copied from the bonus file. To expand the calculation to handle other values, remember the way we used the Left and RightWords functions. Multiply by 7 for weeks, 30 or 31 for months and 365 for years.
This method is really powerful when incorporated into your databases. Copy and paste make it easy to incorporate and, the power that it provides to the users of the database is great. Take the bonus file apart to get the hands-on experience. You will quickly see how useful this technique is!
## END ##