- Overview of Finding Data
- Creating Find Requests
- Omitting Data
- Sorting Data
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Creating Find Requests
Begin by entering Find mode either from the command in the View menu or from the button at the top of the status area. (Custom layouts may provide Find buttons and other interface elements that automatically place you in Find mode.) This section shows you how to create a find request.
Creating the Simplest Find Requests
The simplest and most intuitive find requests are sufficient for many people. After you've entered Find mode, type a word or phrase into the field you want to search, and then perform the find. FileMaker locates all records in which the word or phrase appears at the beginning of words in the field into which you've typed it. (More complex searches are described in the following sections.)
The same simple method works for numeric, date, and time fields: Type the number, date, or time into a field, and FileMaker finds it.
Here's where your database design is important. If you've created a composite field containing city, state/province, postal code, and country, you're out of luck if you want to do a simple find. On the other hand, if you have created four separate fields (city, state/province, postal code, and country), typing "Ant" into city immediately finds Antwerp, just as typing "Italy" into country immediately finds all records for Italy. You might get unexpected results from searching on a composite field: Searching for "India" might match a country, a U.S. state (Indiana), and a city (Indianapolis).
Finding Duplicates
FileMaker provides a quick way of searching for duplicate values in a field. If you type !! in a field and then perform a find, you will retrieve all records that have duplicate values in that field (no matter what those values might be). If you need unique values (such as ID numbers), you can enforce uniqueness with validations as discussed in Hour 6.
Finding Text
FileMaker provides a number of more sophisticated searching tools for text. They're simple to use:
Use quotes to match one or more characters anywhere in the text field (not just at the beginning). "werp" finds Antwerp.
Use @ to indicate any single character (a wildcard). @arlton matches carlton, but it does not match Ritz-Carlton.
Use * to indicate any number of characters (0 or more) to match. *arlton finds Ritz-Carlton.
Use = to search for whole words. =Paris finds Paris but not Parisian.
Use == to search for an exact match. ==Tokyo Japan finds Tokyo Japan but not Tokyo, Japan (note the comma) and not Tokyo Japan Pacific Rim.
Use quotes around special characters such as @ and * to search for those characters. "*arlton" finds *arlton but does not find Ritz-Carlton.
-
To perform a case-sensitive search, you need to change the default language for indexing and sorting text in the field in question. You can do this when you want to conduct a search, or you can set up the field to always use case-sensitive searching. (Normally, you either use case-sensitive searching all the time on a particular field or not at all, so setting up the field to use or not use case-sensitive searching, instead of adjusting it for individual searches, is generally the way to go.) Use the Options button in the Define Database dialog to set the language to Unicode rather than English, French, or whatever language you are using. Figure 3.1 shows the Unicode setting. This forces FileMaker to treat lowercase and uppercase letters differently. If you use a natural-language setting (such as Italian), FileMaker uses the rules of each language to determine which characters are to be treated similarly. The Define Database dialog is discussed in Hour 13, "Creating a FileMaker Database."
)
Figure
3.1 Set the language of a field to Unicode to do case-sensitive searching,
sorting, and indexing.
Finding Numbers, Dates, and Times
Before starting to find numbers, dates, and times, it's important to note that FileMaker can apply various types of formatting to these values. (You'll see how to do that in Hour 4, "Introducing Layouts.") In a numeric field, the currency and percent symbols, as well as the number of decimal points, are determined by the format, which is stored separately from the value. Thus, the value 1.25 can be represented with various formats, as $1.25, 1.25%, 1.3, or 1.250. When you search, you find based on the value, not the formatted value. Searching for 1.25 finds all these values. (In a text field, however, the text is what it is. $1.25, 1.25, and 1.25% are different values when stored in a text field.)
The same principle applies to dates and times: The formatting is stored separately from the value. Thus, April 4, 2003 has the same value as 4/4/2003.
NOTE
Prior to FileMaker 7, it was possible to automatically coerce Boolean values to numeric ones, placing values such as yes or no in a numeric field. That is no longer possible. The GetAsNumber function converts such values to 0 (false) and 1 (true).
Just as with text, you can use some additional features for finding numbers, dates, and times:
//—Used to indicate today's date in a field that you are searching on.
?—Used to search for an invalid date or time. Note that if you set automatic validation options, you may not have invalid dates and times, but if you set the option to allow a date or time to be formatted as entered, such anomalies can occur. (There is more on this in Hour 4.)
Ranges of Data
You can find ranges of data by inserting symbols into the field(s) on which you are searching. Figure 3.2 shows the Symbols pop-up menu. (You can also type these symbols into a field manually.)
)
Figure
3.2 Insert symbols to search ranges of data.
The first five symbols are normal relational operators. For a field that contains a price, the entry
<5
finds all records that have a price of less than five (dollars/Euros/Yen or the like).
Ranges and relations also apply to text. The entry
<f
finds names such as Edgardo and Diana.
The language option described previously applies to ranges. If you use Unicode for the language, upper- and lowercase characters are treated differently. You can use two or three dots to specify a range of data. To find dates in the month of April (using standard U.S. month/day/year order), you can search on this entry in a date field:
4/1 .. 4/30
FileMaker assumes the current year if no year is supplied.
The other symbols in the Symbols pop-up menu were described previously in this section. You can either type them into fields or insert them into the currently selected field by using the pop-up menu. (The ~ for relaxed searching is used in Japanese.)