Force creation of related records?

[scotttro]

Sorry this is so verbose - I've been fighting with this for a couple of weeks now, and it's driving me nuts. I want to make this clear. To skip the setup and just get to the question, jump to the last paragraph.

I'm creating a rather complex (for me!) FM7 database to keep track of students' grades and performance for an entire year for my small school (8 teachers, 200 or so students). There are about 50 classes, in each class there are about 40 chapters, and each chapter consists of half-a-dozen datums. Students need to be registered or transfer classes at any time (it's a continuation school). The idea is that staff can monitor and analyze student performance across classes and over a semester or year, and students can "go back" and cover material from any time before they were registered in the school or class.

I've created a 3-table database; "Students" (biographical data & schedule), "Enrollment" (a join table), and "Classes" (records for each class). Everything works so far: schedule entries into "Students" creates related records (one record per class, six classes per student) in "Classes" via the "Enrollment" join table. I can display records for a particular class, and I can examine all a students data. However, I don't want a couple of hundred fields for individual chapter data in the "Classes" table. Instead, I'd like to create a fourth table "Chapters" which would create records for each students chapter data for each class. I have already gotten it to create records upon entry into portal fields, but this won't work for me. The problem is, I need to create all 40 related chapters records simultaneously with each "Classes" record: it's important for the staff to see *all* the data for students' performance, even data that came before the student was scheduled in the class or if it's blank. Also, it would be a nightmare to troubleshoot or modify.

How can I "force" the creation of 40 "Chapter" records with the creation of each "Class" record? I'd like to avoid using scripts as much as possible - automatic creation of the records would seem to be more robust and foolproof than scripting, and I've learned that my users (being teachers) just can't resist doing things "unexpected"...

Thanks,

Scott

[Vaughan]

"I'd like to avoid using scripts as much as possible"

Do it with a script. This is one of those cases where it's not avoidable. Design the interface so that running the script is unavoidable: custom menus are great for this.

[comment]

It's not avoidable only if you accept the premise that records should be created before there's any data to put into them. I do not accept that. I believe records should be created only when needed.

If I understand the description correctly(?), there need to be TWO additional tables: one table of Chapters (describes the chapters of each parent Course), and another table to record a student's performance in a specific chapter (a join between Chapters and Enrollments).

The requirement to see a student's performance in ALL chapters of a course, even when there is no data for some of the chapters, is a display issue and should not affect data structure.

[scotttro]

Part of my problem - maybe most? - is that I quickly lose track of what scripts do, how they do them, what changes will affect the script, so on and so on. Perhaps my brain is just too old and rattled to keep track of such things. I find the "Relationships" display much easier to understand and keep track of. I *did* in fact end up using "macros", but during the design of the database: what I ended up doing was to create 40 identical Chapter tables (using QuicKeys), then linking them to create related records from the "Classes" table. Then, I placed 40 identical portals in the "Classes" layout (set up one, then duplicated it and relinked the fields to the appropriate tables, again using QuicKeys). Only took a couple of hours. The advantage of this is that I can display the "Chapter" assignments, looking perfectly ready to enter data into, without actually having to create the records until data is in fact entered.

Edited June 26, 2008 by Guest

[comment]

I quickly lose track of what scripts do, how they do them, what changes will affect the script, so on and so on.

That's what ehm... comments are for.

what I ended up doing was to create 40 identical Chapter tables

I am quite sure that's neither necessary nor advisable.

[scotttro]

I am quite sure that's neither necessary nor advisable.

Why not? It seems at this point to work perfectly.

[comment]

Because any change has to be replicated 40 times. That alone should be reason enough, but there are others:

Because a table represents an entity, and you don't have 40 chapter entities.

Because when a new course is added, and it requires 42 chapters, you will need to make a structural change where all that SHOULD be required is user action.

Because 40 relationships take 40 times longer to evaluate than one, thus wasting CPU cycles and disk revolutions, increasing energy consumption and contributing to global warming.

Edited June 27, 2008 by Guest

[LaRetta]

:jester: