10 Replies Latest reply on Jan 18, 2010 2:21 PM by philmodjunk

    Linking two pre-existing tables via a drop-down menu



      Linking two pre-existing tables via a drop-down menu


      I'm constructing a database for our counseling agency that has two tables: Clients and Therapists.  As I create a record for a client, I want to be able to link them to their associated therapist, and would like to be able to do this utilizing some sort of drop down list of currently existing therapists.  The difficulty that I'm encountering at this point is that when I place the "Therapist" field in the portal (based on a value list that pulls from a name calculation field in Therapists), I can't access it unless "Allow creation of records in this table via this relationship" is checked, but when it is I create a new Therapist record every time, as opposed to just selecting one that's already there.


      Additionally, it would be FABULOUS if I could have a system that allowed the user to type in the first letter of the therapist's name and have it scroll down to that area (on the drop down menu or whatever), as we have quite a few therapists and the list is rather long to scroll through by hand. 


      Help! And thanks!

        • 1. Re: Linking two pre-existing tables via a drop-down menu

          The linked therapist field you want needs to reside in the client table, not the therapist table. Once created and placed in the client layout, attach a drop-down list to the field. For the value list, create a value list whose value list items are based on the content of a field: the therapist name field of the therapist table. Make sure to click the "Auto-complete using value list" check-box option.


          That's it! 

          • 2. Re: Linking two pre-existing tables via a drop-down menu
            I'm not sure I understand; I tried doing that and nothing happened.  I can see how the suggestion of clicking the "Auto-complete using value list" check-box option will help in terms of auto-filling in a name, but this doesn't answer my question of how to link a client to a therapist.  
            Here's some more info on what I've done.  The Therapist value list is drawn from a name calculation (last name, comma-space, first name) that resides in the Therapist table.  The relationship itself is based on the unique Therapist serial (so the one-to-many is one therapist to many clients).  I have unchecked the allow creation of records in the relationship area (because I don't want to create a new Therapist record every time I try to link a client to a pre-existing therapist).
            I'm actually someone with an intermediate level of Filemaker experience, but haven't run across this sort of thing before.
            What am I missing? 
            • 3. Re: Linking two pre-existing tables via a drop-down menu

              Value lists can display names (as in the case of therapists) yet return a different value (therapist key value, in this case). If you make your list a pop-up menu, the name will appear except the key value will be stored.


              To make a value list like this, make it based on the therapist key field and select to display a second field, the name field. Check that only the second field displays and voila!


              Because you are selecting the therapist key field, that is the field that needs to be stored in the client table. That is the link between your two tables. 

              • 4. Re: Linking two pre-existing tables via a drop-down menu

                That did it!  Thanks!


                Now just one more question.  Somehow in the kerfuffle, the ability to turn on the option for "Auto complete using value list" went down.  I've got the field as a pop-up menu, but it won't allow me to choose that for a drop-down list either. 




                And thanks again... 

                • 5. Re: Linking two pre-existing tables via a drop-down menu

                  Pop-up menus do not allow auto-completion. That is because they do not allow typing like a Drop-down list does.


                  If you need a drop-down menu, you will need to do a bit of extra work. You will need to make a relationship between the employees and the managers using the manager key field. Then, you will need to add the manager name field to the employee layout. The field needs to be of the same size or larger then the manager key field and it needs to be completely on top of the manager key field. Make the name field opaque, so that the manager key field is hidden. Make the name field inaccessible in all window modes - this is strictly a display field.


                  As the the manager key field, make it a drop-down list instead of a pop-up menu and make sure it is accessible in all window modes.


                  That should finish it! 

                  • 6. Re: Linking two pre-existing tables via a drop-down menu

                    Ok, cool, I'll get to work on that...


                    Thanks again for all your help!



                    • 7. Re: Linking two pre-existing tables via a drop-down menu

                      Using the example provided, how would you link multiple therapists name and contact fields to a single patient record.


                      At present, I've built a 1-to-1 linking relationship that is functioning properly.


                      1. "Therapist" Field in Patient Table created.
                      2. "Therapist" Field in Therapist Table created.
                      3. Two Fields have been matched.
                      4. Values List created using Therapist Table Values
                      5. Created Pop-Up Menu for "Therapist" Field in Patient Layout using that Value List.


                      Result:  In Patient Layout, when Therapist Name is selected from Pop-Up, all Therapist Contact information from Therapist Table is visible through portal.


                      Next Layer:  How would you recommend the portal display multiple Therapists and their contact information directly from the Therapist Table.



                      Thanks in advance, StilLearning. 

                      • 8. Re: Linking two pre-existing tables via a drop-down menu

                        In this case, you have a many to many relationship. Many Therapist records could be related to many Patient Records.


                        The classic approach here is to create a third table to function as a join table linking patients to therapists.


                        Patients:: PatientID = Patient_Therapists:: PatientID


                        Therapists::TherapistID = Patient_Therapists::TherapistID



                        • 9. Re: Linking two pre-existing tables via a drop-down menu

                          First thank you so much for the reply.


                          Second, my apologies for taking so long to follow-up - I had ticked the "Email me when someone replies" box, but I never received the email.


                          In any case, I've created the joining table with the two fields and (I believe) linked them as instructed but having trouble creating the portal.


                          If I have populated the Therapists Table with their contact information, and the Patient Table is the primary interface, how do I create the portal that will allow me to use a pulldown menu to link a Patient record with the (multiple) existing Therapist records and delete that relationship as necessary if Patient stops seeing one of the Therapists?


                          [Of note, I'd type out all the things I've tried so far but as they haven't worked, that would likely just confuse the situation] 


                          Thank you so much in advance.....



                          Sincerely, StilLearning.

                          • 10. Re: Linking two pre-existing tables via a drop-down menu

                            The trick here is to base your portal on the Join table, but then add fields from the third table to supply additional data on the records being linked via the portal.


                            On your Patients layout you can add a portal based on the Join table, let's call it Therapists_Patients.


                            Place the Therapists_Patients::TherapistID field in this portal and add whatever name fields such as the Therapist's name field to this same portal.

                            Define a value list of Therapist ID's in this field, (Make it a two column field with the Therapist's name in the second column). Now you can simply select a therapist from the drop down in the portal to assign that therapist to the current patient record.


                            You can do the exact mirror image of this on a Therapist layout in order to assign patients to them.