This lesson requires Students.fp7.

If a field in a Master table with one record per entity is set equal to the value of the same field in a related table with many records, Filemaker will record the value that it encounters in the first related record. This behavior can be quite handy at times. You can manipulate the value returned by defining a specific sort order when you define the relationship.

1. Select File -> Open and choose Students.fp7 from the available sample files. Navigate to Record 1. Scroll the portal and you will see a number of TOEFL scores entered for this student. Look at the field below the portal "Original TOEFL Score". Compare the value in the field with the oldest TOEFL score in the portal and you will see that they are equal. This occurs because the portal is sorted in Ascending order by Term with the oldest Term first. The calculated field "Original TOEFL Score" defines its value as equal to "TOEFL" in the related file based on this ascending relationship to TOEFL Scores. The first value it encounters is "512" from the oldest term 200101.

2. Now let's define a field to ascertain the most current TOEFL score available. Select File -> Define->Database and the Relationship tab and click on the "New table" icon. Select TOEFL Scores from the list of available tables and name the table occurrence (TOC) "TOEFL Scores Descending by Term". Now click on the Field "Student Number" in the TOC TOEFL Scores Descending by Term and drag it over on top of the "Student Number" field in the TOC Students. Then, click the "Edit" (pencil) button. In the lower right hand corner check the Sort Records checkbox then doubleclick the term field in the left hand window.

3. Now, single click the field "Term" in the right hand window and select the Descending Order radio button. Click "OK" and "OK". Now click the Fields tab and select the table Students. Create a calculated field named "Current TOEFL Score". In the Options Window, Select the related table"TOEFL Scores Descending by Term" and the field "TOEFL". Set the data type to number and click "OK".

4. Select View -> Layout Mode. Click on the Field tool and drag a field object below the "Original TOEFL Score" field. Select the field "Current TOEFL score" from the field list and be sure to check the Field Name box and click "OK". Now, Select View -> Browse Mode. The value should be 560, the value from the most recent term, Summer Session.

Note: You are not limited to values in Ascending or Descending order. You can specify a custom sort by creating a value list with the values in the sort order that you desire.

 

COE Techweb

COE Home

UO Home