4 Replies Latest reply on Apr 23, 2012 5:27 AM by jbrown

    Exporting the related records of each teacher, one teacher at a time, one document

    jbrown

      Hey all,

      How would I go about exporting related records of a record in a cyclic fashion?

      I have a table of teachers. Each teacher is related to student records based on school and grade. I want to export the records as pictured below:

      Teacher A: Student A

      Teacher A: Student B

      Teacher A: Student C

      Teacher B: Student A

      Teacher B: Student B

      Teacher B: Student C

       

      Each teacher has 100 students or so, so in excel, teacher A would have 100 lines.

      I want them to be in one file.

      But I'm not sure how to set up the script:

      Go to Layout: Teachers

      Find "Teachers'

      Go to first record

      Export all of teacher A's students in the manner described

      Go to the next teacher

      Export all of teacher B's students in the manner described.

       

      Is there a way to do this?

       

       

      Thanks

      jeremy

        • 1. Re: Exporting the related records of each teacher, one teacher at a time, one document
          beverly

          jeremy, do you have a relationship between teachers and students?

           

          If so, you can go to the "child file" (i.e. students) and put the related "parent field" (teacher) on that layout. You can export the related field(s), as well.

           

          Do you want a separate excel file for each teacher?

           

          Beverly

          • 2. Re: Exporting the related records of each teacher, one teacher at a time, one document
            jbrown

            I need one file for the whole school. The file will have each kids name on 10 lines one line for each teacher.   It's an export I need for some testing do that each teacher can access each child's name in the test.   The relationship is teacher to student by a grade and a school field

             

             

            Sent from my iPhone

            • 3. Re: Exporting the related records of each teacher, one teacher at a time, one document
              TomHays

              FileMaker can do most of what you want in one go.

               

               

              Method A:

               

              When there are multiple related values, FileMaker will export all of the related values on separate lines. I find this behavior a bit odd, but in this case it can be helpful.

               

              If there is a relationship such that when you are browsing the record for Teacher A, you can see students A, B, and C in a portal (of student:: ) on Teacher A's record, you should be able to export these two fields

              TeacherName

              student::Name

               

              The result will be

              Teacher A     Student A

                   Student B

                   Student C

              Teacher B     Student A

                   Student B

                   Student C

              Teacher C     Student A

                   Student B

                   Student C

               

              The teacher's name will appear only once. The student's name will be in the second column consistently (it isn't obvious in the above due to the text formatting.)

              Since you only have 10 teachers, you can edit the file in a spreadsheet and "fill down" 10 times to copy the teacher's name into your preferred formatting.

               

              Method B:

               

              If you have another intermediate table that lists all of the connections between student and teacher, you can export from that table instead.

               

              Say you have tables Teachers, Students, Enrollment.

              Enrollment has fields kf_Teacher_id and kf_Student_id and is used to connect students to teachers.

              You can export directly from the Enrollment table.

              Export the related teacher name and student name.

               

              -Tom

              • 4. Re: Exporting the related records of each teacher, one teacher at a time, one document
                jbrown

                TOm, thanks for the ideas.

                It seems like this has to be a script step, using the relationship between teachers and students (school and grade) to export these.

                 

                I really wish there was an 'append to excel' file. That's what i need: The database exports all the related records of the teachers one teacher at a time, putting the next set (Teacher B's students) under Teacher A's records.

                 

                I'm going to try to use a table to do this, create that intermediate table  via a script to get all the teachers and students in one table to be exported.

                I havea total of 60 teachers or so, and about 700 kids with which to do this, so the excel file will be over 5000 rows long. I'd rather have the database do all the writing rather than me having to copy anything.

                 

                If writing a new table doesn't work then I'll do it the copy/paste way.

                 

                I have never copied one record from a table to another. I assume it's simply a matter of setting variables and looping.

                Ill give it a try.