1 Reply Latest reply on Sep 10, 2010 3:31 AM by LaRetta_1

    Two last names in an external data source



      Two last names in an external data source


      FileMaker Pro



      Operating system version

      Windows XP

      Description of the issue

      I have a student database where I pull information from an external data source.  The external data source is a faculty database.  When I input the last name of a faculty into the student database and have more than one faculty with the same last name, it pulls the information from the first person.  How do I pull the right informaiton if I need the information from the second or third person with the last name?

      Expected result

      I need to be able to have two or more people with the same last name and be able to pull the right information.

        • 1. Re: Two last names in an external data source

          Relationships always see only the first related record.  Your relationship should be based upon more than the last name - it should be based upon unique IDs and your User would select from a list.  The list could be pop-up menu, portal, or filtered portal as your User types their name.

          When you have a lot of people, it is best to create a calculation which concatenates something easily identifiable about a person, such as name and phone.  This unique, easily identifyable reference is then used in your value list but NOT as the key fields.  Each person record should have an auto-enter, FM-generated, non-modifyable serial number.  Then your value list would be:

          Use Values from Field.  Select the ID in the left option and select the calculation in the right.  Below specify all values and specify 'only display second values'.  Attach this to your ID field (where people want to select) using a pop-up.  Now people can select a person even if they have identical last name AND identical names at all.  It is quite normal to have two Bill Smiths.  This method will present the User with:

          Bill Smith (206) 414-2479
          Bill Smith (509) 222-2222

          It depends upon whether you are selecting the person as you speak with them on the phone (so you can verify their phone number).  Sometimes, using a street address works; sometimes the city.  But I've had two Bill Smiths in same city as well.  This would be a business decision on which fields to concatenate.

          Concatenation is text calculation similar to:  First Name & " " & LastName & " - " & PhoneNumber