News     Documentation    Help

Training Labs Contacts

Connecting About    Purchases

Policies TTU Home   ITS Home

Contents

Introduction

FileMaker Pro is a database program that can help you to organize information such as accounts, inventories, or personnel data. It is a flexible program that can work with one file or many relational files.

Basic Database Terms

Planning the Database

In creating a new database, the most important step is planning and considering what you want to be able to do. Take your time with this step. Sketch out your ideas and share them with others. The answers to these questions will help determine what you can and cannot accomplish with your database. You will need to consider:

What is the smallest unit you want information on?   For example, do you want to have information on each class or on each individual in the class? Each record will hold information on this unit.

What pieces of information do you need to keep?   Be practical and think about what you have time to deal with. Also consider how you will keep track of changes in the information. How will you know? These pieces of information will be your fields.

In what form should you store the information (numbers, text, dates)?   You should store numbers that are not used in computations (+,-,*,/) as text fields. For example, you wouldn't add telephone numbers or zip codes, so those should be stored as text.

Which specific fields do you need?   For example, do you want just a Name field or do you want a First Name and Last Name field so that sorting can be done alphabetically by last name. These decisions can affect the kinds of reports you will be able to produce.

Can the information be stored in one database (flat) or are there several databases needed that will be linked together (relational)?   For example, if you are doing requisitions, you might want a second file with the names and address of vendors to be able to link into the file containing each requisition.

In this exercise, we will create a database with one file to keep track of a departmental inventory.

Opening FileMaker Pro

To open FileMaker Pro: Start —› Programs —› FileMaker Pro 4.1 —› FileMaker Pro

Note that in the FileMaker Pro 4.1 subdirectory in your office you may select FileMaker Pro Tutorial. This provides some basic information on FileMaker Pro that you can go through at your own pace.

Creating a Database from a Template

In the New Database dialog box:

• Click on Create a new file using a template
• From the pulldown menu, select:  Education.
• Double-click on faculty staff to open a template to create a staff database.

Note: For more information on each template, click on the Template Info... button.

A dialog box will open asking you to create a copy of the file. 

• Select the temp drive for this class; otherwise, select a permanent folder location. 
• For this exercise, accept the name suggested. 
• Click Save.

You will now see a database set up to keep track of employee information. 

• To create a new record to enter data, click on the button New Record. 
• Type in information on yourself and tab to move from field to field.

Note: You can make changes to the design and layout of this database, since this is a copy based on the original template:  Select Mode —› Layout

To close this file, from the menu select: File —› Close.

Creating a New Database File

In creating a database of items in a departmental inventory, consider some of the fields that will be needed, such as the kind of item, a description of it, the cost and date received, etc. List these ahead of time.

To create a new database:

• From the menu select, File  —› New
• Click on  Create a new empty file
• Click OK.

In the Create New File dialog box:

• Select the temp drive for this exercise; otherwise, select a permanent location.
• Type the filename:  Dept Inventory
• Click Save.

In the Define Fields dialog box:

• Under Field Name, type:  Item Category
• Select the field type:  Text
• Click the Create button.

• Type the next field name shown in the list above:  Item Description 
• Select the field type:  Text
• Click the Create button.
• Repeat for each of the next seven fields above, from Item Location to Assigned To
• Note that the field type for Item Cost is Number
• Note that the field type for Date Received is Date

To enter a calculation field:

To compute the age of each item in the inventory, use the formula:  (Today - Date Received)/365

• Type the field name Item Age 
• Select the field type:  Calculation 
• Click the Create button.

• The Specify Calculation dialog box will appear. 

• To compute the item age, click in the formula box and type a left parenthesis ( 
• In the list of functions to the right, scroll down and double-click on the function Today 
• Click on the subtraction symbol button.  
• In the list of field names to the left side, double-click on the field Date Received. 
• Click in the formula box and type a right parenthesis ) 
• Click on the division symbol button.
• Type 365.
• Note that the calculation result is correctly listed as Number in the pulldown menu. 
• Click OK.

• Enter the last field name:  Comments 
• Select field type:  Text 
• Click Create. 
• Click Done.

Field Types Available

Modes

FileMaker Pro includes four modes for working in your database:

• Browse - for data entry and general viewing of your data.
• Layout - for designing how your information will appear on the screen and in reports.
• Find - to locate a particular record or set of records.
• Preview - to see on your screen how information will look when printed.

Working with Layouts

You will see a basic layout of your fields created by FileMaker Pro. Note in the menu under Mode, that you are in browse mode, ready to enter information. However, before beginning to enter data, we will make some changes to this layout.

Layouts allow you to specify how you want your data to appear. You can easily modify and create layouts to make data entry easier and to create reports. Each database can have many layouts. A layout does not affect how the data is stored, just how it appears on the screen or when printing.

FileMaker Pro creates the first layout, a Standard style layout, which includes all of the fields. This layout is automatically given the name, Layout #1 (see below).

To switch from Browse mode to Layout mode, select Mode —› Layout

To set editing guides, select:

• Show —› Text Ruler
• Show —› Graphic Rulers
• Show —› Ruler Lines
• Arrange —› Autogrid  (Click to deselect — no check mark)

Three parts are shown:  Header, Body, Footer.  Note that each part can be expanded, reduced, or even deleted.

To adjust the view:

Click on Part Label Control button at the bottom left corner to move the part labels off to the side.

Click on the increase size Zoom Control to change your view to 150%. (In the PC Lab leave the zoom at 100% for better viewing).

Delete the footer since it will not be needed in this layout:

• Click on the part label Footer.
• Press the Delete key on the keyboard.

Increase the size of the Header region:

Click on the line separating the header from the body. Your cursor will change to a double arrow. Drag this border down to the 1 inch mark on the graphic ruler.

Increase the size of the Body region:

• Scroll down and click on the bottom line of the Body section.
• Your cursor will change to a double arrow (see above).
• Drag this border down to about the 6 inch mark on the graphic ruler.

Increase the readability of the layout by formatting and arranging the fields in the layout:

• From the menu, select Edit —› Select All   (Note that the pointer tool button is depressed.)
• On the text ruler, select 12 point for the text size.
• Click in the center of the selected group and drag to move the group so that the tags are about
  1 inch from the left side and the top is at 1.25 inches.   Note the rulers are highlighted as you move to help you position the group.
• Click elsewhere on the layout to deselect the fields and tags.
• Click on the label "Comments" and drag it to the right side.
• Click on the field named Comments and drag it under the tag.
• Click on the bottom right corner of the field to drag and resize it to fit under the Comments tag.
• Click on the label Item Age and hold the shift key down while clicking on the field Item Age.
• Drag these down to about 4.5 inches.
• Click elsewhere to deselect.

To set the alignment of items in the layout:

• Select the list of field names by clicking on each while holding the shift key down.
• From the menu select:  Arrange —›  Set Alignment.
• Under Top to Bottom, select Distribute Space
• Under Left to Right, select  Align left edges
• Click OK. Note that the fields are now spread out for better viewing.
• Click elsewhere to deselect the group.

• Select the list of labels by clicking on each while holding the shift key down.
• From the menu select:  Arrange —› Set Alignment.
• Under Top to Bottom, select  Distribute Space
• Under Left to Right, select  Align right edges
• Click OK.

To set the items off using a box, color, and borders:

Select the drawing tool that is a rounded square. Note that your curser becomes a plus. +

Place the plus at the top left corner of the items and drag diagonally to draw a box. Release at the bottom right corner just beyond the last field. The box appears as a layer on top of the layer containing the fields and field labels. With the box still selected, from the menu select:     Arrange —› Send to Back

With the box still selected, click on the:  Select a color:  light yellow. Click elsewhere to deselect.

Fill Color button.

To return the fields to a white color and outline with a border:

Click on the fields while holding the shift key down to select them. Click on the Fill Color button and select white. With the fields still selected, select Format —› Field Borders Check Top, Left, Bottom, Right and click OK. Click elsewhere to deselect the fields and see the effect.

To add a text heading:

Click on the text button (A) in the toolbar and note that your curser changes to an I-beam. Click in the header region and a text box will appear. Select the font Arial and a font size 18. Click on the Bold button. Click on Center alignment

Type: Departmental Inventory. Click outside the text box to deselect text entry.

• Click on the text box and drag to move it to the upper left corner of the page.
• Click on the lower right corner and drag to expand the text box to the right margin line.
• Note how the heading is now centered across the page.

To add the current date under the heading:

• Click in the heading region and set the font size to 12.
• From the menu, select Edit —› Paste Special —› Date Symbol (Looks like two slashes //).
• Click outside the text box to deselect text entry mode.
• Click and drag to position the current date.

To format the date:

Click on the date symbol (//) to select it. From the menu, select Format —› Date Select Format as: and from the pulldown menu, select the style you prefer. Click OK.

To add text in the body of the layout:

Click on the text button (A). Move to the end of Item Age and click. Select a font size of 12 and a Left alignment. Type: Years Click outside the text box to deselect the text button Click on the word Years and drag to adjust the position as needed.

To format the number of decimals displayed:

Click on the Item Age field. From the menu, select Format —› Number Select Format as decimal number Check Fixed number of decimal digits:  Enter 1 Click OK.

To set currency formatting:

• Click on the field Item Cost.
• From the menu, select:  Format —› Number

Select: Format as decimal number Check: Fixed number of decimal digits:  Enter 2 Check Use notation: Select Currency (leading) Currency symbol: $ Check Use thousands separator. Click OK.

To format a field with radio button:

• Select the field Account Number
• From the menu, select Format —› Field Format

Select Radio buttons from the pulldown menu. Select Define Value Lists from the pulldown menu.

Type the name Account Numbers in Value List Name. Click Create. Select Use custom values: Type three account numbers in the box. Be sure to press enter after you type each one. Click Save. Click Done. Click OK.

To format a field with a pop-up list:

• Select the field Item Category
• From the menu, select Format —› Field Format
• Select Pop-up List from the pulldown menu.
• Select Define Value Lists from the pulldown menu.

Type the name Item Category in Value List Name. Click Create. Select Use custom values: Type the categories shown here. —› Click Save. Click Done. Click OK.

To add a graphic image to the layout:

• From the menu, select File —› Import Picture
• Browse to ClassFiles on Athena —› ITS Folder —› FileMaker Pro
• Select the file: Folders.WMF
  Note that the graphic is rather large.

With the graphic still selected, from the menu select Format —› Graphic. Check Maintain original proportions. Click OK.

• Click on the lower right corner of the the graphic and drag to decrease the size.
• Move it to the right of Departmental Inventory in the header.

View of the final layout:

Working in Browse Mode

Use Browse mode to enter new information and correct existing data.
From the menu, select Mode —› Browse

To enter a new record:

• Select Mode —› New Record or use the keyboard shortcut, Ctrl + N
• Type in the data shown below.
• Use the tab key to move from field to field.

To import some sample data from an existing file:

Move through the records by clicking on the book icon or using the slide along the edge of the book icon.

To make corrections to records by editing any field.

• To duplicate a record: Mode —› Duplicate Record
• To delete a record(s): Mode —› Delete Record 
• Or to delete all records:  Mode —› Delete All (Use carefully).
• To omit record(s) from the current set, but retain them in the database: 
  • Select —› Omit  (Omits the current record)
  • Select —› Omit Multiple  (Omits the number of records you specify.)

Using Find Mode

Use Find Mode to locate a particular record or sets of records that match your criteria.

• Select Mode —› Find
• Select Videos from the Item Category menu.
• Press Enter or click the Find button. 

    Note that you have found the videos.

To find all records except videos:

From the menu, Select —› Modify Last Find Check Omit. Press Enter or click the Find button. Note that you have found all items except for the videos.

To find records with items over 1 year old:

• Select Mode —› Find
• Click into the field Item Age.
• Click on the symbols pulldown menu and select the greater than symbol:   > greater than
• Type 1 (for 1 year).
• Press Enter or click the Find button.

To find records that fall within a range of values, such as items purchased between 1995 and 1998:

• Select Mode —› Find
• Click into the field Date Received.
• Type 1/1/95...12/31/98   (Use three periods to denote a range of values.)
• Press Enter or click the Find button.

To find records with missing or blank data in a field:

• Select Mode —› Find
• Click into the field TTU Tag.
• Type =     (Use = with nothing following it to indicate a match to a blank field.)
• Press Enter or click the Find button.

To find records with multiple criteria, such as all Computers paid out of account 5-12345 (And):

• Select Mode —› Find
• Click into the field Item Category and select Computers.
• Click into the field Account Number and select 5-12345.
• Press Enter or click the Find button.   (Note we are looking for this and that.)

To find records with multiple criteria, such as either Computers or Equipment 
paid out of account 5-12345 (Or):

• Select Mode —› Find
• Click into the field Item Category and select Computers.
• Click into the field Account Number and select 5-12345.
  
• Select Mode —› New Request
  
• Click into the field Item Category and select Equipment.
• Click into the field Account Number and select 5-12345.
• Press Enter or click the Find button.
  (Note we are looking for this and that or the other and that.)

From the menu, Select —› Find All to return to viewing your full set of records.

Sorting Records

To sort records: Select Mode —› Sort Click on Account Number in the list to the left. Click on Move. Click on Item Category. Click on Move. Note that the sort will be in Ascending order. Click Sort. Note:  Click on Done to cancel without sorting.

Creating a Report

To create a report that lists records by account number and sums the costs.

• Select Mode —› Layout
• Select Mode —› New Layout
• Type the layout name: List by Account
• Select Extended columnar as the Type.
• Click OK.

Select Item Description. Click on the >>Move>> button to move it into the Field Order list. Select Item Cost. Click on the Move button to move it to the Field Order list. Click OK.

To create the section of the report that will summarize expenses by account number:

• To help see the names of each part, click on the Part Label Control to view the full names of each section. Click again to move them out of the way.
• Click on the Field button located to the left side of the view.   
• Drag it into the Account Number Subsummary that is above the Body section of the report.
• Select the Account Number field.
• Deselect Create field label.
• Click OK.
• Click on the Underline format button.

To create a new field to sum the cost of the items:

Select File —› Define —› Fields Type the Field Name: Total Cost Select the Type:  Summary
Select Total of Select Item Cost from the field list. Click OK. Click Done.

To add the new summary field (Total Cost) to the appropriate section of the layout:

Note that new fields created are added to the body of the layout you are currently in.

• Increase the size of the trailing Sub-summary by Account Number by clicking on the line and dragging down.
• Select the new Total Cost field and label and drag them into the trailing Account Number Sub-summary.
• Decrease the size of the Body section by clicking on the boundary line and dragging it up.

Click on the Line tool.   Draw a horizontal line above the Total Cost label and field by holding down the shift key while drawing your line. This will keep the line level.  (Shift, then click on the spot you want to begin your line, drag across, release the mouse button, then release the shift key.)

• Click on the Text tool (A).
• Click into the Heading section.
• Select font size of 18 and align center.
• Type: Departmental Inventory by Account Number
• Move and size the title of your report appropriately.

To view the report:

• Select Mode —› Browse
• Select Mode —› Sort
• Select Item Category and click the Clear button. 
  This leaves Account Number as the field on which to sort.
• Click Sort.
• Select Mode —› Preview.

Note that you can not see the resulting report unless you are in Preview mode.

Printing and Exiting

To print, from the menu select:   File —› Print

To exit the program, select File —› Exit or use the close button.

Note that the file is saved automatically at exit and periodically throughout your work.

Getting Help

One of the easiest ways to get help with FileMaker Pro is the online help included with it. 
Just click on Help —› FileMaker Help Topics

Should you have a question that the online help does not address, please contact the Microcomputer Support Office at 372-6315 (email: MicroSupport@tntech.edu) or your College Contact.

Handouts on using FileMaker Pro and other programs are available on the web from the TTU home page under Computing —› Documentation (www.tntech.edu/its/pubs/).

Documentation | ITS Home | TTU Home

Maintained by:
Academic Computing Support
Information Technology Services
Tennessee Technological University
September 04, 2002