The Secret Life of Find Mode: Dates and Times

Date values (and to a lesser extent, time values) are exceptionally common in database applications. And it isn’t at all unusual to want to ask you database interesting date-related questions, like “How many orders do we get on Tuesdays?” or “What was our total enrollment for the first quarter?” In fact, it is probably more common to look at ranges of dates than any particular date. Of course, if you’ve read our [previous articles](http://sixfriedrice.com/wp/the-secret-life-of-find-mode-requests/) on Find mode, you know how to find whole ranges of dates easily using find symbols. But FileMaker Pro’s Find mode has several date-specific tricks up its sleeve. Using some not-so-obvious syntax, you can easily search for *date slices* like every tuesday, or the 5th of every month, or every January. This short article will explain how.

> Note: This article is part of our series on Find mode. [Click here](http://sixfriedrice.com/wp/the-secret-life-of-find-mode-requests/) to visit the *first* in the series, which links to all the others.

It stands in stark contrast to the FileMaker norm that none of the tips in this article could possibly be *discovered* by a casual FileMaker user. Nowhere in its status area, menus, or dialog boxes does FileMaker *show* you how to do any of these things. I only know they exist because Jay Welshofer, the product manager for FileMaker Pro 8, told me about them when I was working on [*FileMaker Pro 8: This Missing Manual*](http://www.amazon.com/FileMaker-Pro-8-Missing-Manual/dp/0596005792/ref=pd_bbs_sr_1/104-3600590-0726315?ie=UTF8&s=books&qid=1185335181&sr=8-1). Not that they’re a big secret: Lots of people know how to do these things. But if you’re new to FileMaker, you probably don’t. And that’s a shame, because these hidden Find mode tricks are *super cool*.

> Note: All the techniques in this article will only work with FileMaker Pro 8.0 or later. If you’re using 7, you’ll have to skip this party.

## Abbreviate

When you’re entering dates in *Browse mode* you have to follow FileMaker’s date rules to the tee. But in *Find mode* you can cut corners. To wit:

If you want to find every event scheduled for August, 2007, don’t bother with all this:

8/1/2007…8/31/2007

You can just do this instead:

8/2007

Likewise, if it’s the whole year you’re after, just do this:

2007

FileMaker will find any date in 2007.

The same goes for time values. If you want to find orders that came in after lunch, use this:

12pm…

## A Slice in Time

The examples above are really just time savers. You can accomplish any of them the long way with various find symbols and a lot of typing. But try this on for size: You want to find every order that was placed on a Tuesday. That’s a seriously hard find to do…or is it? Just put `Tuesday` in a date field in Find mode and FileMaker gives you just what you want. To save time, you can abbreviate instead: `Tue`.

This day-name power can be used with ranges too:

Mon…Fri

That will find all the weekday sales.

If you need a more monthly perspective, you’re still in luck. You can find every trouble ticket that came in on a pay day:

*/15/*

That find will match the 15th of any month, on any year. In fact, you can use the * notation in any way that makes sense. If you’re looking for Christmas day clock-ins, try this search:

12/25/*

It will find Christmas no matter the year.

Finally, if * is too liberal, you can use ranges in any segment of the date. This will find bookings between the 4th and 15th:

3/{4…15}/2007

Of course, you can combine `*` and the `{…}` ranges as you see fit. How about finding every first quarter sale in history:

{1…3}/*/*

## Times and Timestamps

The same powers apply to time values. Each of these is perfectly valid:

# fifteen minutes past the hour
*:15

# closing time
{3…4}:* pm

And of course what applies to dates and times also applies to timestamps. Why not find every timeclock record from the fourth quarter that falls between 5:00 and 6:00 pm on a pay day?

{10…12}/15/* 5pm

Timestamp values are a little persnickety, though. If you use a day name (`Monday` or `Mon`, for instance) FileMaker has a habit of shoving the current date in the mix, which throws things off. To compensate, add `*/*/*` after your day name, to insist on *any* date that falls on that day. For instance, why not dig up all the Friday afternoon early punch-outs:

Fri */*/* {3…4}:* pm

These tricks, however hidden, are amazingly powerful. If you don’t know they’re there, you can spend *a lot* of time and energy scripting and calculating your way around these sorts of queries.

Leave a Comment

16 thoughts on “The Secret Life of Find Mode: Dates and Times”

  1. Your series is useful, but I find the section on dates *very* confusing, because all the dates are in American.
    It might be helpful to add a section explaining the notation in standard date format (eg dd/mm/yyyy).
    I presume FM refers to its preference file to correctly parse the date field
    (and therefore know how to interpret various ranges)?

  2. @roman: While I question your use of the word “standard” I understand what you’re saying 🙂

    Luckily, the concept is really quite simple. Whatever date format your computer is configured to use, just put a “*” in any one or more of the date segments. FileMaker will treat this as a wildcard. So, for instance, if your date format is “day/month/year” and you search for 4/*/2007 you’ll find the fourth of every month in 2007.

    If you want to be a little more restrictive, put a range inside { and } instead. This will find the fourth of every month in the first quarter: 4/{1..3}/2007.

    Hope that makes sense.

  3. I am still a novice to using FileMaker, having only used it on and off for some simple applications. I have been reading your articles here on finding. I have now been working on a database [a personal budget and transactions, database] where I have a need to find the last transaction, lets call it ‘max date’, in a particular month. I have tried a few ideas which fail. How could I do this?
    Thanks in advance. I will check back to see if there is an answer posted.

  4. @mark: I think the situation you describe calls for scripting. I don’t believe there’s a way to do it with a simple Find without adding unnecessary clutter to your table (and possibly dealing with poor performance).

    Luckily, it’s easy to do with a script. You will take three steps:

    1. Find the transactions for a particular month.
    2. Locate the last of the found transactions.
    3. Remove all the others from your found set.

    The find portion is fairly simple, using the techniques describe in this article. To find transactions in March, 2007, search your Transaction Date field for 3/*/2007.

    To locate the most recent of the transactions, just sort them descending by creation timestamp or transaction date, or whatever is appropriate.

    Finally, you can use the Omit Multiple command to remove the older transactions from the found set.

    Here’s a sample script that does these things. This version prompts the user to enter a month and year, but it could be written to use a script parameter, hard coded date, or whatever is appropriate for your situation.

    Show Custom Dialog [ "Month"; "Enter the month and year to find."; My Table::Global Month; My Table::Global Year ]
Enter Find Mode
Set Field [Transaction Date; "My Table::Global Month & "/*/" & My Table::Global Year]
Set Error Capture [On]
Perform Find
Set Error Capture [Off]
Sort Records [Restore, No Dialog]
Go to Record/Request/Page [2]
Omit Multiple [Get(FoundCount) - 1]

    If you run this script, or a variant of it, you’ll see that FileMaker carries out all the steps very quickly, and shows your results.

    Hope this helps.

  5. Slow to reply to your answer as I have been rather ill. Thanks very much. Right on the ‘mark’ for me. This will be very useful.

  6. This is great – thanks for the valuable community service!

    Is there a way to pull any record dated/changed in the *current* month. So if I ran a report on January 12th, it would give me Jan 1 > 12 (of the current year)…and if I ran the same report on January 31st, it would give me the entire month…

    If the above is not possible, I could always pull it by all values in the past 31 days. I’m trying to avoid this, as a report ran on Feb.28 gives me some of January.

    Bear in mind, this list-pull will be part of a script, that’s connected to a button. Some folks that I work with who don’t know how to use FM at all will use this script by pressing the button that automates it.

    Any ideas on how to get these results, without them having to type in anything?

  7. @wade: You can use // for the current date and ..// for everything up to the current date. But I don’t know of a shortcut for “just this month”.

    But in a case like this, you can always revert to the more explicit method. For instance, use a script like this:

    Enter Find Mode []
Set Field [My Date Field, Month(Get(CurrentDate)) & "/" & Year(Get(CurrentDate))]
Perform Find []

    In this example, I use FileMaker’s calculation engine to construct a find that looks like this: 1/2008 which means “Any day in January of this year.”

    Hope this helps 🙂

    Geoff

  8. Good article! My issue is that everything has to work in IWP! I

    I’ve found that in IWP I have to use the Enter Find Mode / Set field method for it to perform finds reliably. When I try to set a field to something like ..//, FM gives me an error [ie. This number could not be evaluated.].

    What I need is to find all records prior to the current date. And like Wade’s, all this has to run by the click of a button. Any ideas on how to do this in IWP?

    Thanks

  9. This is a very helpful discussion. I’ve learned a lot, but I can’t figure out how to find the most recent 7 (or other number) of a series of dated records. Can someone help me with this?

    Thanks so much.

  10. Lano:

    You can do what you want with a sort and an omit:

    1. Sort by the date field in descending order.
    2. Go to the 8th record.
    3. Choose Records -> Omit Multiple
    4. Enter a number bigger than your found set
    5. When FileMaker complains, let it enter the total remaining records for you
    6. Click Omit

    Now you’ll be looking at the seven most recent records. You could script this process too with the Sort and Omit Multiple script steps.

    Geoff

  11. Wow! thank you very much! This works very well. What if I had multiple instances of 1 or more dates? How can I find the most recent of 7 records where the date is unique? Is this possible?

    Thanks again,

    Lano

  12. I used the following script to find the seven most recent records:

    Enter Browse Mode []
    Show All Records
    Sort Records [Restore; No dialog]
    #Sort in descending order
    Go to Record/Request/Page [No dialog;8]
    #Select the 8th record
    Omit Multiple Records [No dialog; Get(FoundCount) – 7

  13. hi, all, i am using file maker pro 10 , i need to write such a script , that will find through a field named call reminder , the script will find in all the records that which records has the todays date, it is kindd like call reminder , i have different field for call time,the script will match the current date and will show the dialog box having the name of clients with time to be called.

  14. Amazing! I found all of my CDs that were recorded in the month of August regardless of year. Now I want to sort that list by Date. Sort seems to only ascend/descend by year. How can I get it to sort by dates (within August, regardless of year)?

  15. You guys at SFR totally rock! You have the answer for everything and always explain things so I understand. Thanks for the date tips and for solving my latest programming dilemma!

  16. Would you please explain how to use the current date as a basis for a Find/Script/whatever? I can’t seem to grasp how to write things like “show me records with a date (in a date field) in the month after the current month” and the like. I’ve easily understood and used your previous tutorials, so I’m looking forward to this information!