Finding in FileMaker

Learning the basics of a Find can really make a difference in your work.

By Jonathan Stars

FileMaker Pro is the number one selling standalone database in the world. Learning how to use it's powerful search capabilities can increase its' usefullness to you.

Open a database you've already created or one of the templates that includes fields for people or companies and addresses. Make sure there are some records in the file with a mixture of states.

While you're on the data entry layout go to the View menu, choose Find Mode and type MI (for Michigan) in the state field. Click the Find button in the Status Area on the left side of the window. How many records did you find?

Go to the Records menu and choose Modify Last Find. That's right. FileMaker remembers your last Find. This is a very handy tool in itself! Think about times you've created a complex find only to realize you didn't get it quite right. Don't start over. Use Modify Last Find. Now choose Records > New Request. (This is a tool I didn't even know was available for a couple of years using FMP back in 1985!) Type Lansing (or another city of your choice) in the city field. Just above the Find button click on the box next to Omit then click the Find button. How many records came up this time? You just told FileMaker to find all customers in Michigan except those who live in Lansing.

Choose Records > Find Omitted. Now FileMaker shows you all customers who live in all other states and Lansing, Michigan. You'll find hundreds of uses for Find and Omit once you get the hang of it.

Did you know you don't have to type out the whole word you're searching for? If I'm looking for Arnold Schwarzenegger, I may not remember how to spell his last name. I can quickly type Arn, tab to the Last Name field and type Sch and hit the Enter key. Got 'im!

How about the Find Symbols? When you're in Find mode, look in the Status Area over in the left under the Book. You'll see a triangle next to the word Symbols. Many people don't even know it's there.

The first four Find Symbols will be familiar mathematical symbols and are most often used in number or date fields. For example, if you started a Find in a number field and used the "less than" (<) symbol followed by a "4", you'd find all numbers (and decimals) smaller than the number 4, including negative numbers.

The Symbol used for "exact match" (=) would help us find records exactly equal to 4. But there’s a twist. You can use this symbol to find fields that are empty or have unindexed values. Simply start a find in a field and use the equal sign and don't add anything else after the sign. You'll find records that are empty in that field. In essence, you’re asking FileMaker to find all records that are equal to nothing in that field. Remember, though, that many keyboard characters are unindexed. For instance, if you only had a dash or an underscore character in the field, those records will turn up in your find as well. This can cause you some headaches when it comes to finding e-mail addresses, since all e-mail addresses include the @ symbol. Unless you have the field indexed as ASCII or Unicode (depending on which version of FileMaker you're using), you won’t be able to find them when including the @. However, you can find records that match something before or after the @. To learn how to index a field using ASCII or Unicode, see the Help files. [By the way, don't try to use the "one character" (@) symbol to find the @ in email addresses. It won't work.]

I will often use the "duplicates" symbol (!) to find people at the same address or with the same name. Unfortunately, it may not be as easy as it sounds because you can't simply put an ! in both the First and Last Name fields and get the results you'd expect. You will probably need a special "concatenated" field that combines First and Last Name into the same field as part of a calculation. Same goes for addresses. After all, how may 123 S. Main Street addresses do you think there are in the US? But when it's important to client work, I'll make those special fields and use the ! symbol. It's also very helpful when you have a file that might have customers or invoices with duplicate ID numbers.

One side note about finding is that performing a Find in multiple fields in a portal will not necessarily provide the results you want. I discussed the details in my 3rd newsletter. If you need to read that one, go to and click on Newsletter #3.

FileMaker 6 introduced two new features that allow you to Constrain and Extend Found the Set. They perform variations of a function that I like to think of as a Find within a Find. They act in ways similar to Modify Last Find but with more flexibility and sometimes more simplicity.

Instead of starting your Find with the whole database of records, the Constrain and Extend Found Set commands start with just the currently found (or visible) records. They're especially helpful in situations where your found set wasn’t created with the Find command, such as when you’re using the Omit record commands or a Go to Related Records script step. The Constrain and Extend commands give you much more flexibility than you’ve ever had before. Let’s take a look.

Constrain Found Set Perform a Find of some kind. Enter Find mode again, type something else in a different field and choose Requests, Constrain Found Set. You’re telling FileMaker, “Limit my last Find by showing me only people who match the request in both fields.” In some cases, you may find no matches at all. Yes, you could have selected Modify Last Find and accomplished the same thing. But Constrain/Extend become more valuable when your Finds are more complex and across multiple requests. Say you had just performed a complex Find, looked at the results, and needed to clean it up a little. Just enter Find mode again, make the change, and choose the Extend or Constrain function that suits the situation.

Extend Found Set This works the same as Constrain Found Set except you use it to widen your Find. Although explaining and understanding the specifics of the Constrain Found Set and Extend Found Set functions turns out to be a bit complex, here are some points that may give you some ideas as to how they can be useful to you:

* You can perform an endless series of Extend and Constrain functions in any order on a set of records.
* You can perform a Modify Last Find and Constrain or Extend from there.
* You can manually omit records from a found set and continue to Constrain or Extend from there. The omitted records will not be included in your next move unless they are part of your new request.
* You can combine omitting records using the “Omit” check box (Find mode) with a request to Constrain or Extend Found Set.
* In effect, you can say things like, “Oops! I didn’t mean to remove those people from the found set. Let me put them back in and try again.”
* If you have been moving through a series of steps, extending and constraining a set of records, and you accidentally perform a regular Find (rather than selecting Requests, Extend/Constrain Found Set), your found set will be replaced with your last Find request. This point leads to one of the limitations of these functions which is…
* You cannot go backward through the steps of your Find.
* The functions are scriptable. (Use the Perform Find script step and make your choice from the Options Specify pop-up.) What happens in the script depends on whether the “Restore find requests” option is checked. If it is not checked, the script step will use the last manual or scripted find performed before this step runs.
* The functions allow you to perform an inclusive OR, an exclusive AND, or any combination of either. (This terminology may only be pertinent to mathematicians and statisticians.)

There are more amazing Find capabilities in the program. Using FileMaker's advanced search capabilities can change your life. That may sound like a strong statement, but it is no less true. Read about Find in the users manual and try a few of the ideas. Even if you're fairly advanced, you'll be surprised what you'll learn.


© 1998 Jonathan Stars

< Back to In the News


Email us here.