7 Replies Latest reply on Sep 23, 2010 7:43 AM by d.justins

    Participant List Filter

    d.justins

      Title

      Participant List Filter

      Post

      This seems to be a complex problem, so hopefully someone can help me.

      This database tracks information about particpants in our organization's programs. The particpant may jump from one program to the other, so i've set up a way to track their enrollment. For each program i have a different field and i put a boolean value into that field depending on their status.

      Here's an example:

      Prog.1Prospect
      Prog.1Enrolled
      Prog.1Exited
      Prog.2Prospect
      Prog.2.Enrolled
      Prog2.Exited

      and so on...We have 5 programs right now.

      I've set it up this way instead of having a single field with a code because we have mulitple programs and there are many combinations of enrollment. With this setup, i can just do a find on one field in order to get that group of participants, completely ignoring whether they're enrolled in a different program or not. 

      The problem i'm encountering now is giving drop-down lists of the participants to my users. Before, it wasn't a problem before because i only had 80-100 participants, but now due to some more programs being added, i have about 800 participants. That's just too many to give them in a dropdown.

      I know i can filter out the list by creating a relationship with a table instance, but i can't figure out a way to make this relationship true with only the program selected i needed to because of how i have the enrollment setup.

      So, being the great community you are, i was wondering if anyone had any suggestions on how to either better configure this or how to use what i have to filter out valuelists.

        • 1. Re: Participant List Filter
          philmodjunk

          Leaving the value list issue to one side for a moment, You'll be able to avoid any number of headaches if you define an enrollment table that is related to your participant record. You'd setup your relationships as:

          Participants----<Enrollment>---Programs

          Participants::ParticipantID = Enrollment::ParticipantID

          Programs::ProgramID = Enrollment::ProgramID

          You'd also define the prospect, enrolled, exited fields in Enrollment.

          This will handle the many combinations of enrollment, but eliminates the need on your part to keep adding fields for each new program and makes for much easier reports as well.

          With this structure, you can use the conditional value list you describe by using Enrollment as the source of Participant ID's. The relationship would then filter by ProgramID.

          • 2. Re: Participant List Filter
            d.justins

            I'm not sure if i'm understanding the benefits or how this works. 

            Do you want me to create two tables here? 1. Enrollement 2. Programs? like a many to many relationship?

            If not, then where does the enrollment status play in?

            • 3. Re: Participant List Filter
              philmodjunk

              This is a many to many relatioship. You have many participants enrolled in many programs and each program lists many participants.

              The enrollment table functions as a Join table linking Participants to Programs.

              Since enrollment status is specific to a given participant's enrollment in a given program, you'd put these fields in the enrollment table as I specified in my last post.

              Here's a many to many demo file of the concept. It matches Many companies to many contracts, but the concept is the same:

              http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

              • 4. Re: Participant List Filter
                d.justins

                Okay, so i have that all set up, with some enrollment records, but  i'm getting confused as to how i show only the participants enrolled in a specific program.


                Do i set my layout to be on the enrollment table with participant fields, then do a find for the program i need?

                • 5. Re: Participant List Filter
                  philmodjunk

                  That's a good approach for reports you want to print. You can add any fields from the related participants and Programs tables to fill in the details. You can, for example put fields from programs in the header or a sub summary part and fields from participants in the body to show names and contact data.

                  You can also place a portal to Enrollment on your Program layout--which can be a better approach for data entry and browsing through the data.

                  • 6. Re: Participant List Filter
                    d.justins

                    So i have put everything you suggested into place, and it is working nicely. However, i am having a problem with some scripted finds.

                    I've been using a script parameter to bring in the program id and grabbing the script parameter with a variable at the beginning of the script. However, when i do a find, i'm not getting the results i want to.


                    Say i do a find where the ID = PROG1, i'm also getting the results of PROG11,PROG12 and so on. I've tried adding an = in front of the variable, but that screws things up...

                    wait, i just though... maybe add an =&get(scriptparameter) as the variable.

                    Well, i'm going to try that... but i'll post this just in case it doesn't work.

                    Thanks for your help.

                    • 7. Re: Participant List Filter
                      d.justins

                      Nevermind, i just found this on another thread. Thanks again.