3 Replies Latest reply on Apr 26, 2010 8:51 AM by philmodjunk

    Complex lookup

    sayzgor

      Title

      Complex lookup

      Post

      I am working on FMpro10 on windows xp.  I have two tables that are related "current students" and "faculty".  On the student table I have a field "advisor".  What  I would like to do is have "advisor" in the student table = "full name" in the faculty table so that the "name ID" in the student table will fill in the "students" on the faculty table.  I want to be able to have the db figure out who is whom's advisor from the student table and record it in the faculty table.

       

      Any suggestions on how to build this lookup?

        • 1. Re: Complex lookup
          MikeyG79

          you don't have to record it, a portal will work.

           

          make a relation from the advisor.name to student.advisor, and in the portal show the records from that relationship.

          • 2. Re: Complex lookup
            sayzgor

            It should work, but for some reason the relationship is not working.  I have even taken the step of removing all other relationships. 

            • 3. Re: Complex lookup
              philmodjunk

              If you are matching names in your relationship, make sure you have text fields and not a number field that contains text.

               

              If you are matching names in your relationship, don't--unless this is strictly for data search purposes. Names are subject to change and typographical errors. An advisor could get married. I student could undergo a step parent adoption. For either you could enter a name and then later discover that you have mispelled it. When you go to change the name, the relationship breaks. It's far better to link your records by an Serial Number ID field instead of a name. It takes a bit more work to set up so that you don't have to remember ID numbers, but is much more secure.