The Secret Life of Find Mode: Symbols

Leave a comment

8-20-2007 by Geoff Coffey

Once you’ve mastered multiple requests, you’re ready to move on to the next Find Mode gem: Find Symbols. Using these bits of punctuation, you can tell FileMaker, when it goes about matching records to your find criteria, to be a little more flexible, or a little more strict. In this article, you’ll learn about each one.

Note: This article is part of our series on Find mode. Click here to visit the first in the series, which links to all the others.

Find Symbols are special punctuation you type right in to Find mode. FileMaker doesn’t just match these symbols directly. Instead, they serve as hints to FileMaker about how you want things to match. Luckily, you don’t have to just know what these symbols are. You can see them all in the status area when you’re in Find mode:

FileMaker’s find symbols are listed in a pop-up menu in the status are when you’re in Find mode.

The Symbols pop-up menu lists every valid Find mode symbol, along with a description of what it does. Chances are you’ve used a few of these before, even if you didn’t know they were called Symbols. For instance, most FileMaker users know you can put something like this in a date field in Find mode:

8/11/1976...12/29/2001

When you do, your found set will include all records whose date value is between August 11th 1976 and December 29th, 2001.

The Range Symbol

As you just saw, the ... symbol — better known as the “range” symbol — lets you specify a range of values to match. It isn’t limited to dates. You can provide a range of numbers in a number field:

22...36

Or a range of times:

9:00 AM...5:00 PM

You can even provide a range in a text field:

Adam...Bernard

FileMaker thinks about text ranges alphabetically. In other words, if you put all your names in alphabetical order, the range above would include every name between Adam and Bernard.

It is important to remember that the range symbol is inclusive: A value that matches either end exactly will be included in the found set.

Tip: It’s a small concession, but if you’re keystroke-conscious, you can trim one . from your Range symbols. It is perfectly valid to use just two dots: 3..7

Comparison Symbols

The next-most-common symbols are <, ≤, ≥, and >. These symbols are like the range symbol with only one end. For example, to find everyone born after Star Wars came out:

>5/25/1977

These symbols are pretty self explanatory: “Greater than 5/25/1977.” Some people prefer using the range symbol instead of ≤ or ≥. For example, this will find every order placed after January first:

1/1/2007...

Note: On Mac OS X, the “less than or equal” symbol looks like this: ≤. On Windows, it looks like this instead: <=. But both versions have the exact same meaning. (And in fact, the Windows style symbol will work perfectly on the Mac too.) The same goes for “greater than or equal.”

Exact Match

One of the most useful symbols is =, or the “exact match” symbol. Normally, FileMaker is pretty forgiving about field matches when performing a find. For instance, if you put San in the City field, FileMaker will consider each of these a match:

  • San Francisco
  • Santa Clara

If you want to be technical, FileMaker does a word-based prefix match, meaning if any word in the field starts with the value you type in your find request, FileMaker considers it a match. The “exact match” symbol lets you be more specific. If you use this find criterion:

=San

FileMaker will still match San Francisco but it won’t match Santa Clara. With =, it only matches whole words.

Field Content Match

If you want to be even more specific, you can use the “field content match” symbol: ==. This little buddy tells FileMaker you know exactly what you’re looking for, and it only finds records where the entire field matches your find criterion exactly.

For example, if you did your San search this way:

==San

Then you’d only find people in the city of San. (More likely you’d find nothing, since San isn’t a city.) You would use this symbol when you are looking for something very specific and a normal search finds too many records.

Wildcards

Going the other direction, sometimes you want FileMaker to be less restrictive. FileMaker has three so-called wildcard symbols:

  • *: zero or more characters
  • @: one character
  • #: one digit

These symbols stand in for data in the field. For example, if you want Cities that end with “son” you can do a search like this:

*son

That search will find the mythical city of Son (sister to the city of San), as well as:

  • Tucson
  • Wilson
  • Orson

Note: As soon as you add a wildcard symbol, FileMaker starts doing an exact match as well. In other words, the whole word prefix mumbo jumbo no longer applies. *son will not match Wansonsy.

The * symbol will match any number of letters or numbers. It will even match no characters (that’s why *son finds the city of Son). The @ symbol, on the other hand, matches exactly one character. Try a search like this:

Sant@

And FileMaker will find:

  • Santa Clara
  • Santo Domingo

But it won’t find “Sant” because the @ symbol expects exactly one character (not zero or one).

Tip: Sometimes these symbols aren’t flexible enough. For example, you might want “one or more characters.” You can combine @ and * to get what you want: San@*. That criterion will find “San” followed by at least one character, and possibly more. If you want “zero or one character” your job gets a little tougher. Your best bet is to use two find requests. In the first, put =San and in the second, put San@. The first request will match every record with “San” and the second will match “Sans”, “Sand”, and “Sang.”

Finally, the # symbol works like @ but is only matches a numerical digit. This find:

##

Will match only two-digit numbers.

Literal Text

All these special symbols might leave you feeling a bit worried: What if you don’t want to discombobulate FileMaker’s finding. Instead, you actually want to find an @ sign? Believe it or not, another symbol comes to the rescue: The “literal text” symbol. Just put your punctuated find criteria in quotes:

“mr_magoo@sixfriedrice.com”

When FileMaker sees those quote marks, it dutifully ignores the symbols inside. In fact, this is just what you should do if you’re searching for an email address.

If @ means “any character” then what does it matter? The @ in mr_magoo@sixfriedrice.com will happily match the @ sign in the email address, won’t it? Unfortunately, no. The @ sign (when in regular field data) is not indexed. In other words, FileMaker tosses it out when it takes its speed-search notes about your records. So to FileMaker, the email address “mr_magoo@sixfriedrice.com” is really three words: “mr”, “magoo”, and “sixfriedrice.com” So there’s nothing in there for the @ symbol to match. You can prove this yourself: When you need to find an email address, just put a space in place of the @ sign. It will find without a hitch.

Duplicates

The most esoteric find symbol is !: the “duplicates” symbol. You don’t put this next to any text. Instead, you put it in a field all by itself in Find mode. FileMaker will then find every record that has duplicates in that field. In other words, if your database has two Andy’s and no other duplicate first names, if you perform a find with ! in the First Name field, FileMaker finds both Andy’s but leaves everyone else out. If you later add a second Vincenzo, it will find both Andy’s and both Vincenzo’s.

Note: This helps you find duplicate records. If you want help getting rid of them, see this article.

Today’s Date

The // symbol is really just shorthand to save you some typing. (Truthfully, it is also useful when scripting, but we’ll get to that in a future article.) When you put // in a find request, FileMaker acts just like you had meticulously typed out today’s date. To find every invoice due today, just bounce to Find mode, pound // in the Due Date field, and press Enter.

You can, of course, combine this symbol with others:

# find every invoice due after today
>//

# find every event between now and christmas
//..12/25/2007

Invalid Date or Time

The last symbol is ?. It’s another funny one. Sometimes (if you import data from some other system, or change field types) you might wind up with bogus data in your date field. For example, Microsoft Excel is glad to let you type “sometime next week” in a column that normally holds dates. If you then import this into a date field, FileMaker accepts it (what else is it going to do?) but considers the value invalid. Later, you can switch to Find mode, put ? in the date field, and perform the find. FileMaker finds every record with an invalid date like this.

The ? symbol works for time and timestamp fields as well.

A Few Examples

Find symbols are flexible and powerful. Chances are you’ll dream up ways to use them that I’ve never thought of. But here are a few examples that might come in handy:

If you have a Full Name field and you want to find everyone with an initial, try this:

@

It will find exactly one character. Since periods aren’t indexed, it will find all of these:

  • J. K. Rowling
  • Charlotte A. Cavatica
  • A. A. Milne

To quickly find everything up to today, put this in a date field:

..//

Likewise, if you want everything today or later try this:

//..

If you’re using our delete duplicates technique, you can make your script run faster if you find the duplicates first. Put ! in each of the match fields, and FileMaker will find only the records the script actually cares about.

If 37Signals is one of your customers, but you can’t remember if it’s 37signals or 73signals (or 737signals for that matter) search for this:

#*signals

We’ll see you soon for our next article on Find mode. Until then, enjoy your new found symbol knowledge.

21 Comments

  1. Florence Ross

    Well done!
    I’ve used Filemaker for years and hadn’t made good use of the new search capabilities as they evolved over the years. (I’ve been around since Filemaker II). Thanks for this. I’ll do a session in our office for the personnel that use our database daily in their work.

  2. Darryl Oliver

    One of the consequences of the alpha wildcard symbol (@) is that you can’t do a literal search for an email address. FileMaker will interpret an email address as an invalid Find request.

    Although you can search by entering the text on either side of the @, it’s a little disappointing that you can’t just paste an email address into a field in FileMaker’s Find mode and perform a search. You have to insert a backslash (\) before the @ in order to have FileMaker accept the email address in a Find request.

    I developed a script as a workaround if anyone is interested.

  3. Geoff Coffey

    @darryl: It is a little annoying. I’m pretty sure the “@” symbol in FileMaker predates prevalent use of email addresses, so I give FMI a pass here :)

    There are several ways to work around it. The article mentions two: put the address in quotes: "mr_magoo@sixfriedrice.com" or put a space instead of the @ sign. Your method will work too: \@. Finally, you can use the exact match operator: ==mr_magoo@sixfriedrice.com. If your sample script is available on line, send me the URL (use the Contact Us form on this site) and I’ll add it to your comment.

    Thanks!

  4. Montana

    @ Darryl Oliver

    >> I developed a script as a workaround if anyone is interested.

    Yes please.

    TIA

  5. [...] Find symbols [...]

  6. Mike

    When interacting with FM via XML/PHP/etc the @ needs to be escaped, then encoded, so your final query string looks something like user%5C%40company.com

  7. Suzanne

    For some reason filemaker is not being very forgiving on searches except with I use the *symbol. For instance, I am trying to look up any jobs that have the numbers 250 in it, I HAVE to type *250 to bring up 000250 or *250* to get 0025010.

    Is there a way to set up my find for all fields to be relaxed without having to use the asterisks?

  8. Geoff Coffey

    Suzanne:

    FileMaker’s symbol-less behavior is to search for “words” in the field that start with your criteria. So in a text field, 000250 would not be found by searching for just 250, as you have discovered.

    There’s no direct way I know of to change this automatic behavior, but there are several possibilities you might consider:

    • You could use a script that goes to find mode, pauses, then puts a “*” in front of the job number if the field isn’t empty. It would finish by performing the find. Then, you would run this script rather than using Find mode directly (via a button or even by overriding the Find menu command and keyboard shortcut with custom menus). The mechanism will ensure that the field gets fixed up before the find occurs.
    • If you search by job number often, you could write a script just for that purpose. If would use the Show Custom Dialog script step to ask for the job number, then go to find mode, put the search value in place, complete with *, and perform the find. You could then run this script to quickly look up a job number.
    • Some people like to build a calculation field that is based on the job number field, but includes several versions of it, like this:
      
      0
      50
      250
      0250
      00250
      000250

      Then you search that field instead of the normal job number field. I don’t like this kind of thing much, but some people swear by it.

    There are probably lots of other ways to streamline your job number search…

    Geoff

  9. Nazar

    Nice. Helpful. Yet, I have a related question:

    Is there any way to easily do a search that compares values in two fields and displays records where they match? For example, I have a database that contains two price fields. Let’s call them price1 and price2. I want to perform a find that results in all records where price1=price2.

    Anyone know how to do this?

  10. Jim

    How do I do a find and exclude a word? for example, I want to a search for “Dog” and exclude the word “Cat” ??? Help, drivng me nuts bendorguy@yahoo.com

  11. Geoff Coffey

    Jim:

    You can do this with two find requests:

    1: Enter Find Mode
    2: Put “dog” in your field
    3: Choose Requests => New Request
    4: Put “cat” in the same field that had dog before
    5: Click the “Omit” checkbox or button in the status toolbar

    When you run this find, it will find records with dog, but not cat. See http://sixfriedrice.com/wp/the-secret-life-of-find-mode-omit/ for more.

    Geoff

  12. Timothy Moser

    You say that the field content match (‘==’) “only finds records where the entire field matches your find criterion exactly”. This is true when the only special characters in the field are “?”, wildcards, and so on. However, when the field actually contains quotation marks (“”), I get a “no records found” when I search with field content match. In this case I have to use the backslash (\) before each quote mark.

  13. Svend

    Weirdly, one character that the “@” symbol doesn’t seem to match is itself — we were wanting to allow our users to say “@username” in a notes field, but if I have a note that says:

    Try this out, @svend!

    then searching for:

    @svend

    doesn’t work, though searching for the literal:

    “@svend”

    does.

    This is in FM11.0v2, though; it might not happen with earlier versions.

  14. Geoff Coffey

    Svend:

    This does seem weird at first, but here’s what’s really happening. FileMaker builds an index of the values in a field to make searching faster. Only certain characters are included in this index (all the letters and numbers, and some punctuation). The @ symbol is not indexed. So when you search for @something FileMaker looks in the index for {some letter}something and doesn’t find it because the index just has something (the @ was stripped).

    When you do a literal text search with quotation marks, FileMaker alters its matching behavior to include special characters.

    The exact characters that are included in the index depend on the default language you set in the storage options for a field. If you set this to “Unicode” then the @ symbol will be indexed (along with just about everything else). But this has the unfortunate side effect of making the index case-preserving too, so your searches will be case sensitive.

    And now I’ve said more than you ever wanted to know :)

    Geoff

  15. Adam

    I’m having a heck of a time doing something that seems it should be simple, but I can’t find a way to do it in FMP 9. I want to create a script that finds the content of a global field, and then omits records from that set. I have a global text field that’s defining my “current project”. I simply want to find all record where the field “project” matches “current project” and omit records where the field done = “done”. I can’t find any way to, within the scripting interface or the find interface, to set the criteria to be another field. If I make a complicated script that copies the content of the global field “current project”, then enters find mode, then pastes the contents into the “project” field, I can make (in find mode) a new record/request and set the “done” field to match the criteria “done”, but then I can’t figure out how mark this request as “omit”. The docs are not helpful on this. Suggestions?

  16. Geoff Coffey

    Adam:

    This is a common source of confusion. You can only use the Perform Find step all by itself if your find is based on static data, but in your case, you want to find based on data from a global field. This requires a little more scripting. (Note that in FileMaker 11, it is possible to use a variable in a find request, which is another way to do what you want. But I find it more confusing, and anyway, since I don’t know what version of FileMaker you’re using, I’ll show you the way that will work in any version.)

    As you guessed, the right approach is for your script to do these steps explicitly:

    1: Enter find mode
    2: Set up the find request(s)
    3: Perform the find

    To turn on the “omit” checkbox for a find request, you simply run the “Omit Records” script step. When you’re in browse mode, this step actually removes records from the found set. But in find mode, it toggles the Omit checkbox.

    Also, you should generally avoid using copy/paste in your scripts because it modifies the user’s clipboard, which can be irritating for your users. When trying to put data into a field in a script, you almost always want the Set Field script step instead. Since it doesn’t make sense to search global fields, you will have full access to your global field while in find mode.

    So here is a script that will do what you want:

    Enter Find Mode
    Set Field [project, current project]
    New Record/Request
    Omit Record
    Set Field [done, done]
    Perform Find []

    That should do the trick.

    Geoff

  17. Philip

    I want to be able to find a single digit in a field of numbers separated by commas. The field could have 2,3,8 or 2, 3, 8 In the latter example searching for 3 will return a record with that combination. But it will not return the first record because there are no spaces between the comma and the next number. Is there a way around that, short of going through all 450 record and entering spaces between the commas and numbers?

  18. Dean

    Brilliant. Very easy to understand. Thanks. D.

  19. Tori

    I’m wanting to write a script that will be able to search a year and sort through the files that can give me the statistics of each Quarter. For example if I searched 2007:
    Winter:
    Enrolled:309
    Dropped:25
    Cancelled:3
    Re-Enter:15

    Spring:
    Enrolled:150
    Dropped:9
    Cancelled:1
    Re-Enter:10

    Summer: Ect…

  20. Peter Beehler

    I often store email field as ‘unicode’, under field definitions storage options. This let’s users search the field without having to explain or script the behavior in find mode to handle the @ symbol.

    Anyone know of any con to doing storing it as unicode?

  21. Geoff Coffey

    Peter: The only downside to that is that your search is notnow case sensitive. For something like an email field that may be no big deal, but it can be a problem in some cases.

Tell Us What You Think

*
* (will not be published)