Advanced Find Mode

In Chapter 1 you learned about using Find mode for simple searchesbut you only scratched the surface. The more records you have in your database, the more you'll need advanced finding techniques to avoid wasting precious minutes clicking the Book icon 1,057 times in a row just to find the record or records you want to display, edit, or print. FileMaker's Find tools give you the power to track down the one record in 100,000 you need right this minute, or the five records with missing phone numbers that you created a week ago Tuesday.

Figure 2-4. When you click a column header, FileMaker darkens that header and sorts the list. Here, for example, the list is sorted by Last Name. If you sort the records using the Records Sort Records command instead, FileMaker doesnt try to figure out which column was used. Instead, it simply lightens all the column headers.

 

2.2.1. Finding by Omitting

If you know what you don't want better than what you do want, you can use the Omit checkbox. This feature comes in handy when the records you're looking for can best be described by what they aren't. For example, "every person who isn't from California" is a lot easier to say than "everyone from Alabama, Alaska, Arizona, Arkansas, Colorado…" In this case, you can get what you want by creating one find request, with California in the State field. Then, before you perform the find, simply turn on the Omit checkbox, which appears in the status area only when you're in Find mode (Figure 2-5). That's all there is to it. FileMaker starts with every record in the found set, then throws out all the records with "California" in the State field, so you're left with everything else.

Omit also works with multiple find requests (see the box on Section 2.2.1.3). If all your requests have the Omit option set, then all the records that match any of your requests are thrown out. Whatever's left over goes in your found set. If you have a mix of requests with and without Omit set, FileMaker first finds all the records that match your normal requests. It then discards any that match an Omit request. You're finally left with a found set in which every record matches at least one of your requests, and doesn't match any of your Omit requests.

2.2.1.1. Constraining and Extending the Found Set

When you perform a find normally, it effectively throws away any found set you had before, searches through all the records in the database, and produces a new found set. But you can also tell FileMaker to constrain the found set (that is, search within your last find results) or extend it (add matching records to the current found set).

2.2.1.2. Constraining the found set

Suppose you've just created a great product, and want to send out some free samples to see how people like it. You can't afford the postage to send everyone in your database a sample, so you decide to start with a smaller samplingjust people named Smith.

Figure 2-5. Find mode offers up some new status area options. The Omit checkbox basically tells FileMaker to do an un-find: It finds all the records that don't look like your request. The Symbols give you more control over how FileMaker decides it's found a match. They're described in detail starting on Section 2.2.1.3.

Problem is, your database has hundreds of Smiths. Some quick math in your head reveals that it's still too expensive. How about sending samples to just the Smiths in California? You could go back to Find mode and construct a request to find this criteria instead (by putting Smith in the First Name field and California in the State field), but there's an easier way. After all, you've already got all the Smiths in your found set. What you really want to do is search inside this found set for all the records with California in the State field.

Here's how the procedure goes:

1. Switch to Find mode, type Smith in the Last Name field, and then press Enter.

   FileMaker searches the database and, in Browse mode, shows you the records with Smith for the last name. You glance at the status area678 records! You now need to search through these records for the ones whose state is California. That's exactly what the Constrain Found Set command does.

2. Immediately switch to Find mode again using any of the methods described on Section 1.6.3.

   In other words, don't choose Records Show All Records, and dont do anything to tamper with the current found set (678 records, in this example). You're now in Find mode again, ready to enter a request.

   ---

   Note: In Find mode, there's no way to see what your current found set is. You just have to remember that you last searched for Smith. If you need to see the found set, just switch back to Browse mode and flip through the records.

   ---

3. Type California in the State field.

   Since the found set already has only Smiths in it, there's no need to repeat that information.

4. Choose Requests Constrain Found Set.

   Warning: Once you're in Find mode, FileMaker is itching for you to perform a find. If you just press Enter, it assumes you want to throw away your last found set and make a new one. Once it's gone, there's no way to return to the old found set and constrain or extend it. Likewise, if you accidentally choose the wrong command from the Requests menu, there's no going back. If you want to constrain or extend, be sure you choose wisely or you'll have to start all over again.

   ---

   2.2.1.3. Extending the found set

   Extending the found set works similarly. This time, though, you're asking FileMaker to perform a new find (through all records in the database) and then add the records it finds into your found set. What you end up with is all the records you already had, plus any new ones that were found. Suppose you've already found the Smiths, and you want all the Johnsons as well.

   1. In Find mode, type Johnson in the Last Name field.

      So far, you're doing exactly what you would in every other find. You just have all the Smiths in your found set, even though you can't see that in Find mode.

   2. Choose Requests Extend Found Set.

      and Johnsons in one found set.

   2.2.2. Refining Searches with Find Symbols

   Normally, FileMaker uses a pretty simply rule to decide whether a field value matches the field in your request (the criteria): If every word in the criteria appears at the beginning of any word in the field, FileMaker considers it a match.

   POWER USERS' CLINIC
   Multiple Requests

   It's rarely strictly necessary to use Constrain or Extend to get exactly what you want. To get the effect of a Constrain, just fill out multiple fields in your find request. Since a record has to match every field in the request, a computer geek would say you're doing an AND search. This odd term simply means you're asking for records where both the First Name is "John," for example, AND the last name is "Smith." Extend Found Set, by contrast, is analogous to an OR search, meaning it locates all the records that have either search term. An OR search is like saying, "Show me all the records where a student is either a 'Head Boy' or a 'Prefect.' Another way to get an OR search is with multiple find requests. Remember that a request is the Find mode equivalent of a record. When you're in Find mode, you're free to create as many as you want (choose Requests New Request) and you can flip through them using all the same tools you use to flip through records. Heres how to make your own OR search using multiple requests:

   1. Enter Find mode using any of the methods described on Section 1.6.3. FileMaker opens a blank request.
   2. Type Smith in the Last Name field. You're describing one kind of recordthe kind with "Smith" in the Last Name field. If you add any more criteria to this request, you'll just be excluding some Smiths from your found set. So instead…
   3. Choose Requests New Request. FileMaker gives you a second empty request. The first request is still there, although you cant see it. Since requests work just like records, you can use the Book icon, the Slider, or the keyboard shortcuts to switch between them. Or you can switch the layout to list view.
   4. Type Johnson in the Last Name field. You're describing the other kind of record you want. Since you have two requests, FileMaker treats them separately, finding all the Smiths, then finding all the Johnsons, and finally putting both lists into the found set.

   Essentially, each request represents another extension of the found set. Many FileMaker Power Users, once they've mastered multiple requests, find they can perform them with fewer clicks and keystrokes than the Constrain and Extend Found Set commands. In the real world, though, the Constrain and Extend commands offer a more straightforward, intuitive approach to complex requests. In fact, even the pros constrain or extend the found set when a multiple request strategy fails. In the end, it usually comes down to preference.

   Finally, there's no reason you can't mix these techniques, using Constrain and Extend and multiple requests at the same time. For instance, you could use multiple requests to find all your customers who live in Florida, Hawaii, and California, then use the Constrain Found Set command to find just the people named "Fred" who live in those three states.

    

   For example, if you put "for" in the Note field in a find request, any of these notes would match:

   • All for one and one for all.
   • We will forever remember.
   • Back and forth it went.

   On the other hand, neither of these would match:

   • Wherefore art thou, Romeo?
   • Before there was art, there was an artist.

   How do you tell FileMaker that you're looking for "for" wherever it appears in a word, not just at the beginning? You can use a special symbola character that has special meaning in a Find requestto stand for part of a word.

   The Symbol pop-up menu, which appears in the status area when you're in Find mode, lets you add these special characters to your searches, thus gaining more control over FileMaker's decision-making process when it's looking for records. There are plenty more special symbols, and each is described below.

   2.2.2.1. The Wildcard (*)

   In the "Wherefore art thou Romeo" example, you would use the * wildcard symbol, which stands for "anything." It tells FileMaker that you don't mind if there's something right before or right after the "for." If you type *for* as your find criteria, FileMaker will display records that contain "wherefore," "before," "George Foreman," and so on.

   2.2.2.2. Less Than (<), Less Than or Equal To (), Greater Than (>), and Greater Than or Equal To ()

   These symbols tell FileMaker to use your criteria as a maximum or minimum rather than a direct match. For example, the criteria <David will find every person whose name comes before David alphabetically. Note: In Mac OS X, the Less Than or Equal To symbol shows up as , but on Windows, it shows up as <= instead. You can symbol on Mac OS X (hold down the Option key and press the comma key) but it works just as well to type <= as you would on Windows. The same thing goes for the Greater Than or Equal To symbol, which you can type as >=.

   ---

   The rule FileMaker uses automatically for determining a match is pretty loose. But sometimes you want exactly what you say: "Smith," not "Smithers" or "Smithey" or "Smithsonian." In this case, use the Exact Match symbol (=). In this example, type =Smith in the Last Name field, and perform your find.

   If you want to exactly match more than one word, put the words in quotes instead. This Literal Text match is also good for criteria that contain punctuation, like "Mr. Smith."

   ---

   Note: Like French fries, quotes come in two varieties: curly and straight. If you're someone who notices this kind of thing, you may wonder if it matters which kind you use. It doesn't. Usually FileMaker turns your quotes curly for you, but you can turn off smart quotes for a database in the File File Options dialog box.

   ---

   Smith matches only "Smith" in the field and nothing else.

   ---

   Note: Literal Text doesn't actually match text anywhere in the field. The criteria text must match starting at the beginning of a word. For instance, a search for "Mr. Smith" would match "Mr. Smith" and "Mr. Smithers," but if you searched for "r. Smith" (no M) instead, you'd find no matches.

   ---

   2.2.2.4. Range (…)

   The Range symbol is like the Greater Than or Equal To and Less Than or Equal To symbols combined. The criteria "David…Michael" matches everyone from David to Michael, alphabetically speaking. Just like the other symbols, the Range symbol is smart enough to understand numbers, dates, and times, as long as the field expects them. (You'll learn more about field types in the next chapter.)

   2.2.2.5. Duplicates (!)

   The Duplicates symbol (!) can be hard to get the hang of. When you put ! in a field in Find mode, FileMaker finds records with duplicate data in that field. In other words, it looks for records with the same value in that particular field. The same as what you ask? The same as any other record. Think of it this way: If you have only one person in your database from Idaho, and you perform a find with ! in the State field, the person from Idaho won't appear. That's becausewhen considering the State field alonethe person from Idaho has no duplicates. On the other hand, if you have 11 people from Oklahoma, they'll all be found because they all have duplicates (each one has 10 others just like it, statewise).

   Unlike most other symbols, the ! symbol is always used alone in a field in Find mode. You would never put ! Smith in the last name field. Instead, you'd just put ! all by itself.

   POWER USERS' CLINIC
   Finding Duplicate Records

   Every database user eventually makes the mistake of entering the same data twice. Maybe you assume a person isn't in your database and add him, only to discover months later that he was there all along. The ! symbol can help you hunt this sort of thing down.

   If you want to find whole records that are exact duplicates, you'll have to put ! in every field when you're in Find mode. But it isn't at all uncommon for "duplicate" records to be slightly different. Maybe you updated the phone number in one but not the other, for instance.

   Or perhaps you misspelled the street name the first time you entered this person, and spelled it right the second time. Finding exact duplicate records wouldn't catch these kinds of so-called duplicates.

   If you're looking for duplicate records, you're usually best off putting the ! symbol in as few fields as possible. Try to pick fields that tend to be entered the same every time and stand a good chance of identifying an individual person. You might use just First Name and Last Name, for instance.

    

   2.2.2.6. Today's Date (//) and Invalid Date or Time (?)

   Like the ! symbol, these symbols can go in a field in Find mode all by themselves. The double slash is convenient shorthand for the current date. If you're looking for all the payments due today, you can type // in the Due field more quickly than "September 15, 2005."

   The Invalid Date or Time symbol (?) is another helper when it comes time to clean house. It's possible to end up with the wrong kind of data in fields that are supposed to hold dates or times (like "N/A," "Never," or "Next Week"). Put ? in the Due field, and FileMaker finds every payment whose date is not valid, giving you an opportunity to fix them. (You'll learn more about fields that hold special kinds of data in Chapter 3.)

   ---

   Tip: You can mix and match these symbols in any combinations that make sense. For example, to find everything after today's date, just search for >// instead of >12/21/2003.

   ---

   2.2.2.7. One Character (@), One Digit (#), and Zero or More Characters (*)

   These symbols are like the wildcard characters you sometimes see in other programs. You already saw the Zero or More Characters symbol (*) at the beginning of this section. It simply tells FileMaker you're willing to accept some textany textin place of the * symbol. If you're not ready to go that far, you can instead permit just one character (letter, number, or punctuation) with the One Character (@) symbol. When matching numbers, you can be even more specific, permitting just one numerical digit with the One Digit (#) symbol. Here are a few examples:

   • *smith* matches "Smith," "blacksmith," "Smithsonian," and "blacksmiths"
   • *smith matches "Smith" and "blacksmith," but not "blacksmiths" or "Smithsonian"
   • smith@ matches "smithy" but not "smithers"
   • @*smith matches "blacksmith" but not "Smith" or "blacksmiths"
   • smith# matches "smith1" and "smith2" but not "smithy"

   POWER USERS' CLINIC
   Finding Hot Dates

   That heading got your attention, didn't it? Well, as long as you're here, you might as well read about some cool ways to get date fields to yield their secrets.

   • If you need to find all invoices generated in September this year, type 5/2005
   • To find out who received large bonuses while the boss was on sabbatical last year, type 12/2004…5/2005
   • Keep tabs on payday sales by typing */15/*
   • See if there's slow performance in the sales department on Monday morning by typing =Monday in a date field. You can even just type Mon if you'd rather not bother with those extra keystrokes.
   • To find out if the lull lasts through Tuesday, type Mon…Tue
   • Find those invoices generated just before the monthly incentive period closes by typing */{29…31}/2004

   Timestamp fields (see Section 3.2.6.5) hold date and time information. You'd use a bunch of them to design a Time Clock Punching system. They also have some nifty search capabilities.

   • Find out who clocked out between 4 p.m. and 4:59 p.m. today by typing //4 pm
   • See who your star performers and workaholics are (everybody in on any day between 6 and 7 a.m.), by typing 6 am
   • Find out who left work early to pick up their preorder copy of Harry Potter and the Half-Blood Prince by typing 7/16/2005 3 pm
   • Check on anybody leaving after lunch any day in July by typing 7/2005 2 pm.
    

   2.2.2.8. Relaxed Search (~)

   The last symbol, called Relaxed Search (~), only applies to searching Japanese language text. It instructs FileMaker to consider characters to match if they make the same sound, even if they aren't exactly the same character. Alas, in English, spelling always counts (you can't expect "~korn" to match "corn").

   2.2.3. Refining a Found Set with Omit Commands

   FileMaker provides a few menu commands to help you fine-tune your found set. Sometimes, after you've done the best find request you can, you still end up with a couple of records in the found set that you don't really want to see. FileMaker offers three commands that make tossing out misfit records as easy as pie.

   FREQUENTLY ASKED QUESTION
   Finding Special Characters

   How do I find [e-mail] when "@" means something special?

   If what you're actually looking for includes one of these special symbols, you need to take extra precaution. When searching for [e-mail], the wildcard "@" symbol will match any character.

   (Admittedly, that's not such a big deal in this case, unless you happen to have mrbillXmicrosoft.com in your database as well.) To prevent FileMaker from interpreting the @ as a special character, use the Literal Text symbols you learned about on Section 2.2.2.1. In other words, putting the search text in quotes ("[e-mail]") will do the trick.

    

   2.2.3.1. Omit Record

   Go to the record you don't want (using the Book icon, for example) and choose Records Omit Record. This one-off command tosses the record out of the found set, reducing your found count by one. Dont confuse it with the Omit checkbox that shows up in the Status bar when you're in Find mode (Section 2.2.1), which tells FileMaker to omit all the records that match your find request. The Records Omit Record command just omits the single record youre sitting on.

   2.2.3.2. Omit Multiple

   If you have a whole stretch of records you don't want, use Records Omit Multiple. It omits a contiguous group of records from the found set, starting with the current record. For instance, if you want to omit ten records in a row, navigate to the images/U2192.jpg border=0> Omit Multiple and type 10 in the dialog box that appears. Click Omit and the job is done.

   ---

   Tip: When you're done with your found set, and you want to see all the records again, choose Records Show All Records.

   ---

   Show Omitted Only. This command effectively puts every record thats not in the found set into the new found set, and takes every currently found record out.

   Here's how it works: Perform a find for the customers who have "CA" in the State field. FileMaker produces a found set of all California records for you, ready to print. After printing, choose Records images/U2192.jpg border=0> Show Omitted Only.

   Now you have everyone not in California in your found set, and you're ready to print again.

   POWER USERS' CLINIC
   Over-Omitted

   If you try to omit more records than possible (for example, you're on the third-to-last record and you ask FileMaker to omit 12 records), FileMaker will complain with this message. But don't take offense: It's also nice enough to fix the problem for you. When you click OK, FileMaker returns you to the Omit dialog box and changes the number you entered to the maximum number possible. You only need to click Omit again to get what you probably wanted in the first place.

   If you're looking at a record, and you know you want to omit it and every record after it, you might be tempted to look at the Record Number and the Found Count to figure out how many need to be omitted. But that's more trouble than it's worth. Just choose Records Omit Multiple and enter something really big, like 999, or 9999999. When you click Omit, FileMaker will complain, do the math for you, and enter the right value.

    

   ---

   Tip: You can also use Show Omitted Only if you forgot to turn on the Omit box in your find request. Instead of going back to Find mode to fix your request, just choose Records Show Omitted Only and youll get the same effect.

   ---

   Changing Multiple Records