1 Reply Latest reply on May 11, 2009 11:16 AM by philmodjunk

    One to many in a report/layout

    meschj

      Title

      One to many in a report/layout

      Post

      I'm a newbie to Filemaker, and have worked with databases but more on the data manipulation and less on the reporting.

       

      I'm trying to create a report that provides a list of textbooks by course for students. 1 page per student.

       

      I've got 3 tables of information:

       

      Students

      -ID #

      -Name

       

      Schedules

      -student ID # (matches the similar field in students table)

      -course #

      -teacher

       

      Couses

      -course # (matches the one in schedules table)

      -course name

      -book1

      -price1

      ... (book2, price2, etc)

       

      I have relationships defined tying the student ID # fields and then the course # fields.

       

      I basically need the student name & ID number across the top. Then (for each course) the course name, course number, and teacher on a line, then immediately below the books and prices for that course.

       

      I've played with leading and grand summaries, sub-summaries, and the body, and even portals, but can't seem to get all the courses to list for each student. All I get is the same information repeated down the page.

       

      Any suggestions?

        • 1. Re: One to many in a report/layout
          philmodjunk
            

          The trick is in choosing the correct table as the reference table for your layout. (That's the table you choose under Layout Setup...).

           

          Select the Courses table for you layout. Now one record = one course. If you make it a list view style of report, the body part can be used for any fields that are needed for one course for a given student. Related fields from the other two tables can be placed into the header or subsummary parts as needed.

           

          To limit the report to just one student, do a find on this layout by entering the Student ID# as the search criteria.

           

          To see a listing of multiple students, Put the student ID and name fields in a sub summary part set to be visible when sorted by student ID.

           

          Side Comment: I see a reference to putting Book information into different fields of the same Course record. You might want to create yet another table for this data and link in relationships to your other tables.