If you’re new to databases or just new to FileMaker Pro, you’ll need some background information before you’re ready to create the database (Mileage Master) that’s the subject of this book. This chapter will help bring you up to speed on databases in general and FileMaker Pro in particular.
A computer database is an organized collection of information on a specific topic. Databases can be created for home (recipe collections, greeting card lists, bowling league records), school (contacts, assignments, software registrations), and business (inventory tracking, invoices, petty cash disbursements). A complete set of data for one employee, recipe, team member, or invoice is called a record; the data elements within a record are called fields. Every field collects a single, discrete piece of information, such as a person’s last name, the quantity of an item in a recipe, or an employee’s Social Security number.
In this database, every record contains the data you’ve entered for one medical incident. Fields store a date of service, service type, description, charge, and so on.
If all you could do with a database was enter and flip through records, it would be no more useful than a stack of index cards. But databases also provide many capabilities to help you organize, view, find, and analyze your data.
Sorting. People seldom want to view their data in the order in which it was entered. Instead, you can sort a database by one or several fields, putting it in exactly the order you need at the moment—whether because you want to flip through records in the new order or need to create an impressive report.
Sorting on the Last field arranges everyone by their last name. But people with the same last name aren’t in any particular order.
Sorting on the Last and First fields still puts people with the same last name together, but arranges them in a useful order. Last names that are the same are further sorted by first name.
Calculations. You can create fields that perform simple or complex calculations within each record to compute the total for an invoice, the number of days until a report is due, or the exact length of a phone call based on its starting and ending times. Database programs also provide built-in functions that you can use to perform calculations, such as statistics, business math, extracting characters from text strings, and converting from one data type to another.
Scripts. To enable you to quickly and accurately perform repetitive, multi-step tasks, most database programs allow you to create scripts or macros. A script can perform a task as simple as switching to a Help layout or as complex as selecting a subset of records, sorting the database, printing the resulting report, and then restoring the data to its original order. The manner in which scripts are executed varies from one program to another.
Reports. One of the most important features of a database is the ability to generate reports. A custom report can be based on all or only selected data, include category groupings, and display a variety of summary statistics, such as counts, averages, subtotals, running totals, and grand totals.
You can build two types of databases in most modern database programs: flat file and relational. In a flat-file database, all necessary fields are defined in a single file or table. A relational database, on the other hand, is composed of multiple files or tables, related to one another by matching key fields in the files or tables. The main advantage of a relational database is that it avoids unnecessary data duplication. Rather than retyping the same information in every record that requires it (such as a student’s address), the data can simply be displayed by referencing the table in which it’s stored.
For example, an invoicing database might have three tables: Invoices, Customers, and Products. A relationship could be defined between Invoices and the Customers table, based on a Customer ID field. Another relationship could be defined between Invoices and Products, based on a Product ID field. When you create a new invoice and enter a Customer ID, the matching ID in the Customers table is found, enabling the customer’s address information to instantly be displayed in the invoice. When you enter a Product ID, the Products table is consulted and the information for that item is displayed. If you have repeat customers and products that you sell to many people, you’ll save an enormous amount of time by only having to enter IDs in each invoice—rather than retyping data that’s already stored in the Customers and Products tables.
FileMaker Pro is a mode-oriented program. Almost everything you do requires that you be in the proper mode: Browse, Find, Layout, or Preview. (Note, however, that a few operations, such as creating or changing field definitions or value lists, can be performed in several modes.)
Use Browse mode to create new records, edit data, and view existing records.
In Find mode, specify criteria for the records you want to view. (Criteria can be entered in any layout, such as this data entry layout.) Only records that match the criteria are displayed; all others are temporarily hidden.
A FileMaker layout is an arrangement of selected fields, field labels, and objects. You can create as many layouts as you need; each for a specific purpose, such as entering data, displaying a report, or printing labels.
When printed or viewed in Preview mode, this layout will generate the report below.
Layout mode
Preview mode
In addition to setting a single Find criterion, you can specify multiple criteria. You can search for records that satisfy any of the criteria (an Or search) or records that satisfy all of the criteria (an And search).
You can also perform a Find that locates records within a range, such as all people between the ages of 18 and 21.
In FileMaker Pro, scripts can be executed by choosing the script name from the Scripts menu or by clicking a button to which the script has been attached.
Conceptually, flat-file databases are much easier for new users to understand. While more powerful and flexible, learning to create relational databases has a much steeper learning curve.
A FileMaker layout can include as many or as few of the defined fields as you like. Think of a layout as simply one view into the database.
There is no requirement that a field be included in any of a database’s layouts. Some fields are intended only as a part of a calculation, for example.
The addition of simple scripts—often only a single step—can greatly enhance a database’s ease of use. For example, you can create one-step scripts to change to a particular layout, create a new record, delete the current record, or restore the database to a default sort order.
Any FileMaker database can also have a when opening or when closing script that automatically runs each time the database is opened or closed.