A database application provides many features, but a primary purpose is always the ability to locate whatever data you need at any particular moment. FileMaker excels in providing tools to specify very powerful search criteria, but many users never try them out, restricting their searches to the simplest kind and manually whittling away irrelevant records. However, if you take a little time to become familiar with FileMaker’s built-in search tools you may find that in many cases you can get exactly the records you are interested in and no others.
Software developers know an open secret about computers: They’re very stupid. I don’t mean they aren’t powerful nor that we aren’t able to accomplish amazing tasks with them, but all of the intelligence given to computers is imparted to them by programmers and users. While you can tell another person, “Go through all of the invoices for the past month and pull out those from customers in Washington where the invoice amount is at least $1,000 or less than $50 but have not been paid,” and the person will be able to do so with no further instruction, computers just aren’t intelligent enough (yet) to understand what you mean. So for the time being, much of being efficient with these powerful machines is the skill of translating what we want the computer to do into what it understands. In fact, this is almost the definition of a software developer. But even if you don’t earn your living writing software, knowing the details of your daily tools, such as FileMaker, will enable you to be much more productive.
Throughout this article we’ll be using a very simple FileMaker database that tracks authors and books. If you plan to try the search techniques covered here I recommend you download the sample below and begin there. This article covers the search tools that are built into FileMaker, but developers can, and often do, override those built in tools. Unless you are working with a system you built yourself, there’s no guarantee that FileMaker’s built-in search will be available to you as described here.
Found Sets and Current Record
Before describing FileMaker’s search features, we should cover the concepts of found set and current record, which are integral to understanding how search works. The found set of records are the records that you have immediate access to. This could be all records, a subset of all records or, in some circumstances, even no records. The current record is the record that you are currently working with. This could mean that you’re just perusing the data in a record or actually editing it.
A classic analog to digital databases is a recipe card system. If you open the card box and pull out one recipe, your found set and your current record are both that one card. If you pull them all out, then your found set is all of the cards. And if you notice that you have some ground beef in the fridge and therefore sift through all of the recipes, pulling out only those that include ground beef as an ingredient, and put the rest back in the box, your found set includes those that you’ve retained; the current record changed as you looked at each card. You select one recipe from that batch and put the rest back, returning to a found set of one, then cook that recipe and put the card away, ending with a found set of zero records.
FileMaker works very similarly. While ‘found sets’ are applied in many different areas of FileMaker, whenever you perform a search using FileMaker’s search tools, you’ll end up with a found set of records that conform to the criteria you’ve specified. FileMaker provides a number of indications about the state of the found set and the current record, but the easiest to take note of is in the toolbar on the far left.
Let’s take a look at some examples. Throughout this article we’ll work with a very simple database that tracks authors and books. In Figure 1 below, you can see our list of authors. There are nine in the database and at this point they are all in the found set. In other words, all of the records are currently being shown.
The slider and the records report at the left of the toolbar indicate the current record and the found set status. First of all, note the gray circle with the number ‘9’ and the words ‘Total (Unsorted)’ next to it. The fact that the circle is gray and we see the word ‘Total’ is an indication that our found set includes all of the records in our authors database. And the ‘2’ that appears above the slider indicates that we are on record 2 of 9 within that found set. You can also see this in the list with the darker blue highlight on the second record.
We can change the found set in many ways, but the easiest is to perform a quick search. If we search for ‘william’ in the ‘first_name’ field, we get a found set of two records, as shown in Figure 2 below. Three changes in the toolbar indicate that we’re no longer working with all of the records. First, the gray circle has changed to two shades of green representing a pie chart of found versus all records. To the right of this little pie chart, we see ‘2/9’ indicating that our found set contains two records out of 9 within the database. Finally, below this fraction are the words ‘Found (Unsorted)’ instead of ‘Total (Unsorted).’
Of those two records the first one is the current record, as indicated by the number 1 above the slider and by the fact that the first record in the list is highlighted with the darker blue.
One last fact of note before moving on from found sets: When your found set includes anything other than all of the records, you can press ‘Cmd/Ctrl-J’ (i.e., ‘Cmd-J’ on Macs and ‘Ctrl-J’ on Windows) or choose ‘Records>Show All Records’ to return to a found set that includes everything.
The Simplest Find
Most FileMaker users are familiar with the simplest find. You enter Find Mode either by pressing ‘Cmd/Ctrl-F’, selecting ‘Find Mode’ from the View menu or by selecting ‘Find’ from the menu at the bottom of the window (which will usually say ‘Browse’). Once in Find Mode, you tab to or click into the field you wish to search on, enter your search criterion and either press ‘Return’, click ‘Perform Find’ in the the toolbar, or choose ‘Requests>Perform Find’ from the menu bar. FileMaker then provides a found set of records that have whatever you entered in the field you entered it into.
For simple finds, where you’re searching a single field for a single piece of information, this works fine. Even if your search is a bit general, by navigating through the found set (by using the flipbook in the toolbar or by pressing ‘Ctrl-Up’ or ‘Ctrl-Down’, you can soon get to the record you want. This is exactly what I did above when I wanted to find the author records with ‘william’ in the first name.
Boolean (And, Or, Not) Searches
George Boole was a 19th century mathematician who invented a system called Boolean logic that programmers use to test complex conditions. If I use the invoice example given above, this could be translated into a boolean test:
Include all invoices that:
> have a date greater than or equal to 30 days ago
> ‘AND’ have a date less than or equal to today
> ‘AND’ have a customer state of Washington
> ‘AND’ have an amount less than or equal to $50 ‘OR’ have an amount greater than or equal to $1,000
> ‘AND’ have ‘NOT’ been paid
All of the basic boolean operators are being used here, ‘AND’, ‘OR’ and ‘NOT’, and we’ll cover how to use each of their equivalents in FileMaker.
The first method for specifying more advanced search criteria will be to search for records that satisfy multiple criteria. For example, if we want to find all of the science fiction books by Isaac Asimov, we simply enter Find Mode and place ‘science fiction’ in the genre field and ‘Isaac Asimov’ in the author field, as in Figure 3 below.
This search says, “Show me all of the book records where ‘science fiction’ is in the genre field and ‘isaac asimov’ is in the author field.”
In a similar vein, you can say something like, “Show me all of the book records where the genre field is either ‘fantasy’ or ‘science fiction’,” and as you might guess, whereas the first is called an ‘AND’ search, this one is called an ‘OR’ search. To perform an ‘OR’ search, you need to create multiple find requests. Just as you can create multiple records by pressing ‘Cmd/Ctrl-N’ while in Browse Mode, pressing ‘Cmd/Ctrl-N’ in Find Mode creates a new find request. One such request is shown in Figure 4 below. This was created by performing these steps:
1. Enter Find Mode
2. Type ‘fantasy’ in the genre field.
3. Press ‘Cmd/Ctrl-N’ to create a new find request (or choose Requests>Add New Request from the menu bar).
4. Type ‘science fiction’ in the genre field
Multiple Routes
Many operations in FileMaker can be accomplished with multiple methods, and boolean searches are no exception. When in Find Mode, the Requests menu offers two menu items to assist with boolean searches: ‘Constrain Found Set’ and ‘Extend Found Set’. ‘Constrain Found Set’ operates exactly like an AND search, but the operations are performed in multiple steps instead of one. If we want to duplicate the search we performed that found Isaac Asimov’s science fiction works, we could have done the following:
1. Enter Find Mode
2. Type ‘fantasy’ in the genre field.
3. Press ‘Return’
4. Enter Find Mode again
5. Type ‘isaac asimov’ in the author field.
6. Choose ‘Requests>Constrain Found Set’ from the menu bar.
Note that steps 2 and 5 could have been swapped with no change in the end result.
Similarly, using ‘Extend Found Set’ could be used to duplicate our search for fantasy or science fiction novels:
1. Enter Find Mode
2. Type ‘fantasy’ in the genre field
3. Press ‘Return’ to execute the find first find
4. Enter Find Mode again
5. Type ‘science fiction’ in the genre field
6. Choose ‘Requests>Extend Found Set’ from the menu bar.
Again, steps 2 and 5 can be swapped without altering the end result.
The difference here is only one of how you think of the search or perhaps in what order the search criteria come to mind. The first time we performed an AND search; you can think of this as saying, “Perform a search that matches both of these criteria,” while in the second case, it’s more like saying, “Find the records that satisfy this first criterion and then search those records for those that satisfy the second crition.”
Translating the OR searches methods, the first is saying, “Show me the records that match either this criterion or this other criterion,” while the second one says “Find the records that satisfy this first criterion and then search all of the records for this second criterion and add any you find to the first found set.”
Reversing the Logic
Sometimes it’s easier to define the records you want by what they’re *not* rather than what they are. Perhaps you want to read something. You don’t know what, but you know you’re not in the mood for a tragedy, so you’d like to see all of the records that have a genre of anything *other* than Tragedy.
Given what you’ve seen so far, defining such a search would require that you know what every possible genre is, but FileMaker provides two other methods. The first is direct and immediately shows you the records you’re interested in, and the second first finds what you’re not interested in and then allows you to reverse the found set. To directly show all records that don’t satisfy some criterion, you use the ‘Omit’ button that’s available in Find Mode, so we could show all non-tragic books with the following steps:
1. Enter Find Mode
2. Type ‘tragedy’ in the genre field
3. Click the ‘Omit’ button
4. Press ‘Return’ to execute the find
To use the second method, perform a find you normally would (i.e., by finding all of the tragic books) and then choose ‘Records>Show Omitted Only’ from the menu bar. This basically reverses the found set of records. For example, there are 18 books in my sample database, and 2 of them are tragedies, so finding the tragedies gives me a found set of 2 records. If I then select ‘Records>Show Omitted Only’ from the menu bar the found set will include the 16 records that are *not* currently part of my found set.
Take note here of a slight inconsistency in FileMaker. I said earlier that creating new find requests is the equivalent of creating an OR search, but using the ‘Omit’ button is an exception. As you’ll see in the examples below, if you create a new find request and use the omit option, it’s equivalent to saying “Find records that match the first request and exclude those that match the second.”
Complex Examples
While there’s more to come with the next installment of FileMaker Finds, in order to highlight how much power just the addition of boolean searches can add to your FileMaker use, let’s try a few complex examples that combine AND and OR and NOT (omit) searches. I personally prefer direct searches to using the constrain and extend capabilities, so that’s what will be shown in these examples. If you’d like to test your own understanding of boolean searches, try duplicating these direct searches using FileMaker’s constrain and extend options. All of these examples assume that we’re starting out in Find Mode and end by executing the find, so I’ll omit those steps.
First, how about all books that have the word ‘the’ in the title that were written by an author with a name (any name) that begins with either ‘c’ or ‘g’.
1. Type ‘the’ in the title field
2. Type ‘c’ in the author field
3. Press ‘Cmd/Ctrl-N’ to create a new request
4. Type ‘the’ in the title field
5. Type ‘g’ in the author field
Something more complex. Let’s have FileMaker show us all of the books by an author named “william” that aren’t comedies.
1. Type ‘william’ in the author field
2. Create a new request
3. Type ‘comedy’ in the genre field
4. Click the ‘Omit’ button
This is the example of a new request with the omit option providing AND logic instead of OR logic.
Finally, let’s just go crazy with an obviously contrived example. Let’s tell FileMaker to find all the books that:
– were published in 1600
– are neither a novel nor science fiction
– by an author with “william” or the letter “g” in the name
We’ll use one last new feature here, the ability to duplicate find requests, just like duplicating records.
1. Enter ‘1600’ in the published field
2. Enter ‘william’ in the author field
3. Duplicate the request by pressing ‘Cmd/Ctrl-D’ or choosing ‘Requests>Duplicate Request’ from the menu bar
4. Change the author field from ‘william’ to ‘g’
5. Create a new find request
6. Enter ‘novel’ in the genre field
7. Click the ‘Omit’ button
8. Create a new find request
9. Enter ‘science fiction’ in the genre field
10. Click the ‘Omit’ button
Just The Beginning
If you’ve never used them before, boolean searches, either directly or with Constrain and Extend Found Set, will significantly extend your control over your data. And it’s possible that all your searching needs can be solved by boolean searches, but perhaps not, and next time we’ll cover the additional power in FileMaker searches by exploring the find operators.
~Chuck Ross
