5 Replies Latest reply on Mar 28, 2013 4:42 PM by philmodjunk

    Need help creating a report with a variable amount of fields

    AnthonyDouglas

      Title

      Need help creating a report with a variable amount of fields

      Post

           I have put together a database to help me keep track of class attendance for the classes I teach.

           At my school there are 10 homeroom classes which take the same lessons together every period. I teach each homeroom class twice a week, however due to different school events, classes are cancelled and not every homeroom class takes the same amount of lessons. In my current database I only track absences and a present status is implicit. 

           What I would like to do is create a report that lists all students in a homeroom class, all classes that homeroom class has had, and whether a student was absent for that class.

           Ideally I'd like to have students listed down the vertical axis, classes taken listed along the horizontal axis (which is variable), and the intersection of the 2 showing whether the student was absent or not.

           I currently have the following tables

           HRClass (holds info about each homeroom class. Id, grade, class number, teacher, size)

           Student (pretty self explinatory. ID, student id (given by school), student grade, student hrclass, student name)

           Course (mostly for extensiblilty. ID, course name)

           ClassDetails (id, course id, date, period, grade, hrclass, description)

           Attendance (id, ClassDetails id, student id, absence type) 

        • 1. Re: Need help creating a report with a variable amount of fields
          philmodjunk

               Are these the relationships that you have?

               Attendance>--------Student>-------HRClass-----<ClassDetails>------Course
                                                                                                    |
                                                                                                    ^
                                                                                           Attendance 2

               Student::ID = Attendance::Student id
               HRClass::Id = Student::student hrclass
               HRClass::Id = ClassDetails::hrclass
               ClassDetails::id = Attendance 2::ClassDatails id
               Course::ID = ClassDetails::course id

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Am I correct that each column to the right of the student's name represents a different record in ClassDetails which the student may or may not have been in attendance for?

               If so, a horizontal portal method can be used to set up this cross tab style report.

               Start with a list view layout based on Student. Put the desired fields from student in the left hand portion of the body.

               In the header, place a one row portal to ClassDetails on your layout and put the description field from that table in the portal row. This provides the column header for the first class. Now duplicate this portal, open portal setup... for the duplicate and change the initial row setting from 1 to 2. This will then be the header for the second class. repeat as needed to provide header labels for additional classes.

               Go back to the body and add a one row portal to attendance. Put absence type in the portal row. Give it the following Filter expression:

               Attendance::ClassDetails::ID = GetNthRecord ( ClassDetails::id ; 1 )

               For each subsequent column, duplicate this first layout, but change the portal filter expression to use 2, 3, 4 etc insided the GetNthRecord function call.

          • 2. Re: Need help creating a report with a variable amount of fields
            AnthonyDouglas

                 PhilModJunk,

                 Thanks for your reply. All your assumptions were pretty much correct, the only problem is that each homeroom class has a different total amount of classes attended. For example Grade 1 Class 1 had a total of 25 classes, Grade 1 Class 2 had a total of 20 classes, and so on. So what I need is a row that displays a variable amount of fields. 

                 I'm not sure if I'm articulating it very well, so here's a screen cap of 2 classes in the kind of format I would like to produce. If you notice, the total amount of lessons for each class is different. (Ignore the #'s in class 2's table, I forgot to expand the rows :S )

            • 3. Re: Need help creating a report with a variable amount of fields
              philmodjunk

                   You cannot dynamically vary the number of columns in your report layout. I suggest that you not try to put two classes in side by side columns like this. It really isn't possible in any practicle sense in FileMaker and I don't see the advantage to such a huge number of columns at one time on your screen.

                   You can set up your layout for the maximum number of classes, say 25 and the right hand, unused columns will be blank when there are less than 25 classes.

                   Note: it is theoretically possible to use a Data URL with a Web Viewer to produce a cross tab report with a variable number of columns if you use the right "HTML table tags", but I would be very reluctant to tackle that complex an undertaking for something that can only be displayed in a web viewer and with such a potentially large number of rows and columns.

              • 4. Re: Need help creating a report with a variable amount of fields
                AnthonyDouglas

                     Sorry for the misunderstanding. You are completely correct that I wouldn't want to classes presented side by side. That was merely me making up for the fact that I was unable to upload to screenshots in the one post, so I just took a screenshot of the mockups side by side. Sorry I forgot to mention that.

                      

                     You did however answer my question by saying that a dynamic amount of columns is not possible. I will take your advice and have an upper limit of columns for the report. The reason I wanted it to be variable was more for extensibility. Right now I only teach 2 lessons per class per week, but if that were to change in the future, I would have liked to have been able to handle that automatically, but I guess I'll just have to edit that layout if/when that time comes.

                      

                     Thanks again!

                • 5. Re: Need help creating a report with a variable amount of fields
                  philmodjunk

                       While the number of columns is fixed by the layout design, the data you show in each column need not be fixed. You can set up a system where by the specific classes that appear on the layout can be specified by the user.

                       You might specify the classes for a range of dates with the classes that meet on the first day of the specified range appearing in the leftmost column. Specify a different rarnge of dates and get a different class in the leftmost column... Such an approach uses portal filters or "filtered relationships" to dynamically select which records appear in a given column so it is quite a bit more complex to set up than what I have described doing with the horziontal portal.