Jump to content

change schema to make grouped-data report work?

This topic is 7110 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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:


Here's the file itself:


Link to comment
Share on other sites

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.)


Link to comment
Share on other sites

  • 4 weeks later...

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.

Link to comment
Share on other sites

This topic is 7110 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.