FileMaker Find Symbols / Search Operators

From Dwayne Wright PMP
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

When you do a traditional find operation in FileMaker, you simply go into find mode, type what you are looking for in the appropriate fields and perform the find. You have the ability to adjust what you are looking for with the Find Symbols (often called Search Operators). The idea is that you would place a certain character(s) in front of your search settings and your search results are modified. For example, a search of 18 in an age field for a members database could find all the members that age. A search of < 18, would find everyone less than 18. The less than symbol is being used as a search operator. You have the ability to type in a search operator or you can select them from a pop-up menu inside of the status area.

Some of the symbols / operators you can use are ...

The specifics about each search operator would be ...

< Less Than
Finds all the records that are less than what you specified.

≤ Less Than Or Equal
Finds all the records that are less or equal to a value that you specified.

> Greater Than
Finds all the records that are greater than what you specified.

≥ Greater Than Or Equal
Finds all the records that are greater than or equal to a value that you specified.

= Exact Match
Searches for each full word match for each word specified. Without the operator, a search for Bob would return records of Bob Jones, Bob Williams and Billy Bobbett. If you did a search using the exact operator ( = Bob ) then your search would return Bob Jones and Bob Williams. Billy Bobbett would not be found when using the exact match search operator.

... Range
This is used to find the matching records between two values in a particular field. This is most useful when dealing with numbers, date and time fields. If you were to go into Find Mode and type 10...20 as your find setting and execute the find, you would find the records with values between 10 and 20 ( including 10 and 20 ). For example, you might get records with values of 11, 15 and 20.

! Duplicates
This is used to find records that have exactly matching data within the search field. The found set will have all the duplicates ( original and following matches ). A common use is to find duplicate contact records in an address type of database. This can happen via data entry errors or perhaps a recent import of data. You want to make sure you don’t do a find for duplicates and delete the found set. That would delete the original records as well. There are a number of ways to isolate the original record from the duplicates by using relationships and calculation fields.

// Today’s Date
This is a quick and easy way to find records that have a date value that exactly matches the date in the computers memory.

? Invalid Date or Time
By default, FileMaker does not allow a user to enter in invalid data into a date or time field. FileMaker will bark at you if you try. However, you can enter invalid information into a date or time field with scripts or via imports. So we now know that invalid data is possible in these fields. This invalid data can cause a variety of problems with your database solution. So you can use the ? operator to find any invalid data you might have. Then you can work to correct the problem records.

NOTE: You might have seen the ? character in your date fields right after running a script step that sets a date field to “”. You can fix this by using the set script to set the field equal to TextToDate(“”) and the ? will now appear.

@ One Character
This is used to put a wildcard character into one character space that makes up part of a search setting. For example, a search for Mo@ could return Mom, Moe or Mod. This means any character could take the place of the @ character. However, only one character is all that can be replaced. Records with moon, move or moot would not be found because of the one wild character limitation.

* Zero Or More Characters

This is one of your more “taking a blind stab at it” type of search operators. This will find matches for the wildcard for zero or more characters. So a search for Mo* would return matches such as Mo, Moe, Modern and Moomoo.

“””” Literal Text
The Literal Text Match is a search operator used when you are searching for a literal string of text pieces. Say we have a name field and it contains the following data on four different records ...

LeSaux Media Services
LeSaux Media Services, Inc.
Media Services LeSaux
Services Media LeSaux

A standard search for LeSaux would find all four records. A literal search for LeSaux Media Services would find the records of ...

LeSaux Media Services
LeSaux Media Services, Inc.

~ The Relaxed Search
Well, there is a half hour of my life I’ll never get back. I had a devil of a time researching what this search operator meant and couldn’t find mention of it in any of my books, FileMaker support site, online help or anywhere. Then I remember FileMaker puts a copy of the manual in the English Extra’s folder. Opened it up and did a search there. Then I found out, it is a Japanese only feature (that just seems to appear in the English version of FileMaker). Oh Well!

== Field Content Match
This is used for finding exact matches to what you typed in your search settings. The match will only find the exact same words ( no more or less ) in the exact order you entered them.

NOTE: For case sensitivity when searching for text information, you will need to index the field in ASCII ( see indexing discussion for details ).

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.