Are these the relationships that you have?
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.
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 )
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.
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.
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.