11 Replies Latest reply on May 23, 2009 1:57 PM by LaRetta_1

    Trying to learn about join tables

    Amy

      Title

      Trying to learn about join tables

      Post

      Hi all, I'm building my first FileMaker database, and I need some help with a join table.

       

      I'm trying to track the process of assigning students to mentors. Students are in the program for one year, and mentors continue to serve from year to year. Mentors frequently have more than one student; students rarely have more than one mentor (but it does happen). I have a table containing students (ID number, name, year, etc.) and a table containing mentors (ID number, name, etc.). I have also created a join table called Placements that has the student ID number and the mentor ID number as foreign keys. It also has a field for the year, as I am interested in looking at all the placements for a given year.

       

      I'm not sure how to set up a layout in the context of the join table so that I can reasonably assign students to mentors. Once I get that figured out, from a layout showing a student's record, I'd like to be able to see who his/her mentor(s) is/are. And from a layout showing a mentor's record, I'd like to have a portal listing all of his/her students.

       

      In the back of my mind, several months down the road I'm hoping to expand the functionality of the database so that I can also track potential matches between students and mentors in addition to confirmed matches.  

       

      Any tips or advice to help me make progress? I've already tried reading "The Missing Manual," but I'm still stumped -- and feeling nostalgia for good television that's been cancelled.

       

      -Amy 

        • 1. Re: Trying to learn about join tables
          comment_1
            

          You can add mentors to a student in several ways - I'll outline two of those:

          Manually:
          1. Allow the relationship between Students and Placements to automatically create records on the Placements side.


          2. Define a value list of Mentors (showing values from Mentors::MentorID and also showing Mentors::Name).


          3. Put the field Placements::MentorID in the portal to Placements on the Students layout, and format it as a drop-down/pop-up using the Mentors value list.

           

           

          Selecting a mentor in the last portal row will add that mentor to the currently viewed student.


          Scripted:
          1. Define a new relationship between Students and a new occurrence of the Mentors table, using the x relational operator. Name this new occurrence of Mentors as "AllMentors".


          2. Place a portal to AllMentors on a layout of Students.


          3. Put a button in the above portal that runs a script to create a new record in Placements for the selected mentor and the currently viewed student.



          • 2. Re: Trying to learn about join tables
            Amy
              

            Thanks. That helped a lot. I did the "manual" option, and it worked. Adding a mentor from a student's record works perfectly.

             

            Follow-up question. When I tried to also do the same thing but the other way around (assigning a student from a mentor layout) it didn't work. There's some problem with my Value List of students, I suspect.

             

            When I'm in a mentor's record, when I click on the list in the portal from the mentor's record to the Placements table, only one student shows up in the pop-up menu. When I view the record of a mentor that I've already assigned a student to, only the student's ID number shows up in the portal. 

             

            I've tossed three screenshots up at: http://tinyurl.com/p2o9tv

            (In the database, "Mentors" are called "Research Groups") 

             

            First two screenshots show the options I've chosen when creating the value list. The third one shows how I've set up the field in the portal.

            • 3. Re: Trying to learn about join tables
              comment_1
                

              To do the same thing from the Mentors table (i.e. assign students to a mentor), you need to "mirror" what you did earlier, i.e.:

              1. Allow the relationship between Mentors and Placements to automatically create records on the Placements side.

              2. Define a value list of Students (showing values from Students::StudentID and also showing Students::Name).

              3. Put the field Placements::StudentID (instead of Placements::MentorID) in the portal to Placements on the Mentors layout, and format it as a drop-down/pop-up using the Students value list.

              Now select the student to assign to the currently viewed mentor.



              • 4. Re: Trying to learn about join tables
                Amy
                   Hrm.... I'm pretty sure that's exactly what I did. But when I click on the drop-down list from the Mentor side of things, only one student (the one with ID #1) appears on the list. When I browse the record of a mentor to whom I've assigned a student, I only see the student's ID number, not the student's name (except for the student with ID #1 -- his name shows up).
                • 5. Re: Trying to learn about join tables
                  Amy
                    

                  The plot thickens... I think I don't understand value lists.

                   

                  The Name field is a calculation Given Names & " " & Last name

                   

                  When I use the Name field in my Value List of students, it doesn't work. But if I use the Last name, then it does work. 

                  • 6. Re: Trying to learn about join tables
                    comment_1
                       It's difficult to trouble-shoot blind, but the obvious question would be: do you have other students in the Students table, and do they have names (you defined the value list to show only the name, so duplicate/empty names will not show).
                    • 7. Re: Trying to learn about join tables
                      Amy
                        

                      Yes, there are 76 students in the Students table, and they all have names. Their names show up when I browse the students' records in a layout from the Students tables. The Names field will correctly show up in a portal on a layout from the Counselors table that shows the names of all the students in a counselor group.

                       

                      When I took my Value List of students and switched the second field from Name to Last Name, then everything worked: all 76 students' last names showed up in my pop-up list. When I switched it back from Last Name to Name, it stopped working again, and the only student who showed up in my pop-up list was the student with ID#1.

                      • 8. Re: Trying to learn about join tables
                        LaRetta_1
                           Although the fullName calculation may display the names, are you sure the Name calculatio data type is set to text and not (default) number?  If it is set to number, your value list will improperly display only the first entry.
                        • 9. Re: Trying to learn about join tables
                          Amy
                            

                          Wow! Yes! Perfect!

                           

                          It's working wonderfully now! Thanks so much! 

                          • 10. Re: Trying to learn about join tables
                            comment_1
                               What a bull's eye from a shot in the dark!
                            • 11. Re: Trying to learn about join tables
                              LaRetta_1
                                

                              Yeah, you do all the work to get them there and I slide in with a small tweak and get the prize!  Now THAT'S what I call bull's eye! :smileyhappy:

                               

                              Actually, I've done this before - left a field as number then only had it produce one result.  Sometimes being an idiot has it's benefits if one learns from it ... I know!