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.