Using portals to create different types of rosters for students, teachers and different type of classes

[LisetteG]

I am working on a summer camp database that essentially has one table for campers, staff members, clubs, classes, teams. There is also a "grouping" table which links staff members to their club, class and team; each staff member hashas one club, one team and one class. And one more called "group roster" which relates each campers with the groups (each camper has one club, one team and one class).

I am a novice FMP user, so if you have any suggestions for reconfiguring this arrangement please feel free to make them.

It seems to be working out so far, especially when using portals to generate group rosters and in profiling what staff members are doing..

My first question is about the possibility of creating portals containing campers' classmates for their class, club and team as they have a different group each time.

I would like to place portal information for a list of classmate in a different tab representing the class, club and team in the participant profile table. I am thinking that I might have reconfigure my relationships in order to accomplish this.

My second question is about how I can make a formula that would encapsulate the campers' group information when looking at one of the three rosters. So if I'm on the team 6 roster (portal) I can see the students name and I can also see that they belong to Class 5 and are in the yoga club.

Any assistance you can offer will be greatly appreciated.

CF_screencapture.tiff

[comment]

I'm not sure what's the purpose of your Grouping table. If each camper has one class, one club and one team, then a camper should be linked directly to a team - assuming a team belongs to one club and a club belongs to one class.

More precisely, since you have camp terms and apparently a camper can participate in more than one term, it is the camper's enrollment record that should be so linked.

The same thing applies to camp staff.

[LisetteG]

assuming a team belongs to one club and a club belongs to one class.

The teams, clubs and classes don't belong to each other in anyway. Each camper had different group of classmates in each group. Same for the staff members, they teach a club, class, and team and will have a different group of students for each grouping. Would this change your suggestion about not needing a grouping table?

[comment]

I don't know - I don't really understand the real situation. Are "team", "club" and "class" different types of a "grouping"? Are these groupings permanent (for the duration of a term)?

[LisetteG]

There are 12 teams, 12 classes, and 12 clubs. A camper belongs to one team; will attend a daily class with other campers (not their team members) for the duration of the camp, and they also join a club of their choice. So Camper Jim Lee could belong to Team 6, Class 12 and the Science Club. The groupings will stay the same for the duration of the camp term. I hope this clarifies the situation. Please let me know if it doesn't.

[comment]

Ok, but then why do you need the individual tables (Teams, Vlubs, Clases). What information needs to go there? I don't see any significant fields in any of them in your RG - except TeamName (which seems to be the same as GroupName in Groupings) and TeamCounsellor, which obviously doesn't belong there.

Back to your questions:

The way you have it now - using a join table (GroupRoster) to assign campers to groups - it is difficult to pick a group by type from the point-of-view of a camper. You say that "each camper has one club, one team and one class", but the structure allows a camper to have two clubs, no team and ten classes just as well.

I would suggest something that may not be strictly by the book, but I believe it should do the job: add fields for TeamID, ClassID and ClubID to the enrollments (Participant Profile) table, and link each one directly to an occurrence of the Groupings table (matching the GroupingID). Then you can see the classmates via a self-join of enrollments matching on CampID, teammates via a similar self-join matching on TeamID, etc.

It may be possible to reduce the number of self-joins to one - if you need to see only one group of "siblings" at a time.

[LisetteG]

The idea behind having 3 individual tables for the different groupings (clubs, teams and class) was that it would allow me to easily identify their class,club and team. I didn't use to have a table for each but then I realized that couldn't find a way to list the camper's club, class and team in one place. Please let me know if this makes sense. I appreciate your patience.

[LisetteG]

Then you can see the classmates via a self-join of enrollments matching on CampID, teammates via a similar self-join matching on TeamID, etc.

It may be possible to reduce the number of self-joins to one - if you need to see only one group of "siblings" at a time.

I think I understand your suggestion, except for this last part. I've never worked with self-join relationships and don't understand how I will be relating the field, could you please elaborate? It would be really, really helpful. Thank you!

[comment]

Here's a simplified example:

GroupSiblings.zip

[LisetteG]

Thank you so much for all of your assistance. Your advice and sample file really helped me move forward in this project.