Creating a cross join table for a gradebook

[serverge]

I am trying to create a simple gradebook in FMP10. I have a table of students and a table of assignments. I have created an intermediate table called grades that is related to students and assignments. It has a fk_studentid and an fk_assignments id as well as fields for category, date, and most importantly grade.

My problem is:

I would like to create a portal that works like a spreadsheet so that it lists the students down a column on the left and the assignments as the headers for the remaining columns. I think this is sometimes called a crosstab query or a cartesian product. I want to be able to click at any point on the portal and immediate put in a grade for a particular student for a particular assignment.

I am assuming I will need to script a way to create a new record for every student/every assignment so this will have a blank record ready and waiting. I have seen this done as an append query in an access database. How can I set this up in filemaker to work in a similar way?

Thanks,

Mike

[efen]

Something like the attached? Not in a portal though.

C_tab.zip

[serverge]

That is really helpful! I keep forgetting how powerful list view is as a layout.

My only question would be in this scenario:

Let's say I have 5 students now and 5 assignments. If I were to add a new assignment, I would like a way for that assignment to be automatically "assigned" to each student (through script I suppose) so that after I add the new record for the assignment, I could go to the report page and have the blanks sitting there waiting for the grade entry for each student without having to click the arrow for each student individually. Likewise, if I add a new student to the class I would like to have a way for every assignment (even those previously created) to be automatically assigned to them.

What I am working on may have 300 students who may be added at a later date, or more assignments added as we go. Having a scriptable / automatic way to keep the students and assignments constantly crossed with each other would be an immense time saver.

Thanks,

Mike

[Vaughan]

Like a printed grade book, each page has a set number of assignments that can be accommodated. To get the display you want, the cross-tab layout would have to be set up with a pre-determined number of assignments.

Nice demo efen!

[serverge]

That would be okay. I cannot image having an infinite number of assignments. But since my project would have ~300 students and each having 20 or so assignments per grading period, I want to find a way to automatically cross-tab (or whatever) all of them so I don't have to click 300x20=6000 times to create all the blanks for grade entry.

A typical grade book would accommodate up to 30 assignments per page. That would be more than enough. Efen's awesome example covers all the layout issues. I can now expand his example especially since it covers an unlimited number of students already. I just need a way to script what he is doing with the arrows and have it cover every student x assignment and every assignment x student whenever 1 of the 20 (or 30) maximum assignments are added or a new student is added.

Thanks to everyone ahead of time for your help!