1 2 Previous Next 16 Replies Latest reply on Jan 23, 2009 11:51 AM by shelleym25

    join tables

    shelleym25

      Title

      join tables

      Post

      Hi - I have two tables with a many to many relationship which I have resolved with a join table.  Now I am wondering how to get around manually inputting the IDs from each of these tables into the join table to establish the relationship.  Is it possible to automate this?  Any help would be much appreciated!

       

      Thanks 

        • 1. Re: join tables
          Jens Teich
             The default way to do this is (if both data tables don't have to many rows to be displayed in a value list):

          * Create value lists of all members of both data tables with two columns each: ID + name

          * Create a portal in both tables showing the join table

          * Set both relationhips to allow creation of new join table records

          * Fill the missing ID of the other table with the value list of this table

          Jens




          • 2. Re: join tables
            planner_1
              

            I'm New to FMP 9 and databases (in general).  I believe I get the concept of creating a join table when you want to create a many to may relationship.  But ,I'm still missing something (not sure what).

             

            I have created the following:

             

            • Process (table)
            • Locations (table)
            Now many processes can occur in/at many locations and many locations can support many processes.
             
            So, I created a "dependency" (table) in an attempt to join the two.
             
            The Dependency table includes:
            • DependencyID
            • ProcessID
            • LocationID
             
            Now, when I'm on the Process input forum, I'd like to be able to build a Process profile that shows the Process and all of it's related locations (the locations table has the following fields: LocationID, LocationName, Address, City, State, Zip, County, Country.
             
            Did I set-up the relationship properly?  What table would show me the join between Process and all of it's locations?  

             

             

             

            • 3. Re: join tables
              Jens Teich
                 The definitions of the relationships are

              * Process :: ID <-> Dependency :: ProcessID
              * Location :: ID <-> Dependency :: LocationID

              [hint] I added some spaces in 'table :: field' because the forum software tries to find smileys where I don't want them

              Double click the relationships and check 'allow creation of records via this rel...' on the side of table Dependency.

              Create a portal on layout Process showing records of Dependencies with field LocationID and others.
              Create a portal on layout Location showing records of Dependencies with field ProcessID and others.

              Test creation of new Dependency records via both portals.

              Ok so far for now. The value lists are still missing.

              Jens







              • 4. Re: join tables
                planner_1
                   That works, Thanks!
                • 5. Re: join tables
                  shelleym25
                     This works beautifully!!!!!  I simply cannot thank you enough, I've been pulling out my hair for days now.  Thank you so much!!!
                  • 6. Re: join tables
                    shelleym25
                      

                    Ok, now I have another question.  What happens when I have hundreds of members in both of my data tables?  The value lists will be huge!  Is there a way to enter the first letter of the members name and just pull up those names that start with that letter?  Or maybe something else? 

                     

                    Thank you

                    • 7. Re: join tables
                      TSGal

                      shelleym25:

                       

                      Thank you for your post.

                       

                      If you have a value list, typing the first few letters will drop you down to the first entry with those characters.  For example, if you type "S", the drop down may highlight "Sandy".  If you type "St", it may highlight "Steve".  Does this help?

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: join tables
                        shelleym25
                          

                        Hi - Thanks for responding so quickly. 

                         

                        This is not working for me.  My value list uses member IDs and names, IDs being the first field and names the second field to be displayed.  I think that because of this, typing in the first letter of a name does not work.  I am sorting the values based on the first field, should I change this?  Or should I change the value list to use names as the first field?  I hope this makes sense!

                         

                        Thanks

                        • 9. Re: join tables
                          TSGal

                          shelleym25:

                           

                          This is what I have done...

                           

                          I created a table (TEST) that includes Last Name, First Name, and Full Name (Calculation of two fields - Last Name & ", " & First Name).  I entered 50 records.

                           

                          I switched to another table, went into Layout Mode, selected a text field (called "Text" ), and set the field to use a drop-down value list based upon Full Name from the "TEST" table.  When I go to Browse Mode, click this field, the drop down list appears in alphabetical order based upon the two fields.  When I type in "R", the first (and only in this case) entry for "R" is highlighted.  If I type "E", it jumps to the first entry for "E".

                           

                          Let me know what you are doing differently.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: join tables
                            shelleym25
                              

                            TSGal:

                             

                            First of all, thanks for going to so much trouble for me!  What I have is a database with essentially 3 tables; 2 data tables (contacts & properties) and one join table.  I wanted an easy way to establish the relationship between the two tables and wrote a message for help.  This is the answer I got and this is exactly what I am doing:      

                             

                            The default way to do this is (if both data tables don't have to many rows to be displayed in a value list):

                            * Create value lists of all members of both data tables with two columns each: ID + name

                            * Create a portal in both tables showing the join table

                            * Set both relationhips to allow creation of new join table records

                            * Fill the missing ID of the other table with the value list of this table

                             

                            So now when I change the value lists to use a name field rather than an ID, I don't get the relationship between the two tables that I need.  Any advice?

                             

                            Thanks again

                            Shelley




                            • 11. Re: join tables
                              TSGal

                              shelleym25:

                               

                              Thank you for the clarification.  The explanation does help.

                               

                              The Name field is not attached to any relationship, so it won't find the ID.  Therefore, you need to use a second table occurrence of your tables with the join table.

                               

                              For this example, let's assume the join table is named "ContactProperties".  It includes the fields:

                               

                              ContactID

                              PropertyID

                              Contact Name

                              Property Name

                               

                              1. Make "Contact Name" your value list drop-down.

                               

                              2. Go into "Manage -> Database" and click on the Relationships tab.  In the bottom left, click on the bottom left icon to add a table.  Select the "Contact" table and you will now see "Contact 2" appear on the screen.  Click the Name field and connect it to the "Contact Name" field in the join table "ContactProperties".

                               

                              3. Click the Fields tab, and double-click the field "ContactID".  You should now be in the Options for ContactID.  Select theoption "Looked-up value", and another dialog box appears.  You want to lookup from related table "Contact 2" and copy value from the ID field.

                               

                              4. Go into Browse.  Click on the Contact Name field, and a listing of names should now appear.  Select one of the names, and the appropriate ID is looked up, placed into Contact ID, and the information now appears in the portal.  Or, you can enter a different Contact ID, and the portal changes.

                               

                              Follow the same steps above with the Property Name and PropertyID fields.

                               

                              Let me know if you run into any difficulty.

                               

                              TSGal

                              FileMaker, Inc. 

                               

                               

                              • 12. Re: join tables
                                shelleym25
                                  

                                TSGal:

                                 

                                I guess I need a little hand holding now!  In step #3  I went to the "ContactProperties" table and did what you said.  Is that right? 

                                 

                                I allowed the creation of new records in the "ContactProperties" table via this new relationship with the "Contact2" table.  Is that right?

                                 

                                Having done all of this, now when I am viewing an existing property and I want to connect it to an existing contact (one that has a relationship with another property which has created a record in my "ContactProperties" table), I click on my drop down arrow and get a list of existing contacts.  However, not all of my contacts are on this list.  I have an Ed Smith and a John Smith, but only John shows up.  Do you know what is causing this?

                                 

                                Thanks again!

                                • 13. Re: join tables
                                  shelleym25
                                    

                                  TSGal:

                                   

                                  Ok I think I figured out my problem.  Now I have another.

                                   

                                  I have two main tables "Contacts" and "Properties".  Each has a portal allowing me to see related contacts when I'm in "Properties" and related properties when I'm in "Contacts".

                                   

                                  When I am in "Properties" and I want to add a contact to it, I click on a drop down field in the portal, choose the name I want and hit enter.  The last name now displays in that field and in a second field in the portal I have a calc field which displays first and last name.  All this looks great.  I now click a button in the portal (next to the calc field) that should take me to the related record in "Contacts".  It does take me to "Contacts" and it does take me to the correct contact record itself, but it does not put the property name in the portal. 

                                   

                                  When I do all of this in "Contacts" and I click the button in the properties portal to take me to the related record, it does take me to the related record and the contact name is in the portal.

                                   

                                  Shouldn't this work both ways?

                                   

                                  Thank you!

                                  • 14. Re: join tables
                                    shelleym25
                                      

                                    TSGal:

                                     

                                    One more thing!!!  I did not solve my problem of missing contacts on my drop down list. 

                                     

                                    Thanks

                                    1 2 Previous Next