PDF Estimated Time To Read This: 4 – 7 minutes
Up until now, there have been very few options for searching dates and times in FileMaker Pro. The user could search for a specific date or time (e.g. 1/15/2006 or 12:15 PM), or they could search for a range of dates or times (1/1/2006…1/31/2006). However, FileMaker now allows the user to take advantage of several new search options, which makes searching much easier.
Before covering the new search options for searching dates and times, we will cover some of the options available to searching in general.
FileMaker Search Options
Wildcard characters allow the user to search for text with one or more variables or unknown characters. The character @ lets you search for one (and exactly one) unknown character. For example, c@t would find cat or cut, but not caught.
Using the character * lets you search for 0 or more unknown characters. Searching for b*t would find bit, bat or bought (words stating with b and ending with t, with any number of characters in between).
Using # works similar to @ as it lets you find one (and exactly one) character, but is limited to numbers. For example, # would find a single digit (3, but not 30 or any single alpha character); ## would find a two digit number (30, but not 3 or 300 or any two alpha characters).
To search for a specific phrase, you would surround the text with quotes. For example, “truth and justice” would find any text containing “truth and justice”, but not “justice and truth”.
Searching with a question mark (?) will find any invalid characters (for example, text in a number field).
To perform an exact match, you can put == or = in front of the search criteria. Using == finds the text (and only the text) in the search criteria. So searching for ==John Smith in a field would only find records where that field had the text “John Smith” in it; records with the text “John” or “John Smith III” would not be found.
Searching with a single equal sign in front of a word would find any records where the field contains that word. Searching for =engineer would find any record where the text contains the word engineer, but not the words engineering or engineers.
To search for special characters, symbols and other non-alphanumeric characters, there are two options. One is to surround the character in quotes (for example, johnsmith”@”xyz.org would find johnsmith@xyz.org). The other is to use a backslash in front of the character (for example, johnsmith\@xyz.org would find johnsmith@xyz.org).
Searching for Dates, Times and Timestamps
Users can now take advantage of wildcards (mentioned above) when searching for dates and times. By replacing any part of the date with an asterisk (*), you can find any dates that match the other parts of the date. For example, by searching for 5/31/*, you can find any date that falls on May 31st, for any year.
By taking advantage of this, some searches (such as finding any record in a specific month, or all records for a given year) can be done without using date ranges. So, to search for all records in the year 2006, the user can search for */2006 (instead of searching on the range 1/1/2006…12/31/2006, which is how it had to be done in previous versions). Searching for records in May of this year would be done by searching on 5/* (for May of any year, it would be 5/*/*). This also works for times; so 7:* would find any records from 7:00 to 7:59. Timestamps work a similar way, so // 5:* would find any records for today from 5:00 to 5:59.
Similar results can be achieved by leaving part of the date or time blank, rather than using a wildcard. So searching for 2006 in a date will find all records for the year 2006. Searching for 12 in a date will find all records in December of the current year. Searching for 12/25 will find all records for December 25th of the current year. Note that when choosing Modify Last Find, FileMaker will have filled in anything that was left out of the search criteria in the previous search – so a search of 12 appears as 12/*/2006 when Modifying the last find.
Another new option is the ability to search by the day of the week. Searching for Monday will find any records that fall on a Monday. This can be combined with the search options above – so, searching for Monday 12/* will find all records falling on Monday in December of the current year; searching for Friday 2006 will find all records that fall on a Friday in 2006. Using the first three letters of the day of the week will produce the same result, so Fri 2006 works the same as Friday 2006 – after choosing Modify Last Find, the short form is always used.
If you are viewing a record with a field value that you want to use to search other records, you can use the new Find Matching feature. Assuming you are viewing a record in Browse mode with a last name of Brown and you want to search the table’s last name field for the word Brown, right-click (or control-click on a Macintosh) in the field and select Find Matching Records. A search is performed as if you had entered Find mode and entered Brown in the last name field.
Note that for a solution hosted by FileMaker Server, these new search abilities will only work if the files are hosted using FileMaker Server 8.0. For more information refer to Knowledgebase article 5376.
With these new options, users have much greater flexibility when searching on date fields. Searches that previously required a date range, or more complicated calculations and scripts, can now be done with a fairly simple search using wildcard characters.
