14 Replies Latest reply on Jul 21, 2011 4:51 PM by philmodjunk

    Multiple Teachers to One School (using teacher table)

    brianquillin

      Title

      Multiple Teachers to One School (using teacher table)

      Post

      Is there a way to attach/assign a teacher record to an existing school record from the TEACHER record form/table?

      I understand how to assign them from the school (company) form/table via a portal but I'd like to reverse this process.

      Thanks in advance.

      BQ

        • 1. Re: Multiple Teachers to One School (using teacher table)
          philmodjunk

          Presumably, you have SchoolID and SchoolName fields in your School Table.

          Define a SchoolID field if you have not already done so in your teachers table and format it as a value list of SchoolIDs in field 1 and SchoolNames in field two from your School table. Then you can set up Teachers::SchoolID as drop down list or pop up menu with this list.

          Then the relationship:

          Teachers::SchoolID = Schools::SchoolId

          Will give you access to all the data from the selected school record from your current teacher record.

          • 2. Re: Multiple Teachers to One School (using teacher table)
            brianquillin

            Thanks for the quick response. 

            Since the edit box field is set to Teachers::School_ID, the field shows the school serial number instead of the name after it has been selected.

            What is the fastest way to fix this?  (I do have it set for the list to only show values from the 2nd list, which are names).

            • 3. Re: Multiple Teachers to One School (using teacher table)
              philmodjunk

              Make it a pop up menu and the name will appear in this field when you exit the pop up.

              Or put the school name field from Schools directly on your layout if you prefer to use the drop down.

              You can even place this field (after setting behavior to deny access to the field in the inspector) directly on top of the drop down field formatted with an opaque fill color/pattern. When you click on it, the drop down list in the field behind it deploys, but when you exit, this ID field disappears back behind this related name field from the Schools table.

              PS. with show only 2nd field specified you should make sure that no two schools have exactly the same name or the 2nd school with that name will not listed in your list. There's a unique values field option you can select to warn the user should they enter a second school record with a name that is not unique.

              • 4. Re: Multiple Teachers to One School (using teacher table)
                brianquillin

                I chose the pop up menu view which was the easiest fix.  It is interesting to me that the drop down menu doesn't respond in the same way.

                Thanks for the warning about the school names.  I'll remedy that now.

                • 5. Re: Multiple Teachers to One School (using teacher table)
                  brianquillin

                  Getting more specific (perhaps should begin new thread with this one - your call):

                  I am tracking test scores.  At the top of the RESULTS form page, I would like to have a pop up menu with the School Value List I already have.

                  THEN, I'd like to have second pop up menu below that showing ONLY the teachers that are referenced to the selected school.

                  I thought I had it at one point but no such luck (that's usually the way it goes for me).

                  Thanks in advance...

                  • 6. Re: Multiple Teachers to One School (using teacher table)
                    philmodjunk

                    That's called a conditional value list where the selection in one value lists limits the choices listed in the second.

                    Forum Tutorial: Custom Value List?

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

                    Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

                    • 7. Re: Multiple Teachers to One School (using teacher table)
                      brianquillin

                      I've struggled with so many settings and relationships since your last post that I've gone in the same loop at least 100 times.

                      Sorry to have to follow up with this.

                      I have three TOs - Schools, Teachers, & Results

                      I currently have (which is obviously incorrect):

                      Teachers:teacher_ID -< Results:teacher_ID

                      Results:result_ID -< Teacher:result_ID (couldn't decide which one would work)

                      Results:result_ID -< Schools3:result_ID 

                      ***At one point, about an hour ago, I actually saw a FULL list of teacher names in the popup menu; I could NOT modify the field so I went back to the Relationships (I changed something and they were gone).

                      • 8. Re: Multiple Teachers to One School (using teacher table)
                        philmodjunk

                        Helping someone with the design of their database via this forum is often like peeking through the front door of a house and trying to determine the wall paper pattern used in the bathroom. Wink

                        Please keep that comment in mind as I know very little about the actual structure of your database and how you plan to use it.

                        What is the purpose of your Results Table?

                        Do you have many Teachers for one Result or many results for one teacher?

                        Define a schoolID field in Results if you do not already have one. Put this field on your results page and format it like you did before so that you can select a School by ID in this field. This will be the "filter" field that controls which teachers will appear in the conditional value list.

                        Define a TeacherID field in Results if you have not already done so and add it below the school Id field. This field will be formatted as your conditional value list.

                        To get our conditional value list, we need to add a relationship from results to Teachers that uses the SchoolID field. Chances are that you already have a relationship between these two tables and that you now need to add a second, different relationship between the two and that's the assumption that I am making here.

                        In Manage | Database | Relationships, Select Teachers, then click the button with two green plus signs. This will give you a new occurrence of Teachers called Teachers 2. Double Click it and change its name to TeachersBySchool. Link it to Results to get this relationship:

                        Results::SchoolID = TeachersBySchool::SchoolID

                        Now set up your value list to list the TeacherID and Teacher Name from TeachersBySchool, include only related values starting from Results.

                        Use this value list for your TeacherID field on the results layout and you now have a conditional value list of teachers controlled by what school you select in the field above/next to it on your layout.

                        This probably doesn't finish the project for you as what you want to do with this TeacherID value that you've selected may modify what we've set up here. That's why I started this post by asking some questions about your database.

                        • 9. Re: Multiple Teachers to One School (using teacher table)
                          brianquillin

                          I'll begin working through these instructions immediately. (thanks)

                          I plan to have many results for one teacher.  

                          Can you provide a helpful hint (or trick in answering the one to many questions).  Those tend to cause most of my confusion.

                          I'll report back with my progress. 

                          (fyi, my bathroom walls are painted (no wallpaper) ;)

                          • 10. Re: Multiple Teachers to One School (using teacher table)
                            philmodjunk

                            I plan to have many results for one teacher. 

                            That answers the one to many questions here. You have one school for many teachers and one teacher for many results.

                            That is sometimes charted as Schools----<Teachers-----<Results

                            Spotting the one to many relationships will tell you which tables will have the primary key (auto-entered serial number) and which tables will use a number field as the foreign key field to link to another table.

                            You should have these fields as auto-entered serial number fields to serve as Primary keys:

                            Schools::SchoolID
                            Teachers::TeacherID

                            Adding in what we need for the conditional value list, we get this:

                            Schools---<Teachers----<Results>----TeachersBySchool

                            Schools::SchoolID = Teachers::SchoolID
                            Teachers::TeacherID = Results::TeacherID
                            Results::SchoolID = TeachersBySchool::SchoolID

                            And remember that Teachers and TeachersBySchool are two different table occurrences of the same data source table which is also named Teachers.

                            • 11. Re: Multiple Teachers to One School (using teacher table)
                              brianquillin

                              As always, very helpful. thanks...

                              I was on track with most of that from the start.  There is really just one (or two) more component(s) that I will add to this solution.  Your insight would be greatly appreciated on this as well.

                              I am going to track PRE-test and POST-test information.  I also assume that over the course of the next several years, they will alter/modify their tests and have different versions of each one.

                              Therefore, I have planned on adding a PRE-test Table and a separate one for POST-tests (each one having a primary key to track versions of those tests)

                              Am I correct in thinking that these would be linked to my db via the Results Table?

                              My current thinking is that ONE Test will have MANY results.

                              Would that be PRE-test----<Results>----POST-test?

                              • 12. Re: Multiple Teachers to One School (using teacher table)
                                philmodjunk

                                What data do you plan to store the actual tests?

                                You don't have to use two tables for these tests. You can actually store pre and post test records in the same table, but use a "test type" field to identify them as "pre" or "post".

                                You can use two fields in Results to link to different records in your test table to link to both a pre and post test for each result.

                                And a portal to results from a Tests layout can be used to see and edit all the result records for a given test.

                                • 13. Re: Multiple Teachers to One School (using teacher table)
                                  brianquillin

                                  Thanks again. You've probably saved me another few laps around the relationship tab.  

                                  I plan to track very little about the actual tests (no content in field form, etc).  Mainly just the test_ID (versions) and the date it was created/released.

                                  If anything, I plan to try to allow them to import and store copies of the test files (attach documents to the records of the test versions)

                                  BTW, I have the conditional value list installed and working perfectly.  Much appreciated.

                                  May have a few more roadblocks but I am beginning to see a hint a light.

                                  • 14. Re: Multiple Teachers to One School (using teacher table)
                                    philmodjunk

                                    As you probably already know, you can add a container field to your tests table to store actual copies of the test document. There are options and issues when it comes to doing that so read up on the subject in help before you try it and pay especially close attention to the differences between storing a file in a container "by reference" and not doing so.