FileMaker Pro
FileMaker is an easy to use but powerful tool for building and managing information databases. Many of these terms are common to all databases, but a few are specific to FileMaker. FileMaker works very similarly to other databases, so its main advantages over free or less expensive alternatives are 1) the relatively easy user interface and 2) cross-platform (Mac and Windows) compatibility.
Database: the entire collection of information, which may contain one or more tables.
Table: A table contains one or more fields that go together. For example, a table called People might contain fields for the names, addresses and phone numbers of employees.
A database table is similar to a spreadsheet. The columns contain the "fields" and the rows contain the "records."
Field: A field contains information of a particular type. For example, three fields in the People table might be Lastname, Firstname, and Phone number. Information is divided into fields based on what the user might want to search on or sort by.
Field Type: You can specify the type of data that is allowed or expected in a field, such as text, numeric, currency, date/time, etc., and can use "validation" to allow only entries of the proper type. Other fields types, such as summary and calculation, don't allow direct user input but report on the contents of other fields. For example, a summary field might tell you how many records are in the table, and a calculation might add up a series of values from other numeric fields in a record.
Field Input Types: You can specify the way data is entered into a field. For example, input can be a text box, a text area (larger than a box, with scrolling as an option), a pull down menu with a list of choices (with an optional Other... category where the user can specify), a list of radio buttons (where only one choice is allowed) or checkboxes (where multiple choices are allowed).
Value Lists: These are lists of choices available in pull-down menus, and with radio buttons and checkboxes. Using a value list rather than a text entry box reduces typos. What kind of data might go into a value list? Months of the Year, Days of the Week, a list of States, etc. Things where there are a limited number of well defined options that don't change much or ever.
Record: A record is all of the information in a table about a particular individual, for example. One record in the People table is for John Smith and his phone number. Another is for Mary Jones and her number. Records are like the rows of a spreadsheet, where each record contains information that goes together; all of the contact info for a particular employee for example.
Key Field: In a database with several tables, one must be able to connect information in one table that is related to information in another table. (When a database is set up in this way, it is called "relational.") For example, John Smith might be assigned a particular laptop computer. The People table, which contains fields about the person, is connected to the Hardware table, which contains fields about equipment, and the two are connected by a key field, such as Employee ID. FileMaker refers to key fields as Match criteria. The key field allows the two tables to associate the laptop with the person. For example, We can see that employee ID #453 is John Smith, and that he is using two devices; a Dell Laptop and an Apple iPad. Why not use Lastname as the key field? The problem is that it might not be unique (What if you have several employees with the last name Smith?), whereas Employee ID should be.
Two tables, People and Hardware, are connected by the Employee ID key field.
Relationships: The reason we have two or more tables is that it allows us to have "one-to-many" relationships. In our example, one employee has two devices. It would be hard to represent this easily in a single table. Instead, we just connect the people to the equipment using the Employee ID key field. It can get more complex. On each hardware device, there are multiple software programs and we might need to keep track of licenses. We could create a third table called Software and use a different key field, Hardware Serial Number, to connect the device to the programs installed on it. As shown, we can see that DreamWeaver and Photoshop are installed on the Apple Laptop and Office is installed on the Dell Laptop, both of which are assigned to John Smith.
Two tables, Hardware and Software, are connected by the hardware serial numbers.
Samples: Download the three FileMaker database tables, People, Hardware, and Software to see how they are connected.
Layouts: In FileMaker, each table can be viewed in one or more layouts. Other database programs sometimes refer to these as "Reports." Layouts allow you to rearrange fields for different types of users, and not all fields need to be displayed. Deleting a layout does not delete the table or the data within it, but just that particular way of viewing the information.
Views: Each layout can be viewed in several ways:
FileMaker Pro
Local Network: A FileMaker database can be used by one or more people on a local network at the same time, as long as those people aren't working in the same field of a table simultaneously. FileMaker limits the number of simultaneous users to 9. Each user must own a unique copy of FileMaker Pro. Both Mac and Windows are supported. The "Advanced" version adds some features but most are not necessary for an everyday user.
Web: Web access to hosted databases doesn't require a copy of FileMaker Pro, and makes them more broadly available. A built-in tool called Instant Web Publishing allows users to access the database using a web browser, but there are some limitations when using the "canned" web interface. Custom web publishing is much more flexible and renders pages faster. Try the web poll or mixed drinks databases for some live examples you can modify. However, after FileMaker version 6, custom web publishing became more complex. For modern custom web publishing, you need to know PHP, or possibly this...
FileMaker Server
More hosting options, but Server is expensive. With Server, you can have up to 250 simultaneous users. Programming for custom web publishing requires knowledge of PHP. Server Advanced allows an unlimited number of simultaneous users.
Compatibility: FileMaker comes out with a new version every few years; version 13 is the current version. Version 7 was a major change from previous versions, and all databases created in an earlier version than 7 need to be converted before first use. Versions 7 through 12 use the same file format, so data can be more readily transferred back and forth. However, it makes sense for everyone to be using the same version of the software for maximum compatibility. Academic copies (for education and non-profits) of FileMaker are cheaper, but are not eligible for upgrade discounts. Here's a table that compares features across the various versions of FileMaker.
Planning is the key to developing a good database solution. Before the development process begins, it helps to think about how the data should be organized, what the tables and fields should be called (because changing these later can cause things to break), and what kind of reports the end users will need. All of this can be mapped out on paper with very little technical knowledge, as long as the limitations of the software are well understood.