Our PTA would like to track students that donate to booster groups. I have 4 related tables:
(a table to join Students & Activity)
ActivityName (such as Soccer, Golf, Swimming, Orchestra, Band, Jazz)
BoosterGroupName (such as Athletics, Music)
In the ActivityParticipation table I have a calculation fields to determine which activities are part of the Athletics Booster Group:
zi_ActPartSports.c = Case(PatternCount (BoostersACTIVITY::BoosterName.t; “Athletic” );Activity::Activity.t;“”)
zi_ActPartMusic.c = Case(PatternCount (BoostersACTIVITY::BoosterName.t; “Music” );Activity::Activity.t;“”)
In the Student Table I have a field to make a list of the sports a student plays: zi_actSportsList = List ( ActivityParticipation::zi_ActPartSports.c )
In the Student Table I have a field to make a list of the music group a student plays with: zi_actMusicList = List ( ActivityParticipation::zi_ActPartMusic.c )
I have a nice report for the Athletic Boosters that lists all the students (and their families) and lists the sports they play via the zi_actSportsList field. The problem is when I want to use the same layout for the Music Boosters I need to have the zi_actSportList be the zi_actMusicList or have it know which list I need depending on the global variable in my script.
I would like to figure out how to use the ExecuteSQL function to make a neat solution to this. Or learn if there is a better way. I would really appreciate suggestions.