Filemaker Workshop: Linking Tables with Many to Many Relationships

This post is another 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 . 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 exercise the student will:

Create a new table
Create new fields
Create new relationships
Create new layouts
Create a portal
Create a value list that uses two fields, one for input and one for display
Format Field with a dropdown list

Let's begin!


Overview:

When you related the "Schools" and "Students" tables in exercise #1, you established a one-to-many relationship between those two tables.

In more complex databases, a many-to-many relationship may exist. For example, each student can potentially enroll in many seminars and each seminar can potentially have many students enrolled in it.

Whenever you have a many-to-many relationship between two tables, you cannot link those tables together directly. You will need to create an intermediate table between the two to act as a "bridge". This "bridge" table needs to include only the information necessary to record a single event. For example, if a student registers for a seminar, we only need to record the ID of the student and the ID of the seminar that the student has requested.  We may also wish to record additional information about the student's participation in the Seminar like Show or No Show, Grade or Assessment Notes.  But we do not need to include any more information about the student themselves other than their ID.  If we wish to view their enrollment records directly, we can simply add related fields from the Student table to view their full name, their address, their phone, etc.

Likewise, we do not need to include any more information about the seminar they attended other than the Seminar ID.  If we wish to view the instructor name, the course title, or any other details about the course, we simply add related fields from the Seminar table to our layout based on the Registrations table.

Creating an Intermediate "Bridge" Table:

1. Open the Students.fp7 sample file.  Choose File ->New->Database and click on the Tables tab.

2. Type "Registrations" in the "Table Name" field and click "Create".

3. Now select the Fields tab and enter Student# in the Field Name box, select Number for the field type and click Create.

Repeat this process and create another number field named Seminar Number.

4. We will now need to define the relationships between the Student and Registrations tables and the Seminars and Registrations tables. ( I have already created the table Seminars and populated it with sample data for you.)

5. Choose the Relationships tab.

6. Click on the "Student #" field in the Registrations table and drag the field over on top of the Student Number field in the Students table.  Note: Related field names do not have to be identical.

Now double click on the relationship line between the tables.

Check  "Allow creation of records in this table via this relationship" and "Delete related records in this table via this relationship" under the Registrations table in the dialog box and click OK.

This will enable us to create a layout based on the Student table and embed a portal based on the Registrations table that we can use to enroll students in a new seminar or view the seminars they have already taken.

7. Now select the field "Seminar Number" in the Registrations table and drag it over on top of Seminar Number in the Seminars table.

Now double click on the relationship line between the tables and check "Allow creation of records in this table via this relationship" and "Delete related records in this table via this relationship" under the Registrations table in the dialog box and click OK. 

 

This will enable us to create a layout based on the Seminars table and embed a portal based on the Registrations table that we can use to enroll multiple students in a new seminar.

Note: Pay close attention to which table is directly above the checkboxes you are selecting.  We want to be able to create new records in the Registrations table from a layout based on the Seminar table - not the other way around!

Now click OK to close the Define Database dialog box.

Let's create a basic layout to view records in the new Registrations table.  Select View->Layout Mode then Layout->New Layout.  Select the table Registrations and name the layout Registrations and select the Standard Form as the layout type and click Next.

Now click the Move All button then click Next.  Select an appealing template then click Finish. Select View->Browse and you'll see we don't yet have any records.

Now we'll create a layout to use to enroll students in seminars using an instructor's viewpoint of the Seminars table.


8.  Select View->Layout Mode then Layout->New Layout.  In the New Layout dialog box select the Seminars table to "Show Records From", name the layout Seminar Enrollment then select the Standard Form layout type and click Next.

On the next screen click the Move All Button to select and move the Seminar #, Seminar Title and Instructor fields in the layout field list and click Next.  Select an appealing design template then click Finish.

9.  Now click on the portal icon in the design palette on the left hand side of your layout screen.

Your cursor will change into a crosshair.  Hold your mouse button down and draw a rectangle about 4" wide.  In the Portal Setup dialog box select the table Registrations.  Check the "Allow Deletion of Portal Records" checkbox and the "Show Vertical Scroll Bar" checkbox and set the number of rows to display to 5 then click OK.

In the "Add Fields to Portal" dialog box, double click the field Student# from the Registrations table

then select the table Students from the drop down list

and double click Last Name and First Name.  Then click OK.

Now we'll set up a value list to be used to format the Student# field in our portal so whoever is enrolling the student can choose from a list of names and doesn't need to know the student's ID number.

First, though, let's create a calculated field that combines the student's Last Name with their  First Name.

10.  Select File->Define->Database and choose the fields tab.  Select the Student table then name the field LastNameFirstName and select Calculation from the Field Type dropdown list. Then click the Create button.

In the "Specify Calculation" dialog box, double click the field "Last Name" then click the ampersand operator - & - and in the calculation text area enter ", " - quotation comma space quotation - then click the ampersand operator - & - again.  Now double click the field "First Name".  Select "Text" as the type of calculation result then click OK then OK again to close the Define Database dialog box.

Note: The ampersand operator is used to create a concatenation - the adding together of strings of text.  The result of the concatenation above, if the student's name was John Smith, would be Smith, John.  We had to insert the ", " so the two names would not run together like SmithJohn.  When building a concatenation, you must enclose nonfield text within quotes so Filemaker knows you are referring to text and not a field name.

11.  Now let's define our new value list.  Select File->Define->Value Lists and click the New button.  Name the Value List Students and click the "Use Values From Field" radio button.

Select the table Students from the dropdown list and single click on the field Student Number.  Now click the "Also display values from second field" checkbox and click the field LastNameFirstName.  Now click the value "Show values only from second field" and click OK

Note: The "Show values only from second field" lets us display only the Student's Last Name and First Name without displaying the Student Number although when this value list is used to format a field, it will actually input the Student Number into the field when the student's name is selected.

Then click OK and OK to close all dialog boxes.

The reason we selected the Student Number as our first field is that we are going to use this value list to populate the field Student Number in the Registrations portal on our new enrollment layout.  However, by displaying the Student's LastNameFirstName field beside it in the drop down list, our enrollment personnel don't need to look up a student's ID number, just ask their name.

Now we're ready to format the Student# field in the first row of our portal.

12.  Now click on the Student # field in the portal on our layout.  Select Format->Field Control->Setup.  Select Drop Down List as the display type, select Students as the value list you wish to use click the include arrow to show and hide list box and check the Create label checkbox.  Then click OK.

Note:  You will notice that there is a choice called "Pop Up Menu".  This format option looks very similar to a drop down list but only the option Drop Down List provides the ability to "type ahead" to find your choice.  This is very valuable when you are dealing with a very long list.  If you click on a field formatted as a drop down list you can begin typing your choice - in our case the student's last name - and the list will jump down the list based on what letters you are typing.

Click the text label and drag it above the Student# field.  To label any other fields in the portal just double click the field and check the Create label checkbox and click OK.  Then drag them above their respective fields.  Now select View->Browse Mode.  Click in the first field of the first portal row and you will see a list of students.  Click on a name and you will see their Last Name and their First Name show up in the portal without any further data entry.

13.   Now select the layout we designed call "Registrations".  You will see a record in the Registrations table - the "bridge" table - with the Seminar # and the Student# entered.  By creating a layout based on Seminars table and placing a portal based on the relationship to Registrations on the layout, Filemaker automatically knows to enter the appropriate seminar number in the Registrations table when we enroll a student.

If we wish to prepare reports to display all students who have enrolled in particular Seminars, we would base the report on the Registrations table ("bridge" table).  But that's another lesson!

Filemaker Workshop: Defining A Relationship and Converting a Multiple File database to a Single File database

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.