2 Replies Latest reply on Feb 20, 2014 8:18 AM by AnnetteRoman

    Autofill or Lookup for two fields when entering info in a third field

    AnnetteRoman

      Title

      Autofill or Lookup for two fields when entering info in a third field

      Post

           I'm stumped. I have a database that is dealing with many-to-many relationships. In my Personnel table, I have employeeID# as a key field. In my Projects table, I have a projectNUMBER as the key field. I've created a JoinTable to help bridge the many-to-many relationship that holds both of these key fields. 

           I am trying to create a way where if I select an employeeID# from a drop down value list on the Projects table it will populate in two separate fields the first name (field 1) and last name (field 2) of the employee that matches the employeeID#. To make matters difficult I have at least three people involved per project. So I'd need this to work for three different employeeID#s per projectNUMBER. 

           On my Personnel table, I want to show which projects an employee has been involved in. I currently have a portal showing my JoinTable information which of course right now isn't showing anything. 

           Please help. 

        • 1. Re: Autofill or Lookup for two fields when entering info in a third field
          philmodjunk

               Your relationships as I understand them:

               Personnel----<Join>------Projects

               Personnel::employeeID#= Join::employeeID#
               Projects::ProjectNumber = Join::ProjectNumber

               If you place a portal to Join on your Projects layout, with "allow creation of records..." enabled for Join in the Projects to Join relationship, you can place Join::EmployeeID# in your portal row formatted with a drop down list or pop up menu of ID#'s and employee names. You can then place the name fields from Personnel in the same portal row. When you select a person by selecting or entering their employeeID#, this creates a new record in Join and the value of Projects::PRojectNumer is copied automatically into ProjectNumber of the new Join record. And you'll find that the name fields or any other fields from Personnel that you choose to add to the portal row will display data from the selected personnel record.

          • 2. Re: Autofill or Lookup for two fields when entering info in a third field
            AnnetteRoman

                 Thanks for the help! It worked.