3 Replies Latest reply on Aug 2, 2016 11:31 AM by philmodjunk

    How do I get the first (or last) record in a one to many relationship


      The following is in context of an educational program for teachers.


      I have a database where each entry in a "Teacher" table is joined to multiple records in a "Teaching History"  table by a "TEACHER_ID" match field.


      The "Teaching History" table is joined to a table "All School Data" via "SCHOOL_ID.  This table contains relevant information about all the schools in the state.  Some teachers have changed schools since they began participating with us. I need to return the most current school each is teaching at.


      I have a calculation field that returns the most recent year of employment "Current Year".


      I run a script to find all currently active teachers and their current schools but it returns the oldest year.


      How do I return the most recent year of employment for each teacher?


      Side note: I can see the information correctly with a portal but that doesn't provide me with a complete set of exportable data (to excel, for example, for reporting purposes).


      Thanks in advance.