University of Glasgow Computing Service

User Note

FILEMAKER PRO on the Macintosh

System: Apple Macintosh

Number: UN 610/2

Author: Rod Ackland

Date: January 1994

Summary: This document deals with the FileMaker Pro database management system for the Apple Macintosh. It describes the operations necessary to create and maintain a database structure and use it to produce friendly screen interfaces and reports. The document includes a worked example showing how to create an address list.

Contents

1. Introduction 2
1.1 Databases and Database Management Systems (DBMS) 2
2. Creating a FileMaker Pro Database 4
2.1 Starting the Package 4
2.2 Creating a New File 4
2.3 Defining Fields 5
2.4 Field Options 7
3. Browsing an Existing Database 11
3.1 Getting Help 11
3.2 Entering Data 11
3.3 Creating New Records 13
3.4 Moving Between Records 13
3.5 Duplicating Records 13
3.6 Changing the View 13
3.7 Indexes 13
3.8 Editing Data 14
4. Finding and Subsets 14
4.1 Find Mode 14
4.2 Working with Subsets 16
5. File Operations 16
5.1 Opening Files 16
5.2 Saving Files 16
5.3 Recovering Files 17
5.4 Importing Data 17
5.5 Exporting Data 18
5.6 Import and Export Formats 19
5.7 Sorting Data 20
6. Example - Using an Address List 21
6.1 Creating the Database 21
6.2 Entering the Data 23
6.3 Selecting a Subset 24
6.4 Sorting the Subset 24
6.5 Exporting the Subset 25
6.6 Mail Merging with Word 4 or 5 25
6.7 Using Tables with Word 4 or 5 27
6.8 Using the Data with Excel 3 27
7. Further Information 27

1. Introduction

FileMaker Pro is a Database Management System (DBMS) that runs on an Apple Macintosh computer. An almost totally compatible version is available for IBM PC-compatible microcomputers. The concepts of a DBMS are described in Section 1.1 below. Previous versions were called FileMaker, FileMaker Plus and FileMaker II. "Pro" is short for Professional but this does not mean that this version is only suitable for advanced users. This document describes FileMaker Pro version 2.0 but notes differences from the previous version 1.0 where these are significant.

FileMaker Pro is a recommended standard at Glasgow University for simple data handling operations on the Apple Macintosh. Data can be exported to other packages, such as Word and Excel, or imported from packages which can export data in one of several standard formats. FileMaker is a flat file system, not a relational database. It is recommended for any simple data handling operations that involve single lists but may not be suitable for more sophisticated applications.

To run the program you will need:

The package will run on a machine with two floppy disk drives but will perform better if a hard disk is available.

This document is designed for people who have some experience in using a Macintosh, such as is gained by using the Macintosh Basics disks (which come free with each machine) in conjunction with User Note 601 The Apple Macintosh or by attending the Computing Service course Introduction to Apple Macintosh. The use of the mouse is described, but keyboard alternatives are available for many commands and are shown on the menus.

This document describes two of the four modes in which FileMaker Pro operates: Browse and Find. The other two modes, Layout, and Preview, are described in User Note 614, Further FileMaker Pro, which also describes the Scripting, Multi-access and Security facilities of the package.

1.1. Databases and Database Management Systems (DBMS)

A database is a collection of information, such as a telephone directory or a library catalogue, with a defined structure that makes it easy to use the information needed at any particular time. It does not matter if the data is held on paper in a filing cabinet or electronically in a computer: it is the structure which is important. For a database to be useful, it must let you phrase questions in a variety of ways, such as "Get me all records where the name is Smith" or "Get me the record where the phone number is 041 339 8855". The speed of computers makes it possible to perform tasks beyond the practical reach of humans, such as finding the number of times William Shakespeare used the words "chamber pot". However, the main power of databases lies in being able to carry out extremely quickly tasks that a well-disciplined person could perform reasonably quickly if they had the same data structured in different ways. A person can go to a telephone directory and find the number for a named individual, but a computer database can also respond just as quickly to questions about the owner of a particular number or the number of telephones in a defined area.

The computer software that handles data and makes this flexibility possible is called a Database Management System, or DBMS. It acts as a go-between for the user and the data, allowing the underlying data to be viewed and manipulated in various ways. FileMaker Pro is such a system that runs on the Apple Macintosh. DBMS range in complexity from those which allow one user to browse through a personal book list, to those allowing all the staff of a large organisation to view centrally managed data, such as student records. The latter type of application needs more control and security checks than the former.

If a file is to be handled successfully by a DBMS then it must conform to certain rules. The constraints vary from system to system and different DBMS allow or forbid different things. A basic requirement is that the data has a homogeneous structure such that the file will be a set of records, each of the same structure. Each record in the file will have a pre-defined set of fields. If the first record has a title field which is defined as containing from one to fourteen letters of the alphabet, then so will every other record in the file. When comparing a computer database to a manual one, a customer's record in the computer will usually correspond to their file in the cabinet and the computer file will correspond to the drawer, or set of drawers, containing similar files.

The field is the basic building block that makes up records. Each file in a database is defined as having a fixed set of fields. While some DBMS require the field structure of a file to be fixed before any data is entered, other DBMS, including FileMaker Pro, allow changes to the structure to be made fairly easily. The only requirement is that the structure is not changed while the file is actually in use. All fields have a specific type, such as numeric, date or alphabetic. They may have restrictions on what they can hold, such as upper case only or no negative numbers. The types and restrictions in FileMaker Pro are given in Section 2.3 below.

A powerful DBMS can link files together. A file of staff records can be linked to a file of department records which can be linked in turn to a file of faculty records. These links avoid the need to keep details of departments or faculties in the personal records of every member of staff. Such DBMS are called Relational Databases. FileMaker Pro is not relational but it does have facilities for linking files. These operate by copying the data into the record rather than by fetching it only when required. This can make the files much larger than they would be in a relational DBMS. If you are setting up a multifile database you should bear this in mind and consider using a full relational DBMS. Terminology can differ between DBMS and it is quite common for a microcomputer DBMS to call a file a database, and to refer to linking databases rather than files.

Much of the detail in a DBMS is concerned with how the data is structured but very few users approach this task wanting to structure data. Structuring is an inconvenient requirement forced on them by the DBMS, although FileMaker Pro makes it less painful than many others. Users often need to browse through their data on the screen, or to produce reports on paper. FileMaker Pro handles both these requirements through a single process, which it calls layouts. A layout describes which fields are to be shown, how and where they are to appear and what headings and totals are to accompany them. Layouts are described in User Note 614, Further FileMaker Pro.

This document follows the usual route, and describes how the horse - the DBMS - is stabled, fed and put between the shafts, before going on to deal with the more interesting topic of what sort of colourful cargoes may be put in the cart. Anyone who wants to sneak a look at the cart before the horse is recommended to look at the section Introducing FileMaker Pro in the tour file supplied with the package. This requires the Hypercard package to be mounted on your machine.

2. Creating a FileMaker Pro Database

2.1. Starting the Package

FileMaker Pro is started in the standard Macintosh way by opening its icon on the Desktop either by double clicking or by selecting Open... from the File menu.

If this is the first time that a new copy of FileMaker Pro has been started, a dialogue box will appear inviting you to personalise your copy with your name, department and serial number. These are registered inside the package and will appear at start-up time on subsequent runs.

Each copy of FileMaker Pro has within it a unique code number, which is not the same as the serial number. When the package is used on a network, a check is made at start up to see if the number is already in use elsewhere on the network. If it is, the package will tell you so and refuse to start. All users of the package on a network must, therefore, have copies installed from separate master disks.

2.2. Creating a New File

If the copy of FileMaker Pro has already been registered, it will then display a dialogue box similar to that shown below, requesting the name and location of the file to be used for data. The appearance of the dialogue box depends on the system release being used.

You are shown any FileMaker Pro files which are held in the same folder as the package. To create a new file, click on the New button and the dialogue box shown below will appear:

Type the name to be given to the new file. If you want to keep it somewhere other than next to the program, then use the pull-down Folder list and Drive buttons in the usual way. When the name and placing are correct click the New button.

2.3. Defining Fields

A database file has its structure defined in terms of what fields each record will contain and what the characteristics of these fields will be. When a new file is created the field definition dialogue box shown below appears:

Type in the name you want to give the field. The name can be up to 63 characters long, and can contain any characters including spaces. It is advisable to avoid the characters and words which are used as operators in mathematical formulae, the most common of which are listed below:

+      
-
*
/
^
&
=
=/ not equal to
> 
<
> _ greater than or equal to
< _ less than or equal to
, 
AND
OR
NOT

A field name containing such operators cannot be used in calculations as it will be misinterpreted. For the same reason, fields should not be given the same names as functions, such as MAX, MIN, SUM or ROUND. If a name has the potential to clash with calculations you will be warned and asked if you wish to change it.

You now need to decide what type of information the new field is to hold. The seven types are described below. The type is selected by clicking the appropriate radio button. The default is Text for the first field defined and on subsequent fields will be the type used for the previous field. Thus, if a field is defined as of type date, the next field will be of type date unless explicitly changed.

Click the Create button once the name and type of a field are defined. It will be added to the list in the window and the next field can be defined. When all fields have been defined correctly, click the Done button to move to the next stage.

2.3.1. Making Changes to Field Definitions

If you accidentally click Done before you have finished, the Define Fields... command on the Select menu can be used to go back into the Field Definition dialogue box. This command can be used at any time if you need to go back and make alterations.

A field name can be changed by selecting it from the list, typing the new name and clicking the Change button. A field type can be changed by selecting it from the list, selecting the new type and clicking Change. A field can be deleted by selecting it in the window and clicking the Delete button.

Some of the above changes may have side effects once data has been inserted into the records and layouts have been created using the original names and types. Read any warning messages carefully, and if in doubt consult the Advisory Service on ext 4831 before taking any action that might destroy data.

2.3.2. Field Order

Several facilities in FileMaker, such as exporting data and creating new layouts, need to know the preferred order in which fields should follow each other. By default, the order is that in which fields are created, called Creation Order. Four other orderings are available, and can be selected from the View by drop down menu at the top right of the Field Definition dialogue box.

As well as Creation Order, fields can be ordered alphabetically by field name, grouped by field types, so that all fields of a given type appear together in the list, or by Custom Order. To create a custom ordering, use the double arrow to the left of a field to drag it up or down the list to a new position. There can only be one custom ordering at any one time. Any new custom ordering defined will replace the previous one.

The facility to switch between orderings was introduced with version 2.0 of FileMaker Pro. Version 1.0 has the ability to drag fields to create a new ordering, but this ordering is then the only one available.

2.3.3. Field Types

There are seven types of field:

Text
Can contain anything you can type, from a single character to several pages. Return characters can be included to create new lines. The maximum field length does not have to be specified as text fields expand and contract as necessary to hold the actual data stored in each record.
Number
Can contain anything you can type with a maximum length of 255 characters on a single line. When the field is used in calculations or displayed with a numeric format, all characters that do not make sense as part of a number will be ignored.
Date
Contains a date, which must be input in the format 3/7/97 or 3.7.97 but can be displayed in a wide range of formats.
Time
Contains a time, which can be input as hours and minutes or as hours minutes and seconds. Either the 12 or 24 hour clock can be used, for example 12:24:34, 15:45, 8:56 pm.
Picture or Sound
Contains pictures, sounds or QuickTime movies. Pictures can be in TIFF, PICT, MacPaint or EPS format. Sounds are in the standard Macintosh resource formats and can be brought in via the clipboard or recorded directly with the microphone. Version 1.0 of the package can handle only pictures.
Calculation
Contains the result of a calculation based on other fields within a record. Examples are a numeric calculation, such as a sum or average, a textual concatenation of one or more fields, and part of a date field extracted and converted to text.
Summary
Contains the result of a calculation based on the values of one field over a group of records. The file must be sorted on a field other than that on which the calculation is based. This sorting field is called the grouping field. The calculated value will change whenever the grouping field's values change.

2.4. Field Options

Options govern the action taken when new data is entered. To set the options on an existing file, open the definition dialogue box by selecting the Define Fields... command from the Select menu. Then select the relevant field with the mouse and click the Options... button.

When a Calculation or Summary field is set up, an Options dialogue box opens automatically to allow you to describe the calculation to be performed. With the other field types, the Options are genuinely optional, and can be set up during creation or later.

2.4.1. Entry Options

If the field is of type Text, Number, Date, Time or Picture a standard Entry Options dialogue box, such as that shown below, will appear. Options not relevant to a particular type will appear in grey. Some options are available only once other options are set and are grey until then.

The Auto-enter options allow you to specify values which will be inserted into records automatically on creation or modification. When the first box is selected, clicking on Creation Date, dragging down and selecting will allow a choice of dates, time or names. The name inserted is the username of your Macintosh which is currently set in Chooser with System 6 or earlier, or with the Sharing Setup control panel with System 7. When the second box is selected, you can specify a serial number by giving the value to go into the next record created and the increment to be added to it for subsequent records. When the third box is selected, any string of characters can be inserted into the data box and will be copied to the relevant field in all new records.

The Prohibit modification of auto-entered values box becomes available whenever any of the three auto-enter boxes is selected. It prevents auto-entered values being altered by anyone without the authority to use the Define Fields... command.

The verify options allow you to check the accuracy of data typed into new records. Depending on the password settings chosen (see User Note 614, Further FileMaker Pro), attempts to input invalid data can either produce warning messages or be rejected altogether. Selecting the different options will produce the following effects:

not empty
New records must have a value entered in this field.
unique
Forces a new value to be different from existing values.
an existing value
Forces a new value to be the same as some existing value.
of type
Limits values typed into text or number fields to those of a particular format. Number format here means valid numbers, and is more restrictive than the default rules for a simple Number field. It can be used to restrict number fields to contain only actual numbers.
from and to
Specifies a range within which values must lie.
use a pre-defined value list
Specifies a set of values. A menu will offer these as choices when a new record is created. Click the Edit Values button to edit an existing value list.

2.4.2. Using the Look Up Option to Link Files

The Look up values from a file box allows you to specify that a field in a new record is filled by reference to another file. This is not a relational connection between the files: data is copied from one file to the other and so takes up space in both. It takes effect only when a new record is created or when the Relookup command on the Edit menu is selected.

Selecting the box brings up a standard file opening dialogue box. Select the file from which data is to be taken. If this file is already open, click Cancel. The dialogue box shown below will appear:

The current file is the one in which you are defining fields and the lookup file the one from which data is to be fetched. The latter can be selected using the Set Lookup File... button. Click and hold on the box below Copy the contents of: and drag down to select the field you want to copy data from. Repeat this with the two boxes on the next line to select the two fields to be used to make the connection between the two files. The field on the left is in the lookup file and the field on the right is in the current file. This will have the following result when a new record is created:

  1. Using the current file the value will be extracted from the field specified on the second line.
  2. The lookup file will be searched for the first record with an entry matching the value from the current file.
  3. The corresponding value of the field specified on the first line will be copied across to the new field.

The search is done on tokens within the field rather than the full contents. Details of token searches are given in Section 3.7.

The option buttons specify the action to be taken if no exact match is found.

2.4.3. Repeating Fields

A repeating field is one that can hold several separate values of the same type. Any field type except Summary fields can be defined as a repeating field by selecting the Repeating field button in the Options dialogue box and specifying the maximum number of values to be allowed. A repeating field is regarded as one object when layouts are defined but all the values are included in any calculations or summaries based on the field.

2.4.4. Calculation Field Options

When a new Calculation field is defined the calculation options dialogue box shown below will appear:

This dialogue box is used to create the formula used to calculate a value for the field from the other fields in the record. The formula can be typed in or assembled by clicking on the different elements. All field types except Picture/Sound and Summary fields can be used in calculations. Relevant field names are listed at the top left. Simple operators and numbers are available from buttons in the centre. Less common operators and functions are available from the two right-hand boxes. A pull-down menu at the bottom of the window is used to specify the field type of the result.

2.4.5. Summary Field Options

Whenever a new Summary field is defined, the summary options dialogue box shown below will appear:

The field to be summarised is selected by clicking and dragging down on the right-hand box. The function to be applied in the summary is selected by clicking and dragging down on the left-hand box. The grouping field is defined during layout design which is described in User Note 614, Further FileMaker Pro. There are nine options:

Total
Produces a cumulative total of all values, including any repeating values, for the field being summarised for each set of records which have the same value of the grouping field.
Running Total
Produces a cumulative total of all values seen so far, updated each time the grouping field changes, by selecting Total and checking Running Total.
Average
Produces the arithmetic mean of all values in the field being summarised, for each set of records which have the same value of the grouping field, using only fields that contain numeric data. The Weighted by option can be used to weight the average by the value of another field.
Count
Produces a count of the records that contain a valid value in the field being summarised, for each set of records which have the same value of the grouping field.
Running Count
Produces a count of all valid values seen so far, updated each time the grouping variable changes, by selecting Count and checking Running Count.
Minimum
Produces the lowest number, earliest date or earliest time in the field being summarised, for each set of records which have the same value of the grouping field.
Maximum
Produces the highest number, latest date or latest time in the field being summarised, for each set of records which have the same value of the grouping field.
Standard Deviation
Produces the standard deviation from the mean of the values in the field being summarised, for each set of records which have the same value of the grouping field. The formula is n-weighted following the population standard deviation.
Fraction of Total
Produces the ratio of the total for the current value of the grouping variable to the value for the whole set of records currently selected.

The field definition process is completed by clicking the Done button.

3. Browsing an Existing Database

When the field definition process is completed, FileMaker Pro changes to Browsing mode. This is one of four modes within the package, the others being Find, Layout and Preview. The name of the current mode appears at the bottom left of the window. You can switch to another mode by clicking either here or on the Select menu and then selecting a new mode. The bottom left button is not present with version 1.0 of the package.

3.1. Getting Help

FileMaker Pro comes with an extensive on-line help system. Providing the help file supplied with the package has been installed, this help system can be accessed by the Help command on the Apple menu or, with system 7, by the Help menu. Help is available whenever the Menu bar can be accessed. The menu bar is disabled whenever a dialogue box is open, which means, in particular, that Help is not directly available during Field Definition. However, as described above, it is possible to leave the dialogue, use Help, and then return to it with the Define Fields command.

3.2. Entering Data

The display shown in Browse mode will depend on the field structure created and the layout in use. After creating several fields as described above and using the default layout, it will resemble the example shown below:

Only the field names are visible. When the Tab key is pressed, the field value areas will appear with the cursor in the first field, as shown below. The different sizes of box reflect the different field types. Calculation and Summary fields will not open out to accept input as they cannot be typed into directly:

Data is inserted by typing. The Tab key moves between fields. Any input options which have been set govern what can be typed in. If necessary, the outline rectangle will expand and cover other fields to display the data. When you move to the next field the rectangle contracts to its original size showing part of the data although nothing has been lost.

Picture/Sound fields can have data inserted in the following ways:

The Paste Special command on the Edit menu can be used to insert the current date, current time or to copy the value the field had in the last record entered.

When you have inserted all the data for a record, press the Enter key. This writes the data to the disk. Until you do this the data exists only in the memory of the computer. Moving to a new record will also write the data to the disk.

3.3. Creating New Records

So far, we have only one record. The record count at the left of the screen shows 1, and the loose-leaf book above it is at record 1. Select the command New Record from the Edit menu and a new, blank record will appear.

3.4. Moving Between Records

The loose-leaf book at the left of the screen shows the number of the current record. Clicking on the pages moves one record forward or backward. Dragging the bookmark moves several records, and clicking on the number and typing a specific number moves you directly to that record. Holding down the command key and pressing the Tab key will move one record forward. Holding down the command and option keys and pressing Tab will move one record back.

3.5. Duplicating Records

As an alternative to creating a new blank record the current record can be duplicated by selecting the Duplicate Record command from the Edit menu. The duplicate is placed after all existing records and becomes the current record.

3.6. Changing the View

The window through which you view the data can be moved, resized or scrolled in the standard Macintosh way.

Additionally, the view within the window can be resized. At the bottom left is an indicator which gives the current size of the display and two mountain range icons. Clicking the left range contracts the view to 50% so that more data is visible. The right range has the opposite effect. The view can be set to between 25% and 400%.

The icon to the right of the mountain ranges is a toggle switch which hides or reveals the controls down the left hand side of the window. When they are hidden, a greater width of data becomes visible.

Selecting the View as List command from the Select menu will display all currently selected records. They can be viewed by scrolling if necessary. To return to a one record display, de-select the menu command. When you scroll, the current record remains that in which you last selected a field and its number is shown on the loose-leaf book.

3.7. Indexes

FileMaker Pro creates indexes on each field as data is entered. When a field is selected for input, selecting the Paste Special command from the Edit menu and choosing From Index will show the index entries. Values can be copied from the index to the field by selecting an entry and the Paste button.

FileMaker Pro builds its indexes by selecting each word of a text field, the numeric portion of a number field, each date in a date field or each time in a time field. This indexing procedure is reflected in the search and linking operations. This whole word indexing, called in some other DBMS a token index, means that some searches may not perform as expected. Consider a file with entries for FileMaker, FileMaker Plus, FileMaker II and FileMaker Pro. Using the lookup facility these have been set up to read the price from a second file but each entry will read and display the price for FileMaker. This is because in the index the tokens are "FileMaker", "Plus", "II" and "Pro". The correct price is obtained by using option and space simultaneously between the words instead of space alone. This makes the words form single tokens. However, a search for occurrences of Pro, Plus or II will no longer find these records because these words now fall within a token, not at the start of one.

3.8. Editing Data

Data in records can be changed while browsing if the user has been given a password with sufficient privileges or if no password has been set as described in User Note 614, Further FileMaker Pro. Select the relevant field and type the new data.

Records can be deleted using the Delete Record command from the Edit menu. The program will check that you really do want to delete before it removes the record.

4. Finding and Subsets

4.1. Find Mode

Searching for specific records using the contents of a field is one of the most powerful features of a DBMS. In FileMaker Pro this is done using Find mode. To switch to Find mode select Find either on the Select menu or the pop-up mode menu in the bottom left hand corner. A blank search request form appears, as shown below:

Insertion rectangles will open for all fields except those of type picture/sound or summary. The criteria to control the search can be inserted into these rectangles either by typing or by using the buttons to the left of the window. The default is to find all records where the relevant field contains a token starting with the entry in the find field. This can be varied with the use of the operators listed below, which can be obtained by clicking on the Symbols button and then selecting from the submenu, or by typing:

Symbol
Put these at the start of the entry
=
Exact matches with the entry.
<
All values less than the entry.
<_
All values less than or equal to the entry. (Press the option key and < simultaneously.)
>
All values greater than the entry.
>_
All values greater than or equal to the entry. (Press the option key and > simultaneously.)
Symbol
Put this between two entries
...
All values in a range eg 5...10 (Press the option key and ; simultaneously.)
Symbol
Put this instead of an entry
!
All duplicate values.
/ /
All records with today's date in the field. (Press the / key twice.)
?
Invalid dates and times.
Symbol
Put these within entries
@
Exactly one wild card character. Thus, "J@nes" matches "Jones", "Janes", etc
*
Zero or more wild card characters. Thus ""J*nes" matches "Jones" and also "Jooones" and "Jnes"

If values are entered in more than one field the effect is to search for records which match the contents of the first field AND that of the second field. To search for alternatives select the New Request command from the Edit menu to set up a second request, or choose Duplicate Request to make a copy of the existing one which can then be altered. The loose-leaf book controls movement between requests. With two or more requests set up, the effect is to search for records which match the first request OR the second one.

As FileMaker Pro performs token searches you cannot search for text within a word. All comparisons are with each token in the field, not with the whole field. A search for =fred will find a record with fred jones in that field, unless the space has been typed as option/space to create one token.

Click the Find button and FileMaker Pro will find all the records which match your request. If you select the Omit box before clicking Find it will find all records that do not match the request. Requests to find and omit can be mixed in the same find operation.

If the search fails to find any matching records you will be invited to try again. At the end of a successful search you will be returned to Browse mode.

4.2. Working with Subsets

When a Find has been completed the Browse window changes and the Found count shows how many records satisfied the search criteria. These records, are called the Found Set and are now the only ones being browsed. Record n is the nth record being browsed and not necessarily the nth record in the file.

Choosing the Find All command on the Select menu returns you to the full set of records. The Refind command on the Select menu will take you into Find mode with the selections as they were. Click Find for an exact rerun or make changes first.

The Find Omitted command on the Select menu makes the set of found records the set of omitted records, and vice versa. Contiguous records within the found set and individual records can be moved from the found set to the omitted set with the Omit and Omit Multiple... commands on the Select menu.

The Delete Found Set command on the Edit menu deletes permanently and instantly all records in the Found Set.

Finds are not cumulative: a second Find operates on the full file, not on the subset resulting from the previous Find.

5. File Operations

5.1. Opening Files

Several data files can be open at once. Each file has a screen window and a full set of operations. These are performed independently of other files unless a lookup operation is specified.

Each new file is opened using the Open... command on the File menu and can be closed with the Close command on the same menu, or by clicking the close box.

Open files are listed on the Window menu and the currently active file is ticked. A file name that appears in brackets has been opened for a lookup operation (see Section 2.4.2) but does not have an open window.

The windows can be moved and resized so that they lie side by side or partially or fully obscure each other. It is possible to move between files by selecting from the Window menu or by clicking in any visible window to make it the active one.

5.2. Saving Files

Word processing and spreadsheet packages hold their files in memory until an explicit command to save is issued. By contrast, database files are saved automatically as each record is updated. There is less risk of changes being lost but a greater risk of mistaken changes being made. You cannot abandon a session which has gone wrong and start again from scratch as the changes have already been recorded.

Back-up copies should be taken regularly, either within Finder on the Desktop or within FileMaker Pro using the Save a Copy as... command on the File menu. This produces a file saving dialogue box with a menu at the bottom offering three options: copy of current file, compressed copy and clone. The compressed copy takes longer to create and re-open but saves space. Clones are empty files with the same structure and layouts but no data and are used to create new files modelled on old ones.

5.3. Recovering Files

Like all computer software, FileMaker Pro will fail occasionally or it may be working when something else fails. When restarted, the package detects automatically if a file has not been properly closed and can recover the situation. If the package detects the problem it offers to solve it but sometimes it will simply reject the file. In such cases the Recover... command on the File menu can usually open the file with a forced recovery. While no guarantees can be given, these operations have so far proved remarkably resilient on local files of several thousand records.

5.4. Importing Data

Importing data to FileMaker Pro needs three things:

To import data into FileMaker Pro the following steps should be carried out:

  1. Open a suitable FileMaker Pro file and select Browse mode.
  2. Select the Import/Export... command from the File menu, and then Import Records from the submenu. A file opening dialogue box will appear with a pull down menu at the bottom. This offers a range of file formats described below in Section 5.6. All files of the selected format appear in the window.
  3. Choose the relevant file. A first attempt at deciding what goes where is made on the basis of the field names. If the two files are clones or have very similar field names, FileMaker may simply perform the transfer but in other cases it will ask for assistance. A dialogue box similar to that shown below will appear:

On the left are the field names from the external file, if the external format has such data, or the contents of the fields on the first line if it does not. The Scan Data buttons can be used to search for a more representative record if necessary.

On the right are the fields available in the FileMaker Pro file. Fields that cannot accept data, such as calculation and summary fields, are greyed. An arrow indicates that the field on the left will be copied to the field on the right.

The FileMaker Pro fields on the right can be dragged into new positions. The arrows can be clicked on or off to stop or force a transfer.

If the record on the left is not representative and there are more fields in records elsewhere in the file than are shown, it is possible to switch on transfer arrows to fields on the right even though nothing appears on the left. The ordering is significant and, for example, the seventh line will be a transfer from the seventh field in each incoming record. It is not, however, possible to transfer more incoming fields than the number of acceptable fields in the existing FileMaker Pro file. You can go to the field definition dialogue box and create more fields. When you return to the import dialogue it will be as you left it.

New records can be added to those in the existing file, or they can replace the current found set. This latter option means the whole file if there is no current found set. In both cases, the imported records become the new found set. When you are satisfied click OK. If a mistake has been made it is easy to reverse the import using the Delete Found Set command. This will not reinstate the original records if an existing found set was deleted.

Imported data is not subject to the entry options (Section 2.4.1) which apply only to newly created records. The Replace command on the Edit menu provides similar facilities.

5.5. Exporting Data

To export data from FileMaker Pro the following steps should be carried out:

  1. Make the records you want to export the current found set using the Find command.
  2. Select the Import/Export... command from the File menu and then Export Records from the submenu. A file opening dialogue box will appear with a pull down menu at the bottom. This offers a range of file format options, described below in Section 5.6.
  3. Select the most appropriate option and specify the file name and placing. Click the New button and a list of the fields in the file will appear as shown below:

Picture and summary fields are greyed to indicate that they cannot be exported. Other fields are ticked to indicate that the field is to be exported. These ticks can be switched on and off by clicking. The export order can be changed by dragging.

Some export formats offer the option of formatting individual fields using the current layout. Formatting using a layout converts the data, using the format specified for the first occurrence of each field in the current layout.

Click OK and the data will be exported to the specified file.

5.6. Import and Export Formats

FileMaker Pro supports the following file formats for import and export of data:

FileMaker Pro
FileMaker Pro's own format, created with the Save a Copy As... command. Note that this is not offered as an export format - use the Save a Copy As command instead.
Tab-Separated Text
For transfer to other Macintosh packages that cannot accept one of the special formats. Output is ASCII text, with fields separated by tabs and records separated by returns. All Macintosh packages accept this format as a Text file. Packages with a column-based structure read the data into columns using the tabs as delimiters.
Comma-Separated
For transfer to packages on other computers that cannot accept one of the special formats. Output is ASCII text with fields separated by commas and records separated by returns. All Macintosh packages accept this format as a Text file. Column-oriented packages place all the data in one column unless they have a specific comma separation option when reading text files.
Merge
Field names are placed in the first record. Formatting of the actual data is as for Comma-Separated, but uses semicolons as separators instead of commas.
SYLK
For spreadsheets such as Excel and Wingz. Data is stored in rows and columns.
DBF
For dBase III and IV under DOS. A different format is used by dBase Mac and dBase II which are incompatible with each other and with this format.
DIF
For some spreadsheets, including VisiCalc and AppleWorks.
WKS
For the Lotus 1-2-3 spreadsheet and accepted by many other spreadsheets, including Excel.
BASIC
A variant of comma-separated text suitable for use with Microsoft BASIC.

5.7. Sorting Data

Most DBMS have two separate operations:

Data Sorting
Records are actually moved into a new order
Indexing of data
An index is used to view the records in a different order but their actual position remains unchanged.

FileMaker Pro does not have a sorting function but calls its indexing function sorting. Thus a request to sort a group of records is a request to view those records using an index. When the found set changes and the index ceases to be correct, the package reverts to unsorted order. Using the Find command will unsort the records and so Find should be used before Sort if you want to produce a sorted subset.

To perform a FileMaker Pro Sort carry out the following steps.

  1. Make the records you want to sort the current found set.
  2. Select the Sort... command from the Select menu. A dialogue box will appear as shown below:
  3. Select the field that is to be the primary key, i.e. the most important one in the sort. Click the Move button to transfer it to the right hand window.
  4. Choose the sort ordering from one of the buttons at the bottom of the window. Ascending is alphabetical order for text, low to high for numbers, and chronological for dates and times. In each case descending is the opposite of ascending. Custom Order sorts to match the order in any value list created as an entry option for the field (see Section 2.4.1).
  5. Move as many fields to the sort window as are needed to specify the sort order fully. The second field specified defines the order of all records with the same value for the first field, and so on. The order for each field used in the sort can be individually specified as ascending, descending or custom, and an icon appears beside the name to show which order is being used. The order setting for each field can be changed by selecting it and clicking another button. A field from the sort keys list cannot be changed or reordered. If you have made a mistake, or want to specify a completely new set of keys, select the Clear button and start again.
  6. Click the Sort button and the sort will be performed. You will be returned to Browse mode, with the marker at left now indicating sorted. The order and the marker will remain until some operation disturbs the index.

Picture fields cannot be specified as sort keys. Other fields can be specified only once. Summary fields can be included if the Include summary fields button is selected, but they will automatically move to the bottom of the key list. As a result, their order of specification only matters relative to other summary fields.

An alternative to performing the sort is to click the Done button. This will return you to Browse mode. Whether you click Sort or Done, the sort keys remain specified and will be offered the next time the command is invoked.

If, while viewing a sorted found set, you wish to see the unsorted order, select the Sort command and click the Unsorted button.

A physical sort of records into a new order can be achieved by following the steps below:

  1. Perform a FileMaker Pro sort to view the records in the desired order.
  2. Use the Save a Copy As... command to create a clone with an identical structure but no records.
  3. Open the clone and import the data from the original file into it. The import uses the index and copies the records to the new order.
  4. Discard the original and rename the copy.

6. Example - Using an Address List

This example shows how to set up a FileMaker Pro database to maintain an address list, how to pass data to Word for mail merging and to Excel to produce graphic statistics.

6.1. Creating the Database

The following steps should be carried out:

  1. Open FileMaker Pro by double clicking the icon.
  2. Click New, type the name AddressList and click New again.
  3. Create the following fields in the Field Definition dialogue box by typing the field name, selecting the relevant type and clicking Create: Field Name
  4. Field Type Surname Text First Name Text Title Text House Text Street Text Town Text PostCode Text Credit Balance Number Last Update Date
  5. Click on Title to select that particular field and then click the Options button.
  6. Check the box beside Use a pre-defined value list. A window will appear in which you can enter the values which are to be acceptable in this field. Enter Mr, Mrs, Dr and Prof, pressing the Return key after each entry. The window will now look like this:
  7. Click the OK button to close this window and return to the Options dialogue box.
  8. Click the OK button there to return to the field definition dialogue box. You have just set up the title field to select from a predefined list. If you find you need extra entries, such as Ms, you can go back and add them.
  9. Click on Last Update to select the field and click the Options button.
  10. Check the first Auto-Enter box and then scroll down and select Modification Date. Click OK. The field definition dialogue box will now look like this:
  11. Click the Done button and FileMaker Pro will switch to Browse mode.

6.2. Entering the Data

The following steps should be carried out:

  1. Check that the program is in Browse mode.
  2. Press the Tab key to open up the fields as shown below:
  3. Type Jones into the Surname field and press Tab.
  4. Type Brenda into the First Name field and press Tab.
  5. As you move to the Title field a scroll box will open up below it, covering the next few fields and containing the options. Double click on Mrs.
  6. Type 5 into the House field and press Tab.
  7. Type Links Road into the Street field and press Tab.
  8. Type Invermuckie into the Town field and press Tab.
  9. Type MW5 4BB into the Postcode field and press Tab.
  10. Type 200 into the Credit Balance field and press Tab. User Note 614, Further FileMaker Pro describes how to display this figure in pounds and pence.
  11. Do not type into the Last Update field. The current date will have been inserted automatically.
  12. Hold down the command key and press N to create a new record.
  13. Hold down the command key and type a quote ('). FileMaker Pro will automatically insert the contents from the previous record into the first field.
  14. Press the Tab key to move to the First Name field and type Arthur.
  15. Press the Tab key to move to the Title field. Double click on Mr.
  16. Hold down the command key and type a quote (') followed by a Tab for each field down to PostCode to duplicate the field values from the previous record.
  17. Type a credit balance of 100 and press Enter to save the record.
  18. Hold down the command key and press D to duplicate the current record.
  19. Click on the First Name field and drag to select its current contents. Type William to replace it.
  20. Repeat the above step to insert 50 in the Credit Balance field. Press Enter to save the record.

You have now created three records for the Jones family. Go on to create as many others as you wish. Because "House" is a text field and not a number field, you can enter addresses such as Myrtle Cottage if you wish.

6.3. Selecting a Subset

To send a letter to all those with a credit balance of #100 or less, carry out the following steps:

  1. Select the Find command from the Select Menu. The Find dialogue box will appear.
  2. Click in the Credit Balance field and the insertion point will begin to flash in this area.
  3. Click on the symbol button and drag to select the symbol for less than or equal to. Then type 100.
  4. Click the Find button to start the search.

The search will take place and you will be returned to Browse mode. You should see the data for Arthur Jones, the first record where the details match the requirements you specified. Brenda Jones was not selected. Click on the lower page of the loose leaf book to move to the next record and you will see that William Jones has also been selected. Depending on what you typed into them, other records may also have been selected.

6.4. Sorting the Subset

In order to sort the selected records by the postcode (or, where there is no postcode, by the town name and street name) the following steps should be carried out:

  1. Select the Sort... command from the Select menu. The relevant dialogue box will appear.
  2. Click PostCode in the Field List and then the Move button. Do the same with the Town and Street entries. The window should then look as shown below:
  3. Click the Sort button.

The sort will be performed and you will be returned to Browse mode. The sort will not make much difference to the Jones because they live at the same address but if you Browse through the other records you created you should see that the order has changed.

6.5. Exporting the Subset

In order to do things which are not possible within FileMaker Pro you need to export the data to other programs:

  1. Select the Import/Export... command from the File Menu and then Export Records from the submenu. Type a filename of TempAddress. Select Tab-separated text as the file type. Press Enter and the field order list will appear.
  2. Click on the arrow beside the Last Update field to deselect it as we do not wish to export this field. The window should now look as shown below:
  3. Click OK and the data will be exported.

6.6. Mail Merging with Word 4 or 5

This section describes the way that the exported data can be used with Microsoft Word versions 4 and 5. Similar procedures apply to most other word processing packages. The following steps should be followed:

  1. Start the Word program by double clicking the icon.
  2. Select the Open... command from the File Menu. Use the dialogue box to move to the correct place so that the filename TempAddress is displayed. Select it and click the Open button. (Alternatively, System 7 users may select the TempAddress icon, drag it over the Word icon and release it.)
  3. Word will open the file. The tab marks will be displayed as shown below if you have the Show /Hide facility switched on. The data will not line up because no tab marks have been set on the ruler but this does not matter. If you wish, you can insert tab marks on the ruler so that the data is clearly aligned, but this will not influence the operation.
  4. With the Insertion point immediately before the first record - the default position until you have moved it - start typing the field names, separated by tabs. Press the return key when you have finished. The names you type do not have to be the same ones used in FileMaker Pro but they do have to correspond to the position of the data associated with them, as shown below:
  5. Now select the Save command from the File Menu.
  6. Create a new file to be used as the mail merge master file, as described in the User Note Word on the Macintosh. It should resemble that shown below:
  7. In Word 4 select the Print Merge... command from the File Menu. In Word 5 click one of the merge buttons. Letters similar to that shown below will be printed:

6.7. Using Tables with Word 4 or 5

Within Word the natural equivalent to a database is a table. These are described in the User Note Word on the Macintosh. To use data exported from FileMaker Pro as a table within Word the following steps should be carried out:

  1. Start the Word program by double clicking the icon.
  2. Export a file from FileMaker Pro as described above and then open it. It should resemble the example shown below:
  3. In Word 4 move the I-beam cursor to the left until it turns into an arrow. Hold the command key down and click the mouse. In Word 5 select the Select All command from the File menu.
  4. In Word 4 select the Insert Table... command from the Document menu. Click OK and the text will be converted to a table with the field values appearing as columns. In Word 5 select the Text to Table command from the Insert menu.

6.8. Using the Data with Excel 3

To use data exported from FileMaker Pro in Excel 3 the following steps should be carried out:

  1. Start the Excel program by double clicking the icon.
  2. Select the Open... command from the File Menu. Use the dialogue box to move to the correct place so that the filename TempAddress is displayed. Select it and click the Open button.
  3. Click the Text File button and check that Excel is set to read a tab-separated file. Click OK and then the Open button. (Alternatively, System 7 users may select the TempAddress icon, drag it over the Excel icon and release it.) . Excel will open the file with the data flowing into columns corresponding to the FileMaker Pro fields.
  4. Select the last two columns, click the chart button and click and drag to create a chart showing credit limit against postcode. This is described in User Note 606 Excel 3 on the Macintosh. The full range of Excel statistics and charting facilities are now available.

7. Further Information

FileMaker Pro is a fairly simple DBMS and there will be applications that it cannot handle. Advice is available within the Computing Service about other systems which are more powerful although less friendly. Please contact Advisory on ext 4831.

There are facilities for handling structured data within other Glasgow University recommended standard products.

If you have any queries about using FileMaker Pro on the Macintosh or databases in general, please contact Advisory in the Computing Service (room 307, ext 4831 or by sending electronic mail to adviser@compserv.gla.ac.uk).