This lesson requires TestClassLookup.fp7 and TestTerms.fp7.

With Filemaker Pro 7 you can create a relationship based on more than one field. Creating layouts with portals based on these types of relationships allows you to provide fast ad hoc viewing of subsets without requiring the user to perform a Find in the related table to view the records they are interested in.

1. Open the file "Testclasslookup.fp7".

2. Select the layout "Advising" from the layout drop down list. Select 200102 from the drop-down list in the "Term" field.

3. Select "EDLD" from the drop-down list in the Subject Code field. Notice how the contents changed in the first Portal.

4. Select File -> Define -> Database and the Relationships tab. Notice that the first relationship to the table Test Terms has three match fields "Term", "Subject Code" and "Student ID". The value lists for the "Term" field and the "Subject Code" field are defined as values from the fields Term and Subject Code in the table "Test Terms". This confines queries to only records within the related table "Test Terms".

These value lists were created by selecting View -> Layout Mode then selecting the field to be formatted ("Term") and choosing Format-> Field Format. I clicked on the pop-up list radio button and selected Define Value List. Then I clicked the "New" button and the "Use Values From Field" radio button. Then I clicked on the "Specify" button and selected the table "Test Terms" from the available tables and "Term" from the list of field names. I repeated this procedure for the field "Subject Code", selecting the field "Subject Code" from the list of field names.

5. What if we wanted to view all EDLD classes the student has taken prior to and including the current term? Select File->Define->Database and the Relationship tab. Doubleclick on the box containing the two short lines in the middle of the relationship connecting line. Now click on the Term relationship and change the relations operator from "=" to "greater than or equal to" and click OK. Now look at your portal contents. The portal shows courses that were taken prior to or during the term specified in the Term field of the Testclasslookup table. This may seem confusing but remember the conditional statement is read left to right and the table Testclasslookup is in the left hand window. By choosing the "greater than or equal to" operator, we are saying we want to see all records in which the specified term in Testclasslookup is greater than or equal to the term values in the table Test_Terms. In other words, all related records with terms less than or equal to the specified term field in the parent table Testclasslookup.

If the table Test_terms had been on the left, we could have used the less than or equal to operator because we would be specifying that we wanted to see all records from Test_terms that are less than or equal to the term value specified in the record from Testclasslookup.

6. Now let's create a portal to view classes in which the student received a particular grade. Choose File->Define->Database and the Field tab. Select the "Testclasslookup" table and create a text field named "Grade". Now click on the Relationship tab and click on the Add Table button. Select the table Test_terms and change the name of the table occurrence to Test_terms by Grade. Now click on the field Student ID in the table occurrence "Test_terms by Grade" and drag it over on top of the field Student ID in the table occurence "Testclasslookup". Click on the field Grade in the table occurrence "Test_terms by Grade" and drag it over on top of the field Grade in the table occurence "Testclasslookup". Click "OK" and then "OK".

7. Select the "Advising" layout then select View -> Layout Mode. Click on the Field Tool and, holding the mouse button down, drag the field object under the first portal and choose the "Grade" field. Now, click on the Portal tool and draw a portal below the grade field and approximately the same size as the first portal. Select the table occurrence "Test_Terms by Grade" from the list of tables in the "Show Related Records From" drop down list. Check the Sort checkbox and specify the Term field. Type "5" in the "Number of rows" box then click OK. In the "Add Fields to Portal" dialogue box, doubleclick the Class Description, Subject Code, and Grade fields. Select View-> Browse Mode. Now Enter "B" in the "Grade" field above the portal. Notice that only courses in which the student received a "B" appear in the portal. Also notice that both Counseling and Ed Leadership courses appeared because we did not include the Subject Code in our relationship formula.

 

COE Techweb

COE Home

UO Home