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.
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 126.96.36.199). 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.
188.8.131.52. 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).
184.108.40.206. 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.
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:
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.
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.
Since the found set already has only Smiths in it, there's no need to repeat that information.
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.
220.127.116.11. 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.
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.
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
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:
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:
On the other hand, neither of these would match:
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.
18.104.22.168. 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.
22.214.171.124. 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."
126.96.36.199. 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.)
188.8.131.52. 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.
184.108.40.206. 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.)
220.127.116.11. 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:
|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.
Timestamp fields (see Section 18.104.22.168) 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.
22.214.171.124. 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 firstname.lastname@example.org 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 email@example.com, 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 126.96.36.199. In other words, putting the search text in quotes ("firstname.lastname@example.org") will do the trick.
188.8.131.52. 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.
184.108.40.206. 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.
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
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.
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help