LAPaiva

List in field depends upon Global Variable in script?

Discussion created by LAPaiva on Oct 3, 2015
Latest reply on Oct 4, 2015 by LAPaiva

Our PTA would like to track students that donate to booster groups. I have 4 related tables:

 

Students

Student ID

Student Name

 

 

Activity Participation

(a table to join Students & Activity)

Student ID

Activity ID

 

Activity

Activity ID

BoosterGroupID

ActivityName (such as Soccer, Golf, Swimming, Orchestra, Band, Jazz)

 

Booster Group

BoosterGroupID

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;“”)

 

In the ActivityParticipation table I have a calculation fields to determine which activities are part of the Music Booster Group:

 

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.

Outcomes