Power Finds

I’ve been working with FileMaker for a really long time, about 20 years now. For the last dozen or so years, I’ve been a full-time developer of FileMaker solutions. I spend a lot of time developing cool new solutions with neat features and interfaces, for a lot of different business applications.  I sometimes forget that not everyone eats, sleeps and breathes FileMaker from the moment the wake to the moment they sleep (and sometimes even after that, in dreams).

Because of all that, I sometimes get so focused on the latest new technique or strategy that I take some really powerful, basic functions of FileMaker for granted. A prime example of this is the power of the Find mode.

Usually, my first clue is that a client is overly concerned about being able to sort records. Only then do I realize that they’re wasting time sorting long lists and scrolling through them, because they don’t realize they can do a better Find an get to exactly what they want. Sort is great for a small set of records, and of course it’s essential for a good report, but if you’re sorting hundreds of records and then scrolling to find what you’re looking for, then… how else to say it?… you’re doing it wrong.

If you work with FileMaker, you’re surely familiar with how to do a basic find. In the View menu, click on “Enter Find Mode” (or just hit Control F on Windows, or Command F on the Mac ). You fill out the blank fields with what you want to find, then click Enter (or select “Perform Find” from the Requests menu, if you really love using your mouse). Voila! You found the records that match. Super easy.

Most times, that’s all you need. Maybe that’s why I often find that users don’t know about all the other useful features of Find. In this article, I’d like to go over some other fairly easy and basic things you can do with Find. They’re the sorts of things I take for granted working with FileMaker, and often forget to tell new users about.

Just so you have a demo to work with, you can click here to download a demo file to follow along with. It’s a super-simple, super-small file. These same tricks will work in your other FileMaker file(s). It’s a .fp7 file, in case you’re using FileMaker 7-11, but if you’re using FileMaker 12, you can easily convert it. The file has a very simple table, with just name, gender, age and a date.

Less is More

The first thing to remember when doing a search is that when you want to find something in FileMaker, less is more. In the demo file, for example, if you do a Find for “Chris Cain”, you’ll find both the record for “Chris Cain” and also the one for “Christopher M. Cain”. If, however, you get really specific and search for “Christopher M. Cain”, you’ll find only the one record.

The lesson here is that the more you enter in your find criteria, the less you’ll find. Many times, that’s what you want; you want to find a very specific set of records. Other times, though, you might not be sure how complete the record is going to be when you find it. Did someone enter my full name, or just my shorter name? When you’re not sure, enter less to get more matches. You might even want to just look for “C Cain” or “Chris C”, to maximize your chance of finding what you’re looking for. Suppose you get a phone call from “Jack Smith”. You can search the demo file for “Jack” all day long, but you’ll never find him. However, if you look for “J Smith” you’ll find that on the day he got entered into the demo file, he was feeling a bit more formal.

This principal of searching (“finding”) isn’t just in FileMaker. It’s also true in other programs, and when searching on the web.

Default Behavior

FileMaker makes some pretty good assumptions about your search criteria that generally make doing a Find easy and intuitive, but let’s take a moment to be sure those assumptions are clear. Usually, these assumptions are correct. Sometimes, though, we’ll want to override those assumptions. More on that later.

When you’re searching a text field, FileMaker assumes you’re entering the beginnings of words, not the middles or ends of them. If you search for “C” you’ll find Chris, but if you search for “h” or “s” you won’t.

To FileMaker, a “word” is anything that’s separated by spaces or most punctuation, even if it’s made up of numbers. It assumes that the order of the words doesn’t matter. If you search for “Cain Chris” you’ll still find “Chris Cain”.

FileMaker assumes that spaces don’t matter. It also assumes that if you haven’t entered anything in a field, you don’t care what that field has in it.

Finally, FileMaker assumes you don’t care about case. You can search for “chris” or “Chris” or “CHRIS”, and you’ll find the same records.

And vs. Or

You probably know that if you  enter Find criteria in more than one field, FileMaker will find records where everything you entered matches. For example, if you do a Find for people with the name “Chris” who are “Male”, you’ll find records where the name has a word starting with “Chris” AND the gender says “Male”.

Sometimes, though, you want to find this OR that, not this AND that. To do that, you want a New Request.

Try this:

Let’s say you want to find everyone who is named Chris OR is female. You want to find all the Female records, whether they’re named Chris or not, and all the Chris’, whether they’re Female or not.

First, enter the Find mode and enter “Chris” in the name field. Next, under the Requests menu, select “New Request” (or hit control/command + n). Now you have a second request. For that request, enter “Female” in the gender field. Now hit enter.  You should find seven records. Four have a gender of Female. Four have the name “Chris”. One, Christina Ricci, has both.

Adding new requests is like telling FileMaker I want this OR that.

Here’s another example. Suppose you want to find everyone with the name Chris OR the name John. In your first request, enter “Chris”, and in the second, enter “John”. Viola! Now you’ve found both.

Omit

Sometimes, you want to find things that don’t match. Suppose you want to find all the records with names that start with J, but you don’t want to find “Jason”.

Enter the find mode. In your first request, put “J” in the name field. Now create a new Request, and put “Jason” in the name field. With that second request selected, look in the status area (the bar across the top) for where it says “Maching Records”, and change “Include” to “Omit”. (A quick note: developers of custom solutions, ourselves included, often hide that status area to free up some screen real estate. In our custom solutions, we put the Omit command in the Requests menu.)

When you hit enter, you’ll find J, but not Jason.

An interesting note: If you have lots of requests, some of which are “Include” and some of which are “Omit”, FileMaker will perform the requests in order.

Useful Menu Commands

There are a few menu commands that are great for “tweaking” a Find you’ve already done. When you’re really using the power of the Find command, you can sometimes end up crafting a pretty sophisticated set of criteria. If you mess it up, or just want to refine your results, starting over can be a real pain. Luckily, there are tools for this.

First, there’s “Modify Last Find”. Suppose you do a search for all the “J” records, and only after you do your find you realize you didn’t want to find “Jason”. No need to start over. In the Records menu, select “Modify Last Find” (or hit control/command + r. Think “Refind”) and your previous find requests(s) come back up. Make your changes and perform your find again.

Next, there’s “Extend Found Set.” In the demo file, do a find for “J”. You have a found set of 7 records. Now suppose you want to find all the “C” records, but you don’t want to lose these 7. Enter the find mode again (“Wait! My 7 are gone!”… don’t panic, stay with me.) Put a c in the name, and (now this is the tricky part!) DO NOT hit enter! Hitting enter to find your records is so easy that I almost have to sit on my hands not to do it automatically. Instead, in the Requests menu, select “Extend Found Set”. Now you have 18 records, including both J and C records. Effectively, you’ve said “take the found set I already have, and extend it to include records matching my new request(s).

Finally, there’s “Constrain Found Set”. You use it like you would “Extend” (sit on your hands), but instead of adding to your found set, you’re only searching within your found set. Follow along: First, do a find for all the “J” records. You’ll find 7. Next, find all the records where the gender is “Male”. You should find 11. Now, with those 11 records up, enter the find mode and put a “J” in the name field (DON’T HIT ENTER!). In the Requests menu, select “Constrain” found set to only find the “J” records that are part of those 11. You’ll find 4.

Of course, if you knew ahead of time what you wanted, you could accomplish all of this in your original find. Reality, though, is that sometimes after you’ve done your find, you realize it could be better. Now you can Modify Last Find to change whatever criteria you entered. Or you can Extend Found Set to find more records without losing the ones you’ve found. Or you can Constrain Found Set to find only records included in the set you already found.

Once you have a found set, you can “tweak” the foundset itself without doing another find. In the Records menu, you can “Show All Records”, which shows all the records in the table (and essentially undoes your find). You can “Show Omitted Only” to switch to the records you did not find. You can also omit individual records, or groups of records. I’ve found that some users are scared of “omitting” because they confuse it with deleting. Omitting a record only removes it from your found set. The record doesn’t get deleted or otherwise changed.

Special Characters

Remember those assumptions we talked about earlier, in “Default Behaviors”? They usually make things easier, but sometimes they get in the way. What if you do want to find the middle of a word, or find a field that’s blank, or find words only in a certain order? For that, we have some special characters.

When you enter the Find mode, the status area has a button that says “Insert” and a drop-down field of “Operators”. (You can also search in Help for “Finding numbers, dates, times, and timestamps” or “Finding text and characters”.) I won’t cover every one of these, but here are some favorites:

You can find things greater than, less than or in a range, using >, ≥, <, ≤ or “…”. If you (like me) can’t find “≤” on your keyboard, you can also use “<=” and “>=” the same way. These are fairly obvious. Search the ages in the demo database for “>20”, “<20”, “>=20” and “30…50” go get the hang of these operators.

A couple of things about these operators aren’t so obvious. First, they work on all kinds of field types, including text and dates as well as numbers. For example, if you search the name field for “c…f” you’ll get names that start with the letters c through f.

What if you really do want to find blank fields? Usually, if you leave a field blank, FileMaker thinks you don’t care what’s in that field. What if you do care, but want to find where there’s nothing in the field? How do you search for nothing? The “=” operator will save you hear. You can use “=” when you want to match a whole word. Searching for “=chris” won’t find “Christopher”. More often, though, you’ll use this operator by itself to find where the field “equals” nothing at all. To try this, search for an equals sign in the gender field, and find all the records where that field is blank.

“?” will find invalid values. For example, if you search for “?” in the age field, you’ll find that some joker put “really old” for Jimmy Dean’s age, when they should’ve put a number.

“!” will find non-unique values, or duplicates. If you search for an exclamation point in the name, you’ll find the two duplicate “Jason Sanders” records.

You can use *, @ and # to find parts of words. An * means there might be characters before your criterion. @ and # mean there will be exactly one character, or one number (respectively) in this spot, before your criterion.

What if the order of the words really does matter? Use quotes. For example, search for “Carmine Nottyors” with and without quotation marks.

And of course, what if I actually want to find one of these characters, like an asterisk, equals sign, quotation mark or question mark? How do I tell FileMaker that I’m actually looking for these characters, not using them as special operators? Use the backslash: “\”. If you put that before an asterisk, for instance, you’re searching for an asterisk, not searching for “zero or more characters”. (And yes, you can find a backslash by searching for \\.)

Finding Dates

Now that you have a basic understanding of these operators, try searching for some dates in the demo file, but I want you to watch what FileMaker will do for you, without you even whipping out your new operators.

Search in dates for 4/2013. You’ll find all dates in April of 2013. If you did it quickly, you may not have spotted what FileMaker just did for you. If you missed it, do it again, but before you click enter (you sitting on your hands again?) click on another field and see what happened to the date you entered. It became “4/*/2013”. FileMaker figured out what you meant, and put in your wild card character for you.

I’m honestly not sure when FileMaker started doing this when finding by dates. When I started with FileMaker, I know it didn’t do that, and searching for dates was sometimes a pretty meticulous process. Now I sometimes forget how intuitive searching dates can be, and I put way more work into it than I have to. Perhaps you’ve done the same.

To “play” with this, try leaving other things out of the date. For instance, search for “1980…2013” or “<1991” or “4/1…4/30”, and watch how FileMaker fills in the blanks for you.

Wrapping Up

Honestly, when I started writing this I thought it would be short. Guess I was wrong, huh? Even still, there are more special operators, more tips and tricks and more strategies, and I could go on for twice as long… but I’d probably lose your attention, if I haven’t already.

Hopefully, this survey of some of the powerful tools available for Finds in FileMaker will encourage you to explore further. Even if you’re not the type of geek (like I am) who sits around trying different commands to see what they do, perhaps next time you’re trying to do a Find you’ll at least trust that there’s almost certainly a way to find precisely what you’re looking for. FileMaker Help has a lot of material, as do other blogs and forums. Give those a shot before you resort to the “sort and scroll” method.

Tags: Chris Cain, xBase User Tips

Other posts by Chris Cain+