Productivity solutions guide
http://www.macworld.co.uk/filemaker
With the right database, FileMaker can simplify those repetitive daily tasks so that they happen at the click of a mouse. At the most basic level, FileMaker lets you create new records, find records that match particular criteria, and view, edit, sort, print, and delete existing records. It also imports data and exports records. Not a very long list, but taken together you and your FileMaker database can do some amazing stuff.
It makes things easier if you think about every FileMaker database as having two halves. Behind the scenes, there are the tables. Each table is made up of field definitions, which are the questions that the table can answer about the type of object that’s being modelled, and the data (or record) for each object being modelled. So a table of contacts might have field definitions for forename, surname, address, and telephone number, and the data for a variety of contacts.
In front of the tables sit the layouts. Layouts are blank forms that FileMaker fills in from the data in the tables. They can be created to show all or only some of the fields. So for a contacts database, you might have a data entry form containing all the fields (in order that you can enter all the data to start with) and a phone-list layout showing just the surname and telephone number. Each layout is linked to a specific table, but you can show related information for the particular record being looked at. For example, a layout linked to the contacts table might show contact details as well as the invoices belonging to that particular contact (provided that a relationship has been defined between the contacts table and an invoice table).
FileMaker can present layouts in three ways: in Form view, where each individual record appears on the screen alone; in List view, where the records in the found set appear vertically one after the other; and in Table view, where fields on a layout are used to build a spreadsheet-like list of current records.
As far as using FileMaker goes then, the layout controls all aspects of creating, editing, and viewing data (records). You create a new record by going to a layout and choosing Records
New Record, or pressing Command-N. Because the layout is linked to a particular table, FileMaker knows which table your new record should belong to. And because the layout tools are like drawing tools in, say, AppleWorks, you can make the layouts look however you like. Be warned; deleting a record is just as easy—Command-E or RecordsDelete Record will erase the record you’re working on.
Editing a record can be done in the same layout as you created it. Just click on a field and change the value. Layouts provide tools to make data entry simple and error-free, too—fields can be formatted as pop-up lists, pop-up menus, check boxes or radio buttons. Because the layout and the table are separate (and FileMaker handles the interface), a field for Salutation can be a pop-up menu on one layout and a set of radio buttons on the other. There are also useful shortcuts such as Command-minus and Command-; to enter the current date and time into fields.
A new option lets you confirm record and layout changes before they’re saved to the database. This feature ensures accurate data entry and allows experimentation with new layouts and structures. It can be disabled at any time. You can make a copy of the current file (as a backup) and then alter or delete records. That way, you can ‘undo’ large changes to the database.
Finding records in FileMaker is also done via a layout. Switch to Find mode (Command-F), click a field, type the data you’re looking for and hit the Find button. If you can’t see the field you need to use, you can switch between layouts, filling in any fields with criteria as you go. (See Find Mode).
Importing and exporting data is also layout-specific. To export a list of contacts and phone numbers, you need to be on a layout that’s linked to the table you want to export from. If the layout doesn’t have all the required fields, you can get at all fields from all related tables in the export or import dialog—but FileMaker needs to know which sort of thing (table) you want to start the import or export process from.
Having entered a large number of records, you may want to sort it. Or lots of diffierent orders. Choosing RecordsSort Records brings up the sort dialog, where you can specify which fields you want to sort by and in which order.
FileMaker also has a preview mode. This is useful for more than just seeing how records will print. FileMaker makes it easy to summarize sorted records by adding sub-summary parts to list-view layouts. Sub-summary parts are parts of the layout that appear only when printed or previewed, and when the records have been sorted by a particular field. You might sort invoices by month, and then include a sub-summary that totals up invoices, but which only appears at the change of every month. Monthly sales could be figures produced with just a sort and a print or a switch to preview mode. Because it’s the combination of sort and sub-summary set-up, you can use the same layout to produce quarterly or yearly sales figures just by changing the sort. Combine this with FileMaker’s scripting capabilities (see the Working to a script sidebar) and those tedious tasks can be done at the touch of a button.
To get to work, you need a suitable database file. There are three ways to create a database that’s right for you—from one of the templates that come with FileMaker (or that you’ve downloaded from a Web site), by converting an existing data file (an Excel spreadsheet, say) into a FileMaker database, or from scratch creating the tables and fields that you need.
That ordering isn’t accidental. Using a template is a no-brainer, but it may not be set-up to store all the different bits of information you need. Converting an existing Excel sheet is also straightforward (see Excel data à la carte), though you’ll end up with a database that fits your data exactly, but which lacks the formatting and added functionality of a template—you’ll need to add them. Creating a database from scratch isn’t too difficult (see Database DIY), but to do a good job it helps to see how other databases fit together.
FileMaker is very forgiving when it comes to switching approaches, and most people start by combining all three approaches—open a template, alter it to cope with existing data from a spreadsheet or text file, and then add features as required. Hopefully we can give you a taster on the templates together page.
The Status area is the dashboard for the power user. It shows how many records are in the current found set and how many are in the whole database. Clicking on the flip-book, editing the record number box, or dragging sliders make getting to the record you’re after a breeze.
Start tinkering with your database you’ll spend a lot of time in the Define Database dialog: use it to create tables, edit fields,
and link tables together using relationships.Get to it via FileDefineDatabase or for speed use Command-Shift-D.
FileMaker includes a powerful but easy-to-use scripting system called ScriptMaker. With it you create scripts to automate processes. Instead of remembering, 30 or so steps, once written, just click a button and sit back.
The remarkable thing about ScriptMaker is that scripts can be set up by double-clicking predefined script steps. The only typing involved is entering FileMaker calculations in exactly the manner as you would for calculation fields.
In Browse Mode you can view records as forms, lists or tables. Form view is just that—you see the fields and buttons, as you’ve laid them out for the current record as if it were a form to complete. List view is slightly different: you see the same layout, but can scroll down through the entire found set of records. This is great if you need to show a group of records, each as multi-line entity. Table view shows just the fields, set out as a spreadsheet. (See Excel data à la carte.)
Select Browse Mode by clicking the pencil icon at the top-left, pressing Command-B, choosing Browse from the pop-up menu at the bottom of the window, or choosing ViewBrowse Mode.
