University of Glasgow Computing Service
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.
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.
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.
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.
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.
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.
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.
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.
There are seven types of field:
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.
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:
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:
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.
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.
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.
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:
The field definition process is completed by clicking the Done button.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
Importing data to FileMaker Pro needs three things:
To import data into FileMaker Pro the following steps should be carried out:
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.
To export data from FileMaker Pro the following steps should be carried out:
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.
FileMaker Pro supports the following file formats for import and export of data:
Most DBMS have two separate operations:
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.
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:
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.
The following steps should be carried out:
The following steps should be carried out:
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.
To send a letter to all those with a credit balance of #100 or less, carry out the following steps:
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.
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:
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.
In order to do things which are not possible within FileMaker Pro you need to export the data to other programs:
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:
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:
To use data exported from FileMaker Pro in Excel 3 the following steps should be carried out:
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 firstname.lastname@example.org).