4 Replies Latest reply on Oct 4, 2015 4:19 AM by LAPaiva

    List in field depends upon Global Variable in script?

    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.

        • 1. Re: List in field depends upon Global Variable in script?
          erolst

          Not sure why you have all those calc fields.


          If you have this relationship:


          Student --< ActivityParticipation >-- Activity >-- Booster

           

          Then this …

          lpaiva wrote:

          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.

          … could simply be done by basing your report on the ActivityParticipation layout, after finding all records that are related to an activity with a foreign BoosterID. (Activity::BoosterGroupID = "*")

           

          If you want to do this:

          lpaiva wrote:

          The problem is when I want to use the same layout for the Music Boosters

          You can use the same report layout and simply search for all records that denote Activities that belong to the Music booster group. (Booster::BoosterGroupName = "Music")

          • 2. Re: List in field depends upon Global Variable in script?
            LAPaiva

            It's interesting how a break can bring clarity. I figured it out... I just needed to put a global field in my Activity Participation calculation field: If (Exact(Activity::zk_CommitteeID.t;zg_CommitteeID.g);Activity::Activity.t;""). For some reason a global variable isn't working. Anyways, this helps eliminate the need for separate calculation fields per booster group as well. Thanks!

            • 3. Re: List in field depends upon Global Variable in script?
              erolst

              lpaiva wrote:

               

              It's interesting how a break can bring clarity. I figured it out...

               

              Well, that break may have brought you clarity on how to solve an issue that you shouldn't have in the first place.

               

              But did you actually read my response?

              • 4. Re: List in field depends upon Global Variable in script?
                LAPaiva

                Yes, erolst, I read your response and considered it. I may have oversimplified my question and so I guess it wasn't a good question. Thank you again for taking the time to help!