This post is one in a series of posts that represent classroom exercises that I used to conduct as a Filemaker Pro instructor. These exercises are designed for new Filemaker Pro users or those unfamiliar with structuring a database with related tables. This lesson can be used with the sample file
Students.fp7 and
Schools.fp7.. It can be downloaded by right clicking on the link and selecting "Save Link As" or "Save Target As" depending on which browser you are using. My instructions and screenshots are based on the Windows Version (8.5) of Filemaker Pro. Mac users can generally follow the same instructions with only a few minor menu differences.
In this lesson the student will:
Create a new table
Create new fields
Create a relationship
Import records
Create a value list
Recreate a layout
So let's begin!
Open the sample files
Students.fp7 and
Schools.fp7.
Overview:
If we look at the Students file you will notice there is one record per student. Each student has an identification number that is unique to that student stored in the
Student Number field. We also have other fields to record the student's address, city, state, zip, phone, music instrument and music style. All of these fields contain information unique to that specific student. Each student record also has a field for
School Number that indicates the school they attend. If this file did not have a related file to provide all the information we need about the school the student attends, each student record would have to have a field for School address, School administrator, etc. However, all of that information is stored in the file
Schools.fp7 which has one record for each school including a field named
School# to identify that specific school.
By relating these two files on the fields "School Number" and "School#", called the "match" field or the "key" field, we can add fields from the Schools file to any layouts in the Student file. Then as we create a new student record, we simply input the number for the school the student attends and all related information about the school will display on the Student's layout without inputting any duplicate data.
In versions of Filemaker Pro before 7.0, each database table was created as an individual file like our two sample files. But, Filemaker 7 and newer provides the opportunity to create separate
tables to store different collections of data within
a single file.
Placing multiple tables within a single file allows all the tables to share common resources such as
value lists and
scripts. There's also no need to duplicate
security settings in each of the many files of a large solution. A FileMaker Pro 7 or newer file lets you set those privileges once, and they apply across an entire large, complex solution with numerous tables.
For example, using the old method, if we related the separate files
Student and
Schools on the key field
School # then wanted to create a menu layout in the Schools file to produce a report listing all students attending a particular school, we would first have to design a report and write a script in the Student file to subsummarize all student records for each school. Then we would have to write a script in the Schools file that called that external script in the Students file.
However, if we use a new version of Filemaker (7 or newer), we could create a single file (Students.fp7) then within the file Students we would create a second table (Schools). If we write a script to produce a subsummarized list of students per school that single script could be called from a menu layout that could be accessed from anywhere within the file.
So how do we convert a collection of Filemaker Pro files from an earlier version of Filemaker Pro to the new single file format? Let's step through the process with our sample files.
Note: If your Filemaker Pro files are still in an earlier format, you will need to begin by opening each file with the new version of Filemaker Pro and saving each file in the new format. I have already done that for you with the sample files.
Creating a Relationship in Filemaker Pro and importing data from a converted Filemaker Pro 6 (or earlier) file.
This procedure changed between Filemaker Pro 7 and later versions of Filemaker Pro. I will explain each method separately.
Filemaker Pro 7 required users to manually create a new internal table (New users of later versions should review this procedure just to become familiar with how to create a new table):
1. Open the file
Student.fp7. Select
File ->Define ->Database and select the
Tables tab. Enter
Schools for the table name and click
Create.
Now select the
Fields Tab. If not already selected, select the
Schools table. Create a
number field named
School # and text fields named
School Name,
Dept Coordinator,
Dept Phone,
City and
State.
School# will be our "
matching" or
key field. This field
MUST be unique.
Note: If I was creating the School table for the first time and not planning to import existing data, I would set the field to AutoEnter a computer generated serial number. However, we have existing records with School Numbers already attached so we will not set that option.
2. To populate our new internal table with the data in the old
Schools.fp7 file, we must import it. With Filemaker Pro 7 you will first need to create a layout based on the table schools that contains all fields from the table Schools.
From the application menu, select
View->Layout Mode then select
Layout->New Layout/Reports. In the
New Layout/Reports dialog box select the table
Schools from the drop down list next to "
Show Records From:". Name the Layout "
Schools". Select the
Standard layout form then click
Next.
Then click the "
Move All" button in the next dialog box and click
Next. Choose any theme you prefer and click
Finish.
Now
select
File->Import Records->File and select the file
Schools.fp7 and click
Open. Make sure field names are arranged opposite each other (you may click and drag fields in the right hand column up or down if they are not - notice the two sided vertical arrow) and be sure they have a black arrow next to each field on the left pointing to its corresponding field on the right. If there is just a dash, click it once with a mouse to change it to an arrow. Then click
Import.
IMPORTANT NOTE: Whenever you perform an import in Filemaker Pro, always select a layout based on the table you wish to import the data into. This will automatically set the import target table. If you are performing an import to update existing records, be sure to select Records->Show All Records first.
Imports only update records in the Current Found Set.
If you are using Filemaker Pro 8 or newer, the creation of the Schools table is done automatically during the Import process. Select
File->Import Records->File and select the file
Schools.fp7 and click
Open. In the upper right hand corner of the
Import dialogue box click the drop down list and select
New Table ("Schools"). then click
Import. A new table called "Schools" and a layout with all fields in the table Schools will be created and populated with the data that was in the file Schools.fp7.
Regardless of which version of Filemaker Pro you are using, you now need to establish your relationships between the tables in your solution.
3. Select
File->Define ->Database from the application menu. Click on the
Relationships tab. Click on the
Schools table field
School #. Holding the mouse button down, drag the field on top of the
School Number field in the
Students table and click
OK.
Note: Fields do not need to be named exactly the same to be used as a "match" field.
Now click on the little box containing two lines in the center of the relationship connecting line. You will see that the fields are now specified as equal to each other in the
Edit Relationship dialogue box. Notice the
Add button. Beginning with Filemaker Pro 7 or newer you have the ability to specify more than one
match field.
For example, at the University we identify a course by a unique CRN number. However, CRN numbers are reused in subsequent terms. Therefore, if you have a table of course information that includes information from multiple terms you cannot use a CRN as a unique record identifier. In older versions of Filemaker Pro we had to create a field that autofilled with a calculation that combined the CRN and Term fields. Now we can establish a relationship using the field CRN and a second relationship using the field Term.
You will also notice that the relationship operator is a dropdown list. The default is "=". However, with Filemaker Pro 7 or newer you can also choose other operators. Why would we wish to? Well, at the University we designate terms by year and term i.e. 201001 (Fall Term 2010). If we have a Student table with one record per student and a Registrations table with one record for each course the student has enrolled in ( a
one-to-many relationship) we can create a layout with a
portal that views all courses the student has taken. But a student record may include years worth of enrollment information.
What if we only want to view courses taken in the last three years but retain the ability to view the entire student's history if we should need to? If we first create a field in the Student table called Target Term then create a dual relationship between the Student table and the Registrations table on the fields "Target Term" and "Term" with the operator set to ">" (greater than), we could use the "Target Term" field to "filter" the records we wish to view in the Registrations table by setting a value in the "Target Term" field in the student table.
We'll explore this technique further in another class.
You also have the option to "
Allow creation of records in this table via this relationship", "
Delete related records in this table when a record is deleted in the other table" and "
Sort records". If we wish to create a layout based on the
Schools table with a portal that views the
Students enrolled in that school that we can use to enroll new students, we need to check the "
Allow creation of records in this table via this relationship" option directly under the Student table. If a School is removed from the tracking system and you wish all student records connected to that school removed from the database, you would check the "
Delete related records in this table when a record is deleted in the other table" option
. This option should be considered carefully, though, based on how you wish to manage the records in your database. If you want to retain all Student information regardless of which school they attend and the Student record is of primary importance while the School information is only secondary, you would not check the "
Delete related records in this table when a record is deleted in the other table" option. In other words, it is very important to understand the "viewpoint" of your records system.
The import process does not include resources like value lists or scripts. We must recreate any value lists we may need that originally existed in Schools.fp7.
4. Using our
Students.fp7 file select
File->Define Value Lists. Click on
New and enter
School Names for the value list name and click the radio button for
Use Values From Field. Select
Schools from the Tables drop down list and click on the field
School# then click "
Also display values from second field" checkbox and click on the
School Name field. Then check the "
Show values only from second field" checkbox and click
OK.
We will use this value list to format the Student Number field to create a drop down list for our records personnel to choose from to assign a school to a new student. So our records personnel don't have to memorize a list of numbers we have configured our dropdown list to display the second field only - the School Name. When the choice of school is made, the School Number will be entered into the field even though a name was selected from the drop down list. Recording the school number is really the only information we need in the student table to relate a student to a school. This also eliminates the problem of schools with the same name. Each school is assigned its own unique school number even if it has a name identical to another school. We can add a related field from Schools on a Student layout to display the School name.
Note: If we had a value list in the Students.fp7 file that was based on the old external file Schools, we need to edit that value list so that it now refers to the table Schools instead.
Because the file Students.fp7 now contains the tables Students and Schools, the value list can be used on any layout referring to the Student Number field from either table.
Recreating layouts
All tables and value lists should be imported/recreated before attempting to recreate layouts so fields formatted to use value lists will retain their format and all fields will map properly to their corresponding fields in the new tables.
In the new file, create a new layout based on the table that is appropriate for the layout you are attempting to recreate. Select
View->Layout Mode then
Layout->New Layout. Select the appropriate table from the list of tables, name the layout
EXACTLY as it was named in your old file and choose
Blank Layout and click
Finish.
Open the old file and select the layout you wish to recreate. Select
View->Layout Mode then
Edit->Select All. Then
copy (Control-C) and
paste (Control V) all objects into the new blank layout in the new file. If your fields are named exactly as they were in the old file, the fields will map themselves to the new fields in the new table. If not, the field will appear blank without the field name showing in Layout Mode. If you see a blank field, double click the field and select the table and field to display. All fields formatted for drop down lists or check boxes will remap to the appropriate value list if they were created first as well.
Note: If you have background objects that are "Locked" on your old layout, you will need to click on the locked object first (in Layout Mode) and select Arrange->Unlock before you perform the Edit->Select All step.
Scripts can be imported using Scriptmaker. However, all tables, value lists, and layouts should be recreated (in that order) before you attempt to import scripts so script steps referring to specific layouts, etc. will not "break". All imported scripts need to be examined after import for Missing File, Missing Field or Missing Layout references and remapped as needed.
