3 Replies Latest reply on Feb 28, 2011 1:54 PM by philmodjunk

    Exporting detailed list

    JulienMenard

      Title

      Exporting detailed list

      Post

      I'm in the process of retreiving data to send tax reports to our students. We basically have two lists : students and courses. I have no problem making a list of all our students. I have no problem with having a list of the people signed up for an individual course either. What causes an issue is that I want to know who to send a tax report for 2010, so I would need not only a full list of students but one that would also include ALL the courses the students were respectively signed up for. As of now I've only managed to come up with a list that includes only 1 of the course each students were signed up for.

      I know this is really a specific issue but I really hope someone could come up with an answer: is there a way for me to have a full list of all students which would feature ALL the courses they've taken without having to go through each course individually in the calendar and taking out the student lists from those classes one by one?

      Any insight is of great value to me!

      Many thanks,

      Julien

        • 1. Re: Exporting detailed list
          philmodjunk

          It's definitely possible, but requires tables and relationships designed to make this work.

          You'd need something like this:

          Students----<Enrollment>-----Courses

          Students::StudentID = Enrollment::StudentID
          Courses::CourseID = Enrollment::CourseID

          A single record in enrollment pairs a specific student with a specific course that they took. A date field in enrollment can record the year in which they were enrolled.

          WIth that structure, you can create a report based on Enrollment, grouped under sub summary part sub headers by student and course, with fields included in this layout from the Students and Courses tables. Perform a find for all enrollment records dated for the year 2010, sort them and you have your report. You can also perform a find for just one student for a specific year if you need to send out a report to a specific student for the year.

          • 2. Re: Exporting detailed list
            JulienMenard

            Thanks a lot for answering so fast. I'm glad to hear it's something that is doable. I must admit that as of now my experience with FM is very minor and I have no idea how to set up the proper tables and relationships in order to make this work. Would you be kind enough to provide me with somewhat of a step by step procedure I could follow?

            Thanks a bunch!

            • 3. Re: Exporting detailed list
              philmodjunk

              An actual database for this purpose will likely have many more tables and that truly goes beyond the scope of what's possible here in a forum.

              You'd define the students table to have all fields needed to record the data unique to one specific student. "Name, age, gender, major, etc..." To that list of fields add a student ID field defined to be an auto-entered serial number to use in the relationship I described in the last post. The same is true for your Courses table, define fields as needed in this table so that each record uniquely documents each course taught at the school. A CourseID field is yet another serial number field to use in relationships with other tables.

              Enrollment get's a bit trickier. Except for StudentID and CoursesID, it normally does not have any other fields that correspond with a field in Courses or Students. The only other fields you add to this table are those fields, such as an enrollment date that are unique to one specific student taking one specific course.

              Here is a demo file you can download (click blue download button), that matches Contracts to Companies the same way you need to match students to courses: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

              Here's a tutorial on Summary Reports--the style of report you'll need for this:  Creating Filemaker Pro summary reports--Tutorial

              Now for another layer of complexity. Chances are that any given course this school teaches may be offered more than once for a given semester. Thus, you'll likely find you need this table structure:

              Students----<enrollment>----CourseOfferings>-----Courses

              Thus, if Biology 101 is offered MWF at 8 am and also is offered at MWF at 2 pm, you'd have one record for Biology 101 in the courses table, but two records, one for the 8am slot and one for the 2 pm slot in CourseOfferings. A student, typically, would only enroll in one or the other-so you end up with the additional table between enrollment and Courses.