10 Replies Latest reply on Dec 21, 2010 4:07 PM by RahulSatasia

    Get last entered row from related table

    RahulSatasia

      Title

      Get last entered row from related table

      Post

      Hi All,

      I have two Tables (Students, Grades).

      - Grades contain values year-by-year.

      I want to display Last Year's grade only from Grads table to Students layout in Text Box.

      How can I do that.??

      Thank You.

        • 1. Re: Get last entered row from related table
          philmodjunk

          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.

          • 2. Re: Get last entered row from related table
            FentonJones

            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).

            • 3. Re: Get last entered row from related table
              philmodjunk

              Thanks! Can't believe I forgot about the Last() function!

              • 4. Re: Get last entered row from related table
                RahulSatasia

                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.??

                Thanks again.

                • 5. Re: Get last entered row from related table
                  philmodjunk

                  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?

                  • 6. Re: Get last entered row from related table
                    RahulSatasia

                    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.

                    • 7. Re: Get last entered row from related table
                      philmodjunk

                      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:

                      Custom Value List?

                      http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                      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.

                      • 8. Re: Get last entered row from related table
                        RahulSatasia

                        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."

                        thanks.

                        • 9. Re: Get last entered row from related table
                          philmodjunk

                          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.

                          • 10. Re: Get last entered row from related table
                            RahulSatasia

                            I got it working...

                            Thank you very much :-)