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!