5 Replies Latest reply on Aug 20, 2010 9:15 AM by philmodjunk

    creating value list from tables showing only some of the records

    med

      Title

      creating value list from tables showing only some of the records

      Post

      I hav a teachers table containing names of teachers, some are also councelors and others are just teachers (indicated by "type" field in the table). I designed a layout which has two fields one to be filled by councelors and one by teachers. How can I design value list to view only some records of the table depending on which field I am dealing with?

        • 1. Re: creating value list from tables showing only some of the records
          philmodjunk

          Are you asking for two value lists--one for teachers and one for counselors?

          Or are you asking for a setup that finds the records for all counselors when you select "counselor" and finds the records for all teachers when you select "teacher"?

          I think you want the first option.

          You have a field in teachers that contains "teacher" if they are a teacher and "counselor 'if they are also a counselor?

          You can define two calculation fields:

          cCounselorKey : If ( type = "Counselor" ; TeacherID ; "" )

          cTeacherKey : If ( type = "Teacher" ; TeacherID ; "" )

          I'm using a field, TeacherID that would be set up as a serial number field because its possible to have two teachers with the same name. To see a list of teacher names define a two column value list listing cTeacherKey in column 1 and the teacher's name in column 2. Selecting a value from this list will enter the TeacherID--not the name into the field you format with a value list.

          Do the same with a second value list but refer to cCounselorKey instead of cTeacherKey to get a value list of teachers who are also counselors.

          This is a simplified version of a conditional value list. It's also possible to use a relationship between two (or even the same ) tables to do this, but that doesn't seem necessary based on what you have posted here.

          • 2. Re: creating value list from tables showing only some of the records
            med

            I tried your solution. I created the two calculation fields as you mentioned, cteatherkey and ccouncelorkey.

            Then I assigned a drop down list with a value list specifying ccouncelorkey plus the name. But when I tested it I see a list of only the first record with value in ccouncelorkey although more records have values (as I checked from the table layout).

            I went to the table and changed the type of ccouncelorkey to text (it retained all values) then I tested again and I got a correct drop down list.

            Does this mean that a calculation field cannot be in a value list or do I have another problem?

            I would also be interested to know about the solution using relationships, because this situation will repeat a lot throuout my system.

            • 3. Re: creating value list from tables showing only some of the records
              LaRetta_1

              I believe you might be interested in conditional value list.  First field would hold Type (whether Counselor or Teacher) and the second list would hold the names of that Type.

              A demo is worth a thousand ill-placed words.  Here is a demo by Comment (Michael Horak) which is the best example available.  He has several over on FM Forums (which is free to register) and you can download other conditional value list examples from his Users blog.  This is simple but is 2-tiered & self-clearing

              http://fmforums.com/forum/showpost.php?post/195641/

              He has more complex 3-tiered and conditional value lists is portals as well.

              • 4. Re: creating value list from tables showing only some of the records
                med

                I figured out why the calculation field was not working for the value list. I noticed that I had to specify whether the result is text or number. It works now.

                • 5. Re: creating value list from tables showing only some of the records
                  philmodjunk

                  And LaRetta has posted an excellent example of the relationship based approach I referred to. (Thanks)