3 Replies Latest reply on May 27, 2009 5:49 AM by Amy

    Conditional value lists



      Conditional value lists


      With the help I received on my previous question, I've made progress designing my database, and now I've reached another point where I'm stumped.


      I'm working on keeping track of placements of students with research groups (before I was calling these "mentors" but I've changed my terminology). A student is only in the program for one year, but we tend to work with the same research groups over and over again. When I'm looking at a record in the context of the Students table, it's entirely appropriate to have a value list pop up with all of the research groups and to use that to create a placement record. However, when I'm looking at a record from the Research Groups and creating a placement, I don't want my value list to have every student who has ever been in the program; I only want to be able to pick from the students who are in the program this year.


      I've created a table called This Year. It only has one field (Year) and one record (the year is 2009). (There are two TOs of it in my graph.)


      relationship graph 


      Right now, when I'm on a layout of the Research Groups table, I can create a placement by picking a student from my All Students value list. What I want to be able to do is have a "Year Students" value list that only shows the students from this year.


      Here's what I've tried:


      value list

      Unfortunately, when I try to use this value list, it says no values defined. (It works great with my All Students value list.)


      And so my question is: When I'm in a layout of the Research Group table and using my portal to my Placements table so that I can create records in Placements, how do I have a pop-up value list that only shows the students from this year?


      And if that's too easy :smileywink: , when I'm looking at a record in the Placements table, I'd like to be able to assign specific Research Group Staff to the placement via a conditional value list that only shows the staff members who are assigned to that Research Group.


      Any advice is appreciated.



        • 1. Re: Conditional value lists

          I found a work-around, but I'm not sure what the right way is to do this.


          If I go into my This Year table and change the storage of my year field to global, then my value lists work.


          When I change it back from global to non-global, then my value list will continue to work if (and only if) I've already chosen something from it on that particular record. If a record has a null field, then the value list returns to being non-working.


          I'm pretty sure that using a global here isn't the best option, but I'm not sure what the correct way is to do this. 

          • 2. Re: Conditional value lists

            You might need to nix that table of year. 


            Create a second occurrence of the student table. Let there be a self-join between your current student 'table occurrence' and the new student 'table occurrence'. The relationship criteria will be a match between the two table occurrences based on year. So create another field for the student table for year (in addition to your other field for 'year'). This new field will be a global field. It will act as a filter. The value list will start from the table occurrence with the global, and will end with the other table occurrence of student table. Put in a value in the global field, and it will determine the contents of the value list. 


            But, do you really want a year field in your student table? What if you student is enrolled for more than one year?  

            • 3. Re: Conditional value lists

              I'm not sure if I see the advantage of putting another field in the Students table with global storage versus having my This Year table with a field with global storage. I'm concerned about having a field with global storage because in the future I'm hoping to have the database be shared. I've read that if I have a global, there isn't an easy way to change its value for all users of the database -- it would revert to the value that it held the last time the database was opened in single-user mode.


              I'm sure that I want a year field in the Students table. Our summer program is designed to be something that students can only do once. In 26 years, we've never had a repeat student. (On average we serve about 80 students a year.)


              The reason that I have my This Year table is that we only focus on one year at a time, to the exclusion of all others. In February the new batch of students gets added. In March - May, we work on arranging their placements with the Research Groups. Then we assign them to dorm rooms, counselor groups, tutor groups, etc. In each of these situations, we want our value lists to only have the current students -- not the alumni. Once we get all the information set up in the spring, we use it when running the program over the summer. Once the students leave, we're much less likely to care about which dorm room they had, etc. Where the historical information becomes important is during the placement process in the spring. When I'm setting up a placement and looking at the record of a research group, I want to be able to refer to their past students.


              It's looking like I'm going to need a global somewhere with the year to make these value lists work. The question is where to keep it -- and how to make sure it updates properly once the database is being shared on a server.