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::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.
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?
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:
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?
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.
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.
Nevermind, i just found this on another thread. Thanks again.