Importing from mySQL to SF, I'm trying to populate a custom field on the Account record called "Official Liaison". The Official Liaison field holds the salesforce ID for a Contact record. In mySQL, I have the AccountID and the official liaison's FirstName, LastName and Email. Currently, I'm performing a lookup in Informatica based on matching the Email address. That matches about 75% of the contacts. To get the remaining 25%, I want to try to match on the combination of FirstName + LastName. In other words, do a lookup on (sourceEmail = sfEmail OR (sourceFirstName = sfFirstName AND sourceLastName =sfLastName). Informatica only seems to allow ANDs in lookups. Is there any way to do what I'm trying to do?
Naturally, there will probably be about 5% or so that don't match even with First and Last Name. I then want to insert those new Contact records so they'll get matched on the next sync. I'm not sure how to go about that, exactly. Is there a best practice for what I'm trying to do here? Maybe Upsert to Contacts first, so I'll always be able to match on email address alone? I've considered doing that, but if the contact already exists with a slightly different email address, I want to be able to move their current email address to the Alternate Email Address field before overwriting the Email field with the address coming from the external system.
Perhaps you can make a second pass of the data, once you compete the lookup to the file by Email and if that match is null, Try to source (output file from the first Task) the Null records to continue to match on the FirstName and LastName in a subsequent second Task, filtering on records with Null Email. Does that work?