2 Replies Latest reply on Jun 13, 2012 2:30 PM by gktummala

    Loop through the records and fetch unique value associated with the record

    gktummala

      Hello,

       

      Is there a way to loop through the records and fetch only unique values associated with the record?

       

      We have a FM solution that maintains course information. And, here are the 3 main tables that I need to work on for the requirement.

       

      Tbl A: Course listing (list of all courses)

      Tbl B: Course offerings (list of courses offered in each semester)

      Tbl C: Instructor details.

       

      Relationship between each table:

       

      Courselisting::courseid = courseoffering::courseid; courseoffering::instructorid=instructor::instructorid

       

      Sample Data

      TBL A

      CourseID

      Description

      A

      Description1

      B

      Description2

      C

      Description3

       

      TBL B

      Semester

      CourseID

      Instructor

      Fall2011

      A

      John

      Fall2011

      B

      David

      Spring2012

      B

      David

      Spring2012

      C

      Sam

      Fall2011

      A

      Tom

      Fall2011

      C

      Amy

       

      The requirement is – user wants to add a new data field on Tbl A --> ‘CourseInstructors’. The purpose of the column is to show all the instructors who had been teaching the course for past few semesters. For which, I need to write a code to loop through all the records of Tbl B and fetch the unique names of instructor and show it among the Tbl A on FileMaker. Not sure how to approach the solution.

       

      Output:

      Tbl A

      CourseID

      Description

      Taught By

      A

      Description1

      John, Tom

      B

      Description2

      David

      C

      Description3

      Sam, Amy

       

      PS: We are using FM11 and the data source for FM is MYSQL. ODBC connection is used to link FM and MYSQL.

       

      Appreciate your help.