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

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.

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

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.