Manipulating data can illuminate a wide range of information and can allow business users to draw conclusions they may not have been able to perceive anecdotally. For example, in our consulting firm, we were able to analyze our time entry data and calculate the average amount of time we need for testing. This helped greatly for future estimating.
Becoming adept at using FileMaker Pro enables you to understand what information you can pull from the system, but, most important, it enables you to know what to ask for. In working with a developer, you can guide that person's priorities (or your own) based on a solid understanding of the platform.
Technique 1: Using Your Keyboard for More Speed
This one's obvious. Entering (-F) [Ctrl+F] brings you into Find mode. Tabbing takes you from field to field. The (Return) [Enter] key executes default values in dialog boxes, performs finds, and so on. (-up arrow) [Ctrl+up arrow] and (-down arrow) [Ctrl+down arrow] page through your data. You'll become much faster with FileMaker Pro if you take the time to learn your key commands. FileMaker's online help details all the key commands available.
For a complete list of all keyboard shortcuts in FileMaker, refer to our companion book, FileMaker 8 Functions and Scripts Desk Reference.
Technique 2: Working with Table View
User interfaces have their purpose, and more often than not greatly assist data entry and working with a given solution, but if you just need to look at the raw data in your system, you can opt to change to Table view from any layout in FileMaker Pro (assuming that your developer hasn't disabled the option). This gives you a bird's-eye view of your information. Don't forget that clicking on a column header sorts for that column. A second click re-sorts descending.
Technique 3: Replacing Data
Fairly often you'll run across cases in which you need to globally replace some data with other data. For example, perhaps you've changed a value list of vehicle types to read "auto, bike, boat, plane," rather than "bike, boat, car, plane." If you leave things alone after changing the value list, you'll have both "car" and "auto" data in your system. Enforcing the consistent use of terms is important in maintaining your data integrity. To quickly take care of migrating from an old value to a new one, follow these steps:
Choose Records, Show All Records. (Otherwise your change is applied to only your current found set.)
Place your cursor into the field in question.
In the case of a pop-up menu, you're out of luck. FileMaker Pro doesn't recognize a cursor in a pop-up menu. You need to do a little development (to be covered in Chapter 3, "Defining and Working with Fields"), copy the field to an open spot on your layout, and change its formatting to a pop-up list. Then don't forget to delete the layout field when you're finished.
Choose Edit, Find/Replace to open the Find/Replace dialog box (see Figure 2.30).
Figure 2.30. Find/Replace can step through your records, or can be applied across the entire database. Be careful: These functions cannot be undone!
Type your old and new values.
Choose All from the Direction drop-down menu (so that your entire database will be covered).
Depending on your preferences, choose Current Field or apply your change to entire records. We recommend just the selected field because that's much safer than accidentally changing all instances of a text string.
Click Replace All.
It's important to note: This is a function that cannot be undone! Be sure that you know what you're doing with your data.
Technique 4: Inserting Specific Information
The Insert menu is an oft-ignored source of handy time-saving commands. From a single menu choice or keyboard command, you can insert the current time, the current date, or your username into an active field.
In addition to that, Insert, From Index allows you to select from all the values in a given field from all records in a database. If you can't quite remember the spelling of a given item, or simply want to be perfectly consistent, this is a great way to see the data in your system and make a compatible selection. (This works only if the field in question allows indexing.)
To learn about field indexing, see "Storage and Indexing," p. 86.
Finally, there's a handy way to pull data from another record in your database. If three or four fields need to contain identical data to another record in your database, visit the source record first, and then via a List view or Table view jump (by clicking on the appropriate row) to the destination record. Click into the specific fields you want and choose Insert, From Last Visited Record.
Technique 5: Getting to Know Your Entire Database
This item isn't so much a technique as it is just common sense: One of the best ways to make the most of a FileMaker database is to learn how it works. Review all the layouts in your system, take a look at the fields you see, and explore other files (if there are others) in the solution. Be sure to discuss with your developer how the information fits together.
Technique 6: Using Multitiered Sorts
Sorting can be a fairly powerful way to derive meaning and see patterns in data. To make the most of the Sort Records dialog, don't forget that you can provide multiple sort criteria. For example, in a contacts database you could sort by Last Name, First Name, City, descending by Age, and finally by Pet Name.
You can also sort by the custom order of a value list. If you have, say, a status field that is managed by a value list of "open, pending, closed," you can sort by that order.
Technique 7: Using Multiple Windows
FileMaker provides you with a Window menu. If you'd like to work with multiple layouts at once, choose Window, New Window, and then navigate to the second layout in question (using either the Layout pop-up menu in the Status Area or the buttons a developer has provided).
Multiple windows are also useful when you open two windows looking at the same List view layout: It's possible for you to have two separate found sets. Imagine finding all the invitees of an event in one window and all the people who you've not yet invited in the other.
Technique 8: Applying Text Styling and Tabs
You can apply a wide range of formatting options to text within FileMaker Pro fields: bold, italic, font choice, color choice, and so on (see Figure 2.31). This information is preserved within FileMaker Pro, and you can copy and paste formatted text with other applications. For formats that support it, such as XML, you can export formatting as well.
Figure 2.31. You have a wide range of control over text appearance in FileMaker Pro.
There is another neat trick in FileMaker Pro: In any field you can establish an internal tab placement and apply tabs by using (-Tab) [Ctrl+Tab]. Choose View, Text Ruler. When you click into a field, a horizontal ruler appears above it, into which you can click to establish tabs. Double-click on a tab to set its properties: left, center, right, align to character, and whether to use a fill character.
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions