Filemaker Pro Lesson
Browsing, Sorting, and Finding Records
Sample files needed for this lesson: Sample Database.fp7
Browsing, Sorting, and Finding Records
1. Open Sample Database.Fp7. You will notice some navigation tools in the area along the left side of the file. This is called the status area. It has changed from earlier versions of Filemaker Pro.
You will see that right now you are viewing the first record in the database. You can see this because the number "1" shows in the Record Number box. Also, the left page of the Book icon is blank. To move to the next record in the database, click on the right page of the Book icon. The next record number "2" now appears in the Record Number Box.
2. Move to record number 4 by clicking on the right page of the Book icon twice.
3. Another method to use to move through the database is to use the Bookmark. To use the Bookmark, drag it left or right until the number of the record you want shows in the Record Number box. Drag the Bookmark right to move to record number 30. Now drag the bookmark left to move back to record number 6.
4. A third method of navigating directly to a particular record is to type a record's number in the record number box, then press Enter. Click in the record number box and type "11", then press Enter.
5. A fourth method to navigate the database is to hold the CTRL key down and press the up arrow or down arrow. Using keyboard shortcuts is more efficient for data entry because you do not have to switch to the mouse to move from one record to the next.
Filemaker offers three ways to view database records: Form View, List View, and Table View. Form View is the default. It allows you to view one record at a time. You cannot use the scroll bars to scroll to the next record in Form view, but typically use the Book icon to move among records. List view is similar to Form view, except that you can scroll to view additional records. Table view lists each database record in a single row, with the database fields appearing in columns like a spreadsheet. In Table View you can adjust the width of each column by dragging the black bar that appears between each column heading. You can also right-click on a column heading to access a shortcut menu containing several options, including the "Set Column Width" command for setting the column width by pixels, inches, or centimeters.
6. Select View -> View as Table. Position the cursor on the line that divides the "State" and "Zip" column headings. Hold down the mouse button and drag to the left to resize the column.. Now resize the "Zip" column to be narrower.
7. You can also move columns to rearrange them. Position the cursor on the word "Last" at the top of the column of last names. Hold down the mouse button and drag to the left of the "First" column. Release the mouse button.
By default, Filemaker displays records in the order that they were entered into the database. If you wish to sort the records into a specific order using only one field, you can right click on the field name in the first cell of the column and choose to sort by Ascending or Descending Order.
If you wish to sort records by more than one field,you will need to specify your primary and secondary fields in the Sort Records dialog box.
8. Select Records -> Sort (Keyboard Shortcut: CTRL-S). Click "Clear All" to remove any existing sort order, if necessary.
9. Click once on the "Event" field in the list of fields under the Current File list on the left side of the dialog box. Click "Move" to add it to the "Sort Order" list on the right side of the dialog box.
10. Double click the "Last" field in the field list. To change the sort order from the default of "ascending" to "descending", click on the "Last" field in the "Sort Order" column then select the "Descending Order" radio button. Now click Sort.
11. To return the database to the default record order number select Records -> Sort then click "Unsort". A new feature in Filemaker Pro 7 is the ability to sort by the calculated values in a summary field. Select the layout Summary By Event from the layout selection list. This report groups records based on the contents of the field event. Because it is grouped on the field event we must sort by the field Event. However, if we click on the field Event in the Sort selection window, we can choose the new Reorder based on summary field checkbox option in the Sort dialogue box and choose to sort by either of the two summary fields All pledges or Number of Participants.
A powerful feature of a database like Filemaker Pro is its ability to locate records quickly based on criteria you specify. Filemaker Pro utilizes a feature often referred to as "query by example". In Find Mode you simply enter the information you're looking for in the appropriate field, then press the "Find" button in the status area. The database will then display only the records that match the criteria you specified. This group of records is the found set.
12. Let's locate the record for Kara Cooper. Reselect the layout Data Entry. Choose View -> Find Mode. Click in the "Last" field and type cooper. (You do not need to worry about capitalization because searches are not case-sensitive. Also you could type just a portion of the text you are searching for such as "coo". You would then find any records for participants whose last names begin with "coo". Click Find. The word "found" appears under the Book icon with a "1" below it. This lets you know that you have one record in your found set.
13. Now choose "Find" from the mode drop-down list at the bottom of the window. Click in the "State" field and type md. Click "Find" or press ENTER. The number "32" appears below the word "Found" under the Book icon, telling you that thirty-two records satisfy the search criteria. Click on the pages of the Book icon to browse the records in the found set.
14. Now select Records -> Modify Last Find. Notice that md is already typed in the "State" field. Place your cusor in the "City" field and type "Annapolis". Click Find or press Enter. You will see that of the 32 registrants from Maryland, only three are from the city of Annapolis.
You can also use logical operators (>,<, etc.) in your searches to find field values that fall within a specified range. You can type your operator symbols using the keyboard or click on the "Symbols" drop-down list that appears in the status area when you enter Find mode.
15. Choose View -> Find Mode. Click in the "Current Pledges" field. Select the "> greater than" from the "Symbols" drop-down list then type 100. Click "Find".
In addition to searching for records that are greater than or less than a specific value, you can also search for records whose value lies between two specific values.
16. Choose View -> Find Mode. Click in the "Current Pledges" field. Type 200 then two periods and 400. Click Find.
Filemaker allows you to find all records in which a particular field is empty.
17. Select View -> Find Mode. Click in the "Zip Code" field and type "=" (or choose "= Exact Match" from the "Symbols" drop-down list). Click Find. You will find that there is no zip code entered for Jordan Ahuja. Click in the "Zip Code" field and type "20004".
Instead of finding records based on specific criteria, you can omit particular records from the found set based on specific criteria.
18. Choose View -> Find Mode. Type md in the "State" field. Now check the "Omit" checkbox right above "Symbols" in the status area. Click "Find". If you browse the records, you will notice none of them are from the state of Maryland. To quickly display just the omitted Maryland records, select Records -> Show Omitted.
Filemaker Pro also allows you to perform complex multiple-criteria searches. The two types of multiple-criteria searches are AND searches and OR searches.
An AND search is one in which you specify multiple criteria, and records must meet all the criteria in order to be found. To specify an AND search, you enter Find mode and enter crteria into more than one field.
An OR search is one in which you specify multiple criteria, and records must meet one or more of the criteria in order to be found. To specify an OR search, you enter Find mode, enter criteria into one or more fields, then select Requests - New Request, and specify one or more additional criteria. The search will locate records that meet the critera in either of the two find screens. It is easier to use the Table View with an "OR" search because each of your search criteria is visible in separate rows.
19. Let's search for everyone from Maryland who has pledges of at least $300. Choose View -> Find Mode. Click in the "State" field and type md. Click in the "Current Pledges" field and type >=300. Click Find.
20. Now let's search from everyone from Maryland who has registered for both kayaking and bicycling. Select View -> Find Mode. Type md in the "State" field and choose kayaking from the "Event" drop down list. Now select Requests -> New Request. Type md in the "State" field and choose bicycling from the "Event" drop down list. Click "Find".
Browse the found set and notice that only participants from Maryland and registered for bicycling or kayaking are included. Important: each Find Request is exclusive. If we had not specified md in the State field of the second find request we would have found participants from Maryland registered for kayaking and participants from any state registered for bicycling.
Constraining and Extending a Found Set
Filemaker Version 6 introduced the features to constrain or extend a found set. Although these types of searches can be conducted with the "Add New Request" technique, the "Contrain Found Set" and "Extend Found Set" options save steps.
To Contrain a found set, you enter Find mode then enter the criteria for the records you wish to remove from the existing found set. Be sure to check the Omit checkbox. Then, instead of pressing the Find button in the status area, you select Records -> Constrain Found Set. The criteria you specified will be applied to the found set in memory.
Likewise to add additional records to the found set, you can enter Find Mode, input your additional criteria then select Extend Found Set to add these records to your original found set.
Choosing either Constrain Found Set or Extend Found Set acts like a Modify Find but applies the criteria to only the records currently in the found set.
Filemaker Pro 6 also introduced Find and Replace.
As in a word processing application, you can find and replace data across multiple fields, across a found set of records, or across text objects in a layout. You can search for data in any type of field except container fields and fields that are not modifiable (such as calculation fields).
In Browse, Find, or Layout mode, choose the Edit menu > Find/Replace. You can’t replace data in fields that are formatted as pop-up menus, radio buttons, or checkboxes. These fields will be counted and reported as skipped at the end of a Replace All operation. To work around this limitation, simply select a layout with the fields formatted as the default Edit Box type.
To find or replace invisible characters, such as tabs or carriage returns, manually copy and paste these characters from a field into the Find what or Replace with box.
If you have an RSS feed that
would be of interest
to faculty and students at the College of Education
please contact Mary Harrsch.
Last updated: 05/19/2005