Matching Custom Object's Data Using Lookup Defined by Multiple Fields?

Question

Is it possible to setup a Lookup during data upload to a custom object that populates information such as a "Person ID" by finding matches of the "first name" and "last name" column combos in the data I'm uploading with my Contact object's records' "first name" and "Last name"?

I don't mind if the lookup is in Salesforce or Dataloader.io.

I'd just like to know how to make SalesForce or Dataloader do the following:

If I'm uploading data into a "Purchases" Custom Object with a Purchase record that has "First Name = John" + "Last Name = Smith," then assign the Contact Object's "PersonID" for the already existing "First Name = John" + "Last Name = Smith" record to the new Purchase record.

The only way that SalesForce tech support has taught me to do this is to upload the people into the Contact Object, export the Contact Object's data, and then use Excel to run a vlookup to match "First Name+Last Name" combos to their Salesforce IDs. THEN upload that new doc with the Salesforce IDs into Salesforce. This worked for datasets of just below 1,000. However, this isn't feasible for datasets we have that are in the millions of records, especially if we're trying to limit errors and steps by limiting our reliance on other products/tools.

It's a little like this StackExchange question but without coding each individual record as something you choose from.

Answer 1

You could create an external ID text field on the Contact object that could be referenced from the Purchases load file (e.g., concatenation of some fields) and then perform an upsert of Purchases, specifying to use the new field to match on the Contacts.

When using the Data Loader Step 2b will be to choose your related Objects. At this point you will have the ability to select the External ID field for the Contact__r (or whatever your lookup to Contact field is named). In the mapping step, make sure that the external ID field is selected as well (e.g., Contact__r.External_ID__c).

To keep the external ID populated on the Contact, you could create a workflow rule that copies the FirstName and LastName (or whatever fields work in your situation) to an external ID field that you create, but don't add it to any page layouts. There'd be an initial update to all existing Contacts you'd have to do to get their external ID set.

If you go this route, just watch out for duplicates. If possible, it would be a good idea to try to select a combination of fields on the Contact that are also available on the Purchases that you could guarantee would always be unique and make the Contact external ID field unique.

• To create the external Id field on the Contact, just create a new custom Text field on the Contact and check the External ID checkbox on it while creating it. There's a checkbox called External ID with a description of "Set this field as the unique record identifier from an external system".

• Create a Workflow rule on the Contact that fires when at least on of the fields you are interested in changes (see ISCHANGED function). For example, ISCHANGED(FirstName) || ISCHANGED(LastName)

• Create a Workflow field update that has a target field of the new external ID field. Set its formula to be the value of FirstName + LastName (or whatever fields you use). For example, formula value of FirstName + LastName.

• Make sure to activate the Workflow rule.

It would be a good idea to test out whatever you are doing in a sandbox first, of course.