This is the html version of the file http://fhdafiles.fhda.edu/downloads/training/fmrelate.pdf.
Google automatically generates html versions of documents as we crawl the web.
FileMaker Pro
Page 1
1
FACULTY AND STAFF COMPUTER TRAINING @ FOOTHILL-DE ANZA
FileMaker Pro
Relational Databases
Page 2
2
Relational Databases
Explanation
Most smaller data documents are called flat files. That means that all the information is contained in a
single document. The exercises we created and used earlier are examples of flat files. Sometimes,
however, a single flat file is not sufficient to handle all the information needed. FileMaker Pro is what is
known as a relational database. This is the ability to relate databases so that information can be extracted
from several databases into one document.
TIP:
When you create a set of relational databases, it is important that they remain together in
the same folder with the same names as they had when they were created. So before we
even get started, create a new folder out on the desktop. Then every time you create a
new database, be sure to store it in that folder.
Today’s exercise will introduce you to “lookups” and “portals,” in addition to relational databases.
We are going to start with a school setting. We’ll create the following databases:
1. When a student applies for admission, they fill out an application with all their personal
information (DB 1).
2. Classes need to be offered (DB 2).
3. Students need to enroll in the classes (DB 3).
4. Class lists needs to be generated so each student knows what days and times to go to class
(DB 4).
Database 1:
When students apply for school, they fill out an application containing the following data, so create the
first database with these fields:
SSN
Number
First Name
Text
Last Name
Text
Address
Text
City
Text
State
Text
ZIP
Text
Don’t spend any time making this database look lovely because it is only going to contain data. We’re not
actually going to print from here. Save this document as Student Info.
Page 3
3
Database 2:
We need a database which lists all information related to classes offered. This information includes:
Class ID
Text
Teacher
Text
Time
Text
Days
Text
Description
Text
Once again, this information is just going to be used, not printed, so don’t bother formatting it lovely.
Save this document as Class Info.
Database 3:
When students sign up for classes, that information needs to go into a database. In Database 3 the data
entry person only needs to enter each student’s ID and the class ID. The rest of the information will be
entered automatically (if it is set up correctly). For now, just enter in the fields. We’ll set them up in the
next step.
SSN
Number
Class ID
Text
Teacher
Text
Time
Text
Days
Text
Description
Text
You may have noticed that most of these fields are the same as Database 2. In fact, the information for
most of the fields will be copied into these fields from Database 2 soon. Once again, this information is
just going to be used, not printed, so don’t bother formatting it lovely. Save this document as Enrollees.
Database 4:
The fourth and last database will pull together all the information we’ve entered into the previous
databases. It will provide the student with a list of his/her classes, along with times and days, teacher
information, and a description of each class. Create the following fields. We’ll format them shortly.
SSN
Number
First Name
Text
Last Name
Text
Address
Text
City
Text
State
Text
ZIP
Text
Page 4
4
In addition to these fields, we will create a “portal” which will bring in information about each class.
Since this information will be printed out, this is the only database we need to format nicely. We’ll do that
after we set up the portal. Save this document as Class List
Setting Up Relationships
Now comes the fun part. When it comes to entering data into our documents, we will need to put all the
data into the first two documents (Student Info and Class Info) ourselves. After we create relationships
between the databases, much of the information common to each will be automatically entered into the
last two databases.
To start with, we’ll create the relationship between Class Info and Enrollees. Since we will enter the
data into Class Info ourselves, we then want data to go from it into Enrollees. This makes Enrollees the
destination file, so start here.
1. From the File Menu, choose Define Relationships…
2. When the Define Relationships box appears, click on the New button at lower left.
3. Identify the document Class Info as the file you’re establishing a relationship with.
4. In the resulting Edit Relationship dialog box, create a link between Class ID in the left box
and ::Class ID in the right box. What you’re doing here is setting it up so that when the Class
ID field in Enrollees has the same data in it as the Class ID field in Class Info, then a
relationship is established. Then click the OK button.
Page 5
5
5. When the Define Relationships for Enrollees box reappears, click on the Done button.
Creating Lookups
At this point we want to create some “lookups.” Lookups copy data from one database into another. A
lookup can contain only one record from a related file. We are going to set it up so that when both the
Class Info document and the Enrollees document have identical information in their Class ID fields,
then the data from other fields in the Class Info document gets copied into corresponding fields in the
Enrollees document.
1. While in the Enrollees document, open Define Fields… from the File menu.
2. Click on the Teacher field name, then click on the Options… button (located near the bottom
of the window.
3. If you’re not already on the Auto-Enter tab, click on it to get there. Then near the bottom of
the dialog box, click on the Looked-up Value check box. When you do that, another dialog
box will appear:
Page 6
6
4. In the top right area of the dialog box, click and drag to select the file we’ve already created a
relationship with: Class Info. Then in the bottom part of the box, click to select ::Teacher
from that list. What you are doing here is creating a link between the Teacher field in the
Enrollees document with the Teacher field in the Class Info.
5. Click on the OK button to close that dialog box. Click OK on the next box to close it. Now
you’ve returned to the Define Fields dialog box. Continue this process until you’ve created
lookups for all these fields: Teacher, Time, Day and Description. Then click on the Done box
at the bottom of the Define Fields dialog box.
Entering Data
At this point, it would be nice to see if our Lookups work. But we haven’t entered any data yet, so let’s do
that now.
Tip
It is important to add data to any documents that are used as lookup sources before
attempting to have a file lookup data. If there isn’t any data to be looked up, that data
won’t appear in the second file. When data is then added to the source document, it
doesn’t automatically appear. Just be aware of this.
Page 7
7
1. Open up the Student Info document. We’ll create records for six students as shown below:
SSN
First
Last
Address
City
State
ZIP
11111
Mickey
Mouse
55 Disneyland
Anaheim CA
99944
22222
Minnie
Mouse
54 Disneyland
Anaheim CA
99944
33333
Barbie
Mattel
32 Toyland
Orange
CA
94949
44444
GI Joe
Mattel
33 Toyland
Orange
CA
94949
55555
Donald
Duck
67 Disneyworld
Miami
FL
00033
66666
Daisy
Duck
68 Disneyworld
Miami
FL
00033
2. Open up the Class Info document. We’ll create records for three classes as shown below:
Class ID
Teacher
Time
Days
Description
ART 1
Goofy the Dog 9-10 am
MWF
Learn to draw
BIO 2
Bill Nye
10-11 am
TTh
Learn about life
CHEM 3
Julia Childs
2-5 pm
MTF
Learn to cook
(Remember that FileMaker saves automatically so you don’t have to.)
3. Open up the Enrollees document. Create the following records:
SSN
Class ID
SSN
Class ID
11111
ART 1
11111
BIO 2
22222
ART 1
22222
CHEM 3
33333
BIO 2
33333
CHEM 3
44444
BIO 2
44444
CHEM 3
55555
CHEM 3
55555
Art 1
66666
Art 1
66666
BIO 2
As you enter in this information, you should see the rest of the fields automatically filling in
information. If not, then you may have a problem with your Lookups. These three documents
are essentially complete.
Page 8
8
Class List Document
Now we have to turn our attention to our Class Lists document. This one we have to relate back to our
previous documents so that information is automatically inserted.
1. Open the Class Lists document if it isn’t already opened. Choose Define Relationships…
from the File menu. Click on the New button at the bottom of the box.
2. Choose the document Student Info to establish a relationship. When the next dialog box
appears, link the SSN from Class Lists to the ::SSN field from Student Info. Click in the box
labeled “Allow creation of related records” before clicking OK and Done.
3. Now you have to create the Lookups from one file to the next. Open up Define Fields… from
the File menu. Click once on the First Name field and click on the Options button…. Proceed
to create the lookup relationship as we did in the previous exercise. Create lookups for these
fields: First Name, Last Name, Address, City, State, ZIP. Then close the Define Fields… dialog
box by clicking on the Done button at the bottom of the window.
Portals
A portal allows you to show information on the current database that is actually stored in a different
database. Portals are different from Lookups in at least two important features:
(a) Information from a portal actually stays in the original database. That means that if you
change any information in one place, it will be reflected in all other databases. A lookup,
on the other hand, just copies information from one database into another. If you change
data in the new database, it won’t be reflected in the old one.
(b) Portals can show any number of records from a related file. A lookup can only show one
record.
4. Before we can put in the portal, we need to define a relationship between this document,
Class List and the Enrollees document. The field that will create the relationship is the SSN
field. Create this relationship as you were shown previously.
5. Switch now to the Layout View from the View menu. Drag down on the body line to make
the body a little longer. In the empty white space we will put a portal.
6. On the toolbar, click on the Portal Tool,
7. Move your cursor to the white space in the layout and drag a box, taking up most
of the width of the page. The Portal Setup box will appear. You need to identify the
document connected to this portal, which is Enrollees. Then set the number of rows
to show to 3 and click OK.
8. Now add the fields from the Enrollees document. Just as adding any fields, click on the Field
tool and drag over the field to the Portal box. Take care that the entire field is inside the Portal
box or not all the fields will show up. In this way, insert the following fields:
::Class ID, ::Teacher, ::Time, ::Days, ::Description
Page 9
9
9. Spend some time now making your layout look good. Arrange the upper fields in a nice
order. This is what is going to get printed out, so it shouldn’t look like a list. This is what my
layout looks like:
10. Switch now to the Browse mode. In the first record, type 11111 for Mickie Mouse’s
information. When you hit the Tab key, the entire rest of the database should automatically
fill in. Go ahead and create records for the rest of your students.
COOL!, HUH?
11. Look carefully at your data. Does all the information fit into the cells, or is something
missing. If you need to, return to Layout mode and resize your fields.
12. Switch to Preview mode and see how your data will look printed out. If necessary, return to
Layout mode and adjust again. Keep at it until you are satisfied with your work.
Congratulations!
You have completed a Relational Database
that can save you lots of time and energy!