5 Replies Latest reply on Feb 25, 2009 3:20 PM by davidhead

    Lookup Problem with same values

    jdun

      Title

      Lookup Problem with same values

      Post

      I'm doing a lookup to pull information from a Contacts Database into the current database.
      The matching fields for the relationship are the contact names.
      When I enter a contact name it pulls in the persons email, mobile, phone and fax numbers.
      Everything works fine for the most part but when there are a number of contact names the exact same (e.g two John Smiths) the lookup always picks the first John Smith it encounters in the Contact names database and pulls in their information.
      How can I get the lookup to pull in the information from the correct record in the Contact Names Database


        • 1. Re: Lookup Problem with same values
          davidhead
            

          And therein lies the lesson about what data is used as a match for relationships. It is almost always a bad idea to use a name as a match field. This is why people create an ID for each record with a serial number and use that to match records in a relationship.

           

          Another option is to use more than one match (predicate) - at the moment you are using the person's full name. You may want to use their full name and date of birth. However, there are still no guarantees that two John Smiths will not be born on the same day. 

          • 2. Re: Lookup Problem with same values
            jdun
               Thanks for your suggestion.
            I created a new calculation field (CombineKey) in both tables that combined our unique "Contact Code" for each company listed with their "Full Name"
            I then setup a relationship between these two new matching fields and now using the lookup it pulls in data from the correct record

            Thank You for your help

            • 3. Re: Lookup Problem with same values
              davidhead
                 That's good but there is no reason to create a combined key - you can use a multi-predicate relationship. So in this case you define two predicates - one you already have, to match full names and the other would be added, to match contact code. It will do the same thing as what you have done but you don't clutter your tables with additional fields and stored data.
              • 4. Re: Lookup Problem with same values
                jdun
                   I have Filemaker Pro 6. Will multi-predicate relationship work for that version?
                • 5. Re: Lookup Problem with same values
                  davidhead
                    

                  Unfortunately not. The method you have used is your only option.

                   

                  Just a suggestion - when you post questions, you might consider also posting FileMaker Pro version and platform. This will get you better solutions. :)