This is a public Forum  publicRSS

Forum post

    jkersey
    Trying to get info from one table into another, based off...Answered
    Forum post posted March 23, 2009 by jkersey, last edited February 9, 2012 
    306 Views, 5 Comments
    Title:
    Trying to get info from one table into another, based off the selection made from a drop down box
    Post:

    Hello everyone,

    I am pretty new to FMPro 9 and databases in general, so please forgive any basic questions that I might ask.

     

    I have a table in my database that stores employee information, such as their name, phone #, fax #, email, etc.  I have also created a value list that contains the names of these employees.  What I am trying to do in another table is select the employee's name from value list in a drop down box, and have other fields in this same table automatically populatewith info such as their phone # and email.  

     

    I believe I am trying to do a lookup and have selected in the auto-enter options of the fields I want auto-populated to have them auto-enter based on the looked-up value from the fields in the employee's table that stores the phone #, email, and so on.  However, it does not seem to be working.  I think I am probably just not doing this correctly, and would appreciate any help you could give me.

     

    Thank you very much for your time.

    Best Answer

    PhilModJunk

    You should have a relationship linking your two tables that uses your drop down field's relationship as the key field matching the employee name field to link your two tables. Your field definitions then have to refer to the related employee info table. Now, when you select a value in your drop down, Filemaker will physically copy the data from your employee table into your 2nd table. If you think this is what you've done, but it's not working, you'll need to carefully describe your tables, their relationship and the field's that make up the relationship so that someone here can help you spot the problem.

     

    Two design comments:

     

    1. Since your employee's contact information is subject to change, you may not want to physically copy the information from one table to the other. If you want changes in the employee info table to automatically update in your layouts, you should not use the look up feature.
    2. People change their names! If Jane Doe comes to you and informs you that she just got married and now will be called Jane Smith, changing her name can disconnect her record in the employee info table. You might want to create employee id numbers in an additional field and use it in your relational links. (You can make your drop down have two columns: col 1 shows the ID and col 2 shows the employee name.)

    Answer

     

    • PhilModJunk

      You should have a relationship linking your two tables that uses your drop down field's relationship as the key field matching the employee name field to link your two tables. Your field definitions then have to refer to the related employee info table. Now, when you select a value in your drop down, Filemaker will physically copy the data from your employee table into your 2nd table. If you think this is what you've done, but it's not working, you'll need to carefully describe your tables, their relationship and the field's that make up the relationship so that someone here can help you spot the problem.

       

      Two design comments:

       

      1. Since your employee's contact information is subject to change, you may not want to physically copy the information from one table to the other. If you want changes in the employee info table to automatically update in your layouts, you should not use the look up feature.
      2. People change their names! If Jane Doe comes to you and informs you that she just got married and now will be called Jane Smith, changing her name can disconnect her record in the employee info table. You might want to create employee id numbers in an additional field and use it in your relational links. (You can make your drop down have two columns: col 1 shows the ID and col 2 shows the employee name.)
    • jkersey

      Thank you PhilModJunk!   It looks like my problem was that my database had no idea what information I was trying to look up.  I needed to have the employee name as my key field, but I was using the phone number as the key when trying to auto-populate the phone number...

       

      Thank you also for the suggestion of using employee ID numbers--that would simplify things should one of the women get married.

       

      Out of curiosity, you mentioned that it might be better to not physically copy the info from one table to another.  Is there another way to find and display the info I want without using the lookup to copy the info?

       

      Thanks!

    • PhilModJunk

      "Is there another way to find and display the info I want without using the lookup to copy the info?"

       

      Yes,

       

      Option 1: Display the related info in a portal. This is easy to set up but may not permit the format you want--especially if it is a printed report.

       

      Option 2: Simply place the fields from your employee data table the same layout as the fields from your other table. Because you've defined a relationship linking the two fields, you'll be able to see and (if you choose to permit it), data from matching records in the two fields. This option works if the table selected for your layout is not your employee data table and your relationship matches one record in employee data to one or more records in your other table.

    • jkersey

      Ah, I think I am actually doing option #2 now--if you are talking about selecting the field in the table I am working with to be the field of another table (i.e. the field I am selecting is employees::phone number).  I think I might have been mistakenly calling it looking up, but I did not realize that it actually copied the data from one table into another (although I just typed "lookup" into the FMPro help and it does say just that....

       

      Anyway, thank you again for the prompt and extremely helpful assistance.  I appreciate it!

       

       

    • PhilModJunk
      "Look Up" is an option you can select as part of a field's definition.