What is the relationship that links students to Grades?
Assuming you have:
Students::StudentID = Grades::StudentID
Also assuming you have either a number field to record the year the grade was awarded or the date defined in the Grades table, you can do one of two things:
1) Double click the line in the relationships graph linking these two tables and specify a descending sort order for grades by this date or year field. Now place the Grades::Grade field on a students layout and you'll always see the most recent grade. You can also add your year or date field to show the date/year the grade was awarded.
2) Place a one row portal to Grades on your Students layout. Specify the same sort order in portal setup so that the most recent grade will be displayed in the portal's one and only portal row.
For completeness :-]
3) If the Students to Grades relationship is not sorted, and you've entered the grades so that chronological order = creation order (likely), then the Last ( student_Grades::Grade ) function (as a calculation field in Students) will return the "last" grade entered for that student.
The Last() function has two curious things about it. First, it is in the Repeating functions section, as it was originally designed to work with repeating fields (which existed before FileMaker was relational).
The second curiosity is that it returns the "last non-empty value", whereas relationships (as in Phil's methods) can return a blank (though unlikely). So that is something to be aware of with Last(); either a feature or a problem, depending; you can be surprised however if you point it to a related field which is sometimes blank, and it returns the last value, from a preceeding record (not the last record).
Thanks! Can't believe I forgot about the Last() function!
Thanks PhilModJunk and Fenton Jones,
I got it working.
- I was having Primary Key and foreign Key relationship between my tables.
- I sorted Year by Descending order. (This is what I was missing)
- Then I displayed Grade values to Students Layout.
There is one more problem:
What to do, if I want to see Years from Grade table in Drop-down menu for that students only and after selecting Year from Drop-Down menu It will fill grade values in Student Layout..???
(Its like, Selecting value from drop down menu will autofill other values in textbox based on drop-down menu's value..)
How can I do this.??
I'm not sure I follow that.
On what layout are you selecting the year?. Data from what fields, from what table do you want to "auto-fill"? And there are two possible results here: 1 ) Data from a related table is displayed 2) data from the related table is copied into the current table. Which do you want?
I have same 2 table (Students [studentId, FName, LName...], Grades[studentId, Year, MathGrad, ScienceGrad, GPA]).
- I want to display Year in drop down on Students layout. I'm not sure how can I display Year from Grades table in drop-down menu for Related student on students layout.?
- Then, based on selection of Year from drop-down menu, I want to display/autofill MathGrad, ScienceGrad, GPA from Grades Table on Students layout.
Thanks for quick reply.
I'm not sure how can I display Year from Grades table in drop-down menu for Related student on students layout.?
Do you only want to see years for which the current student has a related record in Grades? This is called a conditional value list. See these links:
I think you only want to see this data, not copy it.
You can combine two pairs of fields to define a relationship:
Students::StudentID = GradesByYear::StudentID AND
Studetns::SelectedYear = GradesByYear::Year
GradesByYear would be a new table occurrence of Grades in your relationship graph. You create it by selecting grades, then clicking the button with two green plus signs.
Then you can place fields from GradesByYear on your students layout. Select a year in the drop down menu in SelectedYear and the matching fields from Grades will appear to display the desired data.
I aleady created new table occurrence and displayed year in drop-down menu from table occurance (GradesByYear).
"Students::StudentID = GradesByYear::StudentID AND
Studetns::SelectedYear = GradesByYear::Year"
But, I dont have SelectedYear in my Students table. Do you want me to create one in student table..??
Right now, I have follwing relationship:
Students:studentId = Grades:studentId
Grades:studentId = GradesByYear:studentId
I didn't understand this line:
"Select a year in the drop down menu in SelectedYear and the matching fields from Grades will appear to display the desired data."
Yes, you have to define a field in students where you can specify the year. In my example it was called SelectedYear.
You need a relationship that links two pairs of fields in the same relationship:
Students::StudentID = GradesByYear::StudentID AND
Students::SelectedYear = GradesByYear::Year
Drag from StudentID to GradesByYear::StudentID, then drag from SelectedYear to GradesByYear::Year. You can also double click the relationship line to open a dialog where you can set this up also.
Put SelectedYear on yoru students layout and format it with a drop down list of years. When you select a year, you create a valid relationship that matches by both StudentID AND the SelectedYear to a specific record in GradesByYear. Thus the fields you place on the layout from GradesByYear will then display data.
I got it working...
Thank you very much :-)