4 Replies Latest reply on May 10, 2013 11:37 AM by GuyStevens

    Value List From Values in a Related Table?



      Value List From Values in a Related Table?


      Short version: I need to create a value list that contains one value for each record in a "Schools" table, but the actual value for each "School" record is pulled from a field in a related "Places" table.

      Long Version: I have a fairly simple database that I am trying to create a value list for.  I have a "Places" table that holds the name and address of all "Places" and I have a related "Schools" table that holds school specific information.  I am basically using the "Schools" table to filter the records of the "Places" table for all of our schools.  The name of the school is not stored in the "Schools" table; it is pulled from the "Places" table through a relationship based on the unique key for each "Place" record.

           What I am trying to create is a dynamic value lists that contains the names of all of our schools, one for each record in the "Schools" table.  Since the name of the school is not actually stored in the "Schools" table, I created a calculation field in the "Schools" table named "cSchoolFilter".  The calculation for that field is "=Places:Name".  This field does properly pull the name from the "Places" table.

           However, when I try to create a value list based on this field, i get the following error: "This value list will not work because the field "cFilterSchool" cannot be indexed."

           Am I doing something wrong, or is this just not possible in FileMaker?  Is there any other method I should be using to create this value list?

           PS: I can generate the needed list if I add a "PlacesType" field to the "Places" table and then designate all of the appropriate "Places" records as schools through this field, however that would break third normal form and partially defeat the purpose of having a separate "Schools" table.

           Any help with this would be greatly appreciated.

        • 1. Re: Value List From Values in a Related Table?

               What you need is a conditional value list. The relationship between schools and places can serve to filter your values to just those for schools or you can use a stored/indexed calculation field in places that only returns a value if the "place type" is "school".

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Value List From Values in a Related Table?

                 A value list automatically only shows each value one time.

                 Just use the field that contains the school name in your value list. Even if that table is not even related.

                 That would be the Places::Name field I believe.

                 One question: Why isn't the school name stored in the schools table?

                 I would believe you would create 1 record for each school in the school table. And then relate that to the places using the School ID.

            • 3. Re: Value List From Values in a Related Table?
              @PhilModjunk - Thanks for the links, I'll be sure to check them out. @DaSaint - Unfortunately I can't simply use the Places:Name field because, in addition to the all the schools, the Places table contains many records for places that aren't schools. I don't want the non-schools to show up in the value list. I don't store the name of the school in the Schools table because I only want the name of a place to be stored once in the database. For example, I can have both a school and a rec center with the same name and location. I'll have one record in the "School" table and one record in the "Rec Center" table, but both will pull their name and mailing address from a single record in the "Places" table. That way, if the name changes (and it occasionally does) I simply change the name once in the Places table and it is updated for both the School and the Rec Center records at the same time.
              • 4. Re: Value List From Values in a Related Table?

                     Ok, I see. In that case PhilModJunk's suggestions are spot on.

                     Let us know if you can't figure it out.