2 Replies Latest reply on Nov 7, 2014 12:26 PM by illumine

    Value List Built From Separate Fields Taken From Current Record Only



      Value List Built From Separate Fields Taken From Current Record Only


      INTRODUCTION  I'm in the middle of developing a FM DB to help Special Education Preschool Teachers manage the enormous data collection requirements levied by federal and state regulations (think Common Core), as well as manage general day-to-day classroom activities not already managed by existing data-management software.

      SUMMARY My particular problem here relates to the need for a constrained dynamic value list which will then populate a pop-up menu.

      BACKGROUND  Teachers must have parental permission to photograph each student (of course, some don't give it).  The DB solution has a container field on the "Student Detail" layout meant just for the student's photo. Immediately next to this container field I have placed a button that controls a popover which hosts fields related to the "Permission to Photograph" document the parents opt to sign for the school. One of the fields is the name of the authority figure (parent or legal guardian) who gave written permission to allow official photography of their child.

      SPECIFIC REQUIREMENTS  I want this field to be a pop-up menu populated with a list of up to three values that originate in three separate fields: "Parent 1," Parent 2," and "Legal Guardian" located in the same "Student" table.  Further, I want this value list to be values in those fields from that one (current) record ONLY.

      RELATED THOUGHTS  It doesn't appear to me that the built-in FM value list management feature provides for the option to gather values from three separate fields and from the current record only.  As such, my guess is that I would have to build a script that dynamically executes via scrip trigger from each of the three fields and places that list into a field specially designated for this purpose.  My pop-up menu would then take its values from the special field.  Online research also leads me to believe that a self-join may help to solve this problem from a different angle than described above.

      Am I on the right track?  Any advice is welcome.


      FMPA 13, skill level: novice to low-intermediate

        • 1. Re: Value List Built From Separate Fields Taken From Current Record Only

          No script needed. Personally, I'd put that data in a related table linked to the current record where you have this data such that "parent 1", "Parent 2", "Legal Guardian" can be up to 3 records that are linked to the record for that student. Your conditional value list set up then becomes much more straight forward and you can even use a design where one field identifies the person as "Parent 1, Parent 2, or legal guardian" and another provides the actual name of that person.

          But working from what you have, you can get the value list that you want without any script needed.

          Define this calculation field, named cParentList, and specify Text as the result type:

          List ( Parent1 Field ; Parent 2 Field ; Legal guardian field )

          Then define this relationship: (Use your field and Tutorial: What are Table Occurrences? names in place of mine):


          StudentTable::__pkStudentID = StudentTable|Self::__pkStudentID

          StudentTable and StudentTable|Self are two occurrences of the same data source table. __pkStudentID must be your primary key field that uniquely identifies each student (Don't use a student name field here!).

          You can then define a value list with the "use values from field" option to list values from StudentTable|Self::cParentList and you can select the "Include only related values starting from StudentTable" option to limit the values to just those from the current record.

          If you change the value of one of these three fields, you will need to commit records (script step or click the layout background) before the value list will update to show the change.

          For more on conditional value lists, (but this option isn't found there), see: "Adventures in FileMaking #1 - conditional value lists".

          • 2. Re: Value List Built From Separate Fields Taken From Current Record Only


            Thanks to your reply, my problem was solved in less than three minutes - flat!  I really appreciate your clearly delineated solution to include the ever-so-important field calculation and table occurrence relationship schema .  You're awesome, my man.  Thanks.