change schema to make grouped-data report work?

[Robert Lauriston]

For a class-enrollment application, I've got a set of classes that sometimes have multiple teachers and always have multiple students, i.e. two many-to-many relationships with join tables. One of the requirements is for a grouped-data report that's supposed to look basically like this:

sub-summary by teacher

sub-summary by class

names of enrolled students & related data

The idea is to get a printout for each teacher showing all their classes and which students are enrolled.

The problem is that if I do the report with "Show records from" set to the teacher-class join table the report shows only one student per class. If I change "Show records from" to the student-class join table, I get only one class per teacher.

I can't use a portal because the number of students varies too much.

I guess I could kludge a denormalized table just for this report, but it seems like there ought to be a more elegant way.

Any other suggestions? Is this maybe a bug in v.7?

Here's the schema:

http://www.lauriston.com/schema_v2.gif

Here's the file itself:

http://www.lauriston.com/Camp2004v2.zip

[Ender]

Welcome Robert!

I think I get what you are trying to do: Given that multiple instructors could be assigned to the same class; for each instructor, show their class lists.

In order to show multiple students, the report should be based in the ClassEnrollment table. But as you pointed out, you can only see one instructor for each class. I don't see a way to do this with one summary report. A couple options though:

On the ClassEnrollment based summary, put a portal showing multiple instructors that are assigned to that class (This would go in the Sub-Summary by InstructorAssignment part. You can turn on sliding to reduce the size of the portal to fit.)

Or, use a script to loop through each instructor, showing that instructor's classes and class lists (From Instructor Go to Related Records [ Show, ClassEnrollment ]) For this option you will have to set some global so the ClassEnrollment report will know which instructor to show.)

Later.

[Robert Lauriston]

Sliding might do the trick, I didn't realize you could do that on a portal. Thanks!

[Robert Lauriston]

No luck, same results with the portal as without.

[Ender]

See attachment.

[Robert Lauriston]

Thanks, but that doesn't produce the required results.

Framemaker tech support says there's no way to get what I need without creating a denormalized table, so I'll repost in the reports section.

[bruceR]

Seems to me the portal should work fine, see attached.

Camp2004.zip

[Robert Lauriston]

After FileMaker tech support told me it was impossible, I got a simple solution from RalphL in the Layouts section: do the report showing records from InstructorAssignment (instructors-classes join table), and make the body of the report a portal to ClassEnrollment (classes-students join table). That way, all the foreign keys are in the report, and everything works as required. Thanks.

[bruceR]

Yes, that's exactly what the attachment to my last post showed.