1 2 Previous Next 25 Replies Latest reply on Nov 27, 2013 9:41 AM by JuliaOlsen

    Value List and Associated Field

    JuliaOlsen

      Title

      Value List and Associated Field

      Post

           I have a database connecting (among other things) teachers with their districts and schools.

           I need to track the teachers each year they are in our program and beyond, so I have a table called "Education Employment History"

           Education Employment History Needs to have a record for each teacher for each year with the fields year, district, school

           I have a table called "All School Data" which lists district, school, address, city, zip, phone, principal, email).  There is a record number called "SchoolID" which is distinct for each record (of course).

           These two tables are related by "District" and I have created a value list where, in my form,  in the "district" field, I can choose the appropriate district from a drop down.  I then can choose from only the schools in that district to populate the "school" field.

           That part works well.

           The problem is, I want to display the school address, phone number associated with a given school  but I am consistently getting only the first record from the schools in the district.   So I might select the school from record 3, but I’m getting the address, etc from School record 1. 

            

           District -------> School Record 1: school, address, principal, etc.

           School Record 2: school, address, principal, etc.

                                   School Record 3: school, address, principal, etc.

            

           Any suggestions are appreciated.  Have uploaded a graphic of the two tables relationship. They are connected by district. I did try using the school ID but that did now allow me to do the value list as I needed.

      Screen_Shot_2013-11-08_at_10.27.38_AM.png

        • 1. Re: Value List and Associated Field
          philmodjunk

               In Manage | Database | relationships, make a new Tutorial: What are Table Occurrences? of All_School_Data by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box such as All_School_Data|SchoolID.

               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

               Add it to your relationships like this:

               All_School_Data|SchoolID::SchoolID = Education Employment History::SchoolMatchID

               Now you can show the correct data for the selected school by adding fields from All_School_Data|SchoolID to your Education Employment History layout.

          • 2. Re: Value List and Associated Field
            JuliaOlsen

                 Still having problems.  I think I am now confused with the value lists but perhaps I don't have relationships correct, yet. 

                 I created the additional table reference as you directed.

                 I only allow creation of records in Education Employment History so I think no relationships should be able to change the data in "All_School_Data", that is, I do not allow creation of records from Education Employment  History to either All_School_Data:SchoolID or All_School_Data

                 I have a value list to use values from All_School_Data::District to select the district

                 I formerly had a value list to select the school using  values from field All School Data::School including only related values from education Employment History

                 This consistently returned the first record's data no matter what the School I chose. I have a screenshot here.

                 --

                 Now, without any other changes, the first thing I see happening is that when I choose a school not at the top of the list, it overwrites the school name at the top of the list in Education Employment History, and still returns that address, rather than the actual school.

                  

                 Thought I understood FMP12 basics and a bit more but this is getting me more and more confused.  I have been struggling with this a week or so before posting, so I may be missing something very simple.

            • 3. Re: Value List and Associated Field
              philmodjunk

                   The value list for selecting a school should list values from all school data, but the first field should list schoolID and the second field should list the School name. The SchoolIDMatch field is the field that you should set up with this value list format.

                   

                        I only allow creation of records in Education Employment History so I think no relationships should be able to change the data in "All_School_Data", that is, I do not allow creation of records from Education Employment  History to either All_School_Data:SchoolID or All_School_Data

                   For the most part, "allow creation..." only makes it possible to create new records by entering data in the bottom blank "add row" of a portal to the table occurrence where you specified "allow creation..." in the relationship on which you based the portal. You can still create records in both tables whether or not this option is enabled via other means.

              • 4. Re: Value List and Associated Field
                JuliaOlsen

                     Thank you for everything so far...

                     I have set up the value list for selecting a school, and in use it looks like the image below. 

                     It is obviously selecting the correct SchoolID and School name, but I am still unable to get a correct address returned.  Depending on which table I try to retrieve it from, it is still either the first address in the list or blank.

                     Generally we need to enter data into fields (such as address), rather than retrieve it, so this is a new process for me. I'm feeling quite dense.

                      

                • 5. Re: Value List and Associated Field
                  philmodjunk

                       In the value list settings, you can choose to hide the first field value if you don't want to see the ID numbers. This assumes that all school names are unique.

                       The address fields must come from the new table occurrence that I described. You may want to upload a screen shot of manage | database | relationships to show what you currently have if you can't get that to work.

                  • 6. Re: Value List and Associated Field
                    JuliaOlsen

                         Ok I'm going to upload a few screenshots...

                         1) Even when I specify show only 2nd field (hide first field) I get the ID number rather than the name. 

                         2)I can, however, get the school name from All_School_Data::School by using  the School field rather than the SchoolID field

                    • 7. Re: Value List and Associated Field
                      philmodjunk

                           1) That is correct. But you can either format the field as a pop up menu instead of a drop down list or you can add the related school name field to show the name after you have selected a school.

                           2) But you don't want it from all_school_Data as that will only show the first school from the same district. Add the field from the new table occurrence to show the matching school name.

                      • 8. Re: Value List and Associated Field
                        JuliaOlsen

                             Here are the relationships ---

                             Education Employment History is connected to Teacher Information, so that each record is connected with one teacher. That's working fine.

                             Do I have the fields correct in Education Employment History?

                             Content, Grade Level and Teaching Status are entered yearly so aren't part of the problem

                             Year_ID is the record number for each record in this table - for each year, for each teacher, there will be a record of where they were teaching.

                        • 9. Re: Value List and Associated Field
                          JuliaOlsen

                          2) But you don't want it from all_school_Data as that will only show the first school from the same district. Add the field from the new table occurrence to show the matching school name.

                               When I change the value list to get values from AllSchoolData|SchoolID rather than from AllSchoolData, I get no values at all....

                               So as imaged below, returns a list of schools from the district (although not the correct address, etc) . From AllSchoolData|SchoolID does not. 

                               I'm still taking the District from the original table, correct ?  (AllSchoolData::District)

                                

                               1)Thanks for the mention of popup vs drop down

                          • 10. Re: Value List and Associated Field
                            philmodjunk

                                 2) I'm not suggesting that you change the value list, keep it as you have originally defined it. What I am suggesting is that you add the school name field from AllSchoolData|SchoolID to your layout. When you select a School in the drop down, this field on your layout then shows the selected school. Do this also for the other fields that should show data about the school and you'll have this up and working.

                                 Once you have the correct data showing on your layout, we can take a crack at improving the look of your drop down list field.

                            • 11. Re: Value List and Associated Field
                              JuliaOlsen

                                   Have gone step by step through your instructions above but I am afraid I am still missing something. So I am stuck on choosing the school from All_School_Data and can't get past that to any information from All_School_Data|SchoolID showing up.  (However, now that I am struggling with this I sure see inconsistencies in my field and table naming!)

                              • 12. Re: Value List and Associated Field
                                philmodjunk

                                     Compare this demo file to your own: https://dl.dropboxusercontent.com/u/78737945/AssociatedValueListDemo.fmp12

                                     I've pared down the details to just the crucial details. I only have a school name appearing when you select from the drop down, but the same method works to show any fields from the table of schools that I need to show. There are also any number of little tricks that can be used to make this layout look and function better, but I've also avoided them to keep the function of the various parts of this design as easy to see and understand as possible.

                                • 13. Re: Value List and Associated Field
                                  JuliaOlsen

                                       Got it! Thanks so much.  The demo was really helpful!  Now for the next steps ...

                                  • 14. Re: Value List and Associated Field
                                    philmodjunk

                                         And what would you like those "next steps" to be?

                                    1 2 Previous Next