Create new records based on two relationships

[skip314]

This is probably simple, but I can't seem to figure it out. I'm creating a gradebook. I have four classes of about 30 students each semester and each class has a unique set of about 60 "assignments" (which could be quizzes, homework, exams, lab, etc.). My goal is to make my gradebook.fm7 table contain a record for each grade. Each record would show the student, the class, the assignment type, the assignment number and the score for that assignment.

Here's what I have so far:

I have a table of all students that can be filtered by a relationship to show a specific class. I have a second table of all assignments that can be filtered to show a specific class, assignment type and assignment number. I'm doing the filtering with relationships to global fields in my gradebook.fm7 table.

This is what I can't figure out:

I want to either run a script that will populate my gradebook.fm7 table with every student and every possible assignment for that student up front, then as the semester goes on I will enter the score for each. Or, if it makes more sense (and if it's possible), I would like to generate records on the fly when I enter a score. However, on any given day I will be entering about 60-70 scores, so I don't want to have to click a button or navigate menus before/after each entry (so running a script up front might be a better solution).

[Ender]

I would probably use a script to create Student-Assignment records for each Assignment at the time an Assignment is graded. This could be done by importing the current enrollment list into your Student-Assignment table then setting the Assignment Type and Assignment Number using Replace, auto-entered calcs, or looping.

The problem with populating everything up front is that class lists change over the course of the school year, and you might not know all of the assignments up front or they may change.