Using Text Fields Instead of Ids in ID and Lookup Fields

Many of our users have faced a situation where they don’t have a column of record Ids that must be used to update records or must be specified in the Lookup field to associate records with other records (Lookup or Master-detail fields).

XL-Connector has a built in functionality to match any column mapped to an Id, Lookup, or Master-detail  field using any text field on the object, whether it’s External Id, Email, Case Number, record Name, etc. To make this possible, simply map a column with the text field to either the Id field on your object or to any relational field (either lookup or master-detail), for example AccountId or OwnerId. The column can contain a value to match against any text field on the corresponding object and the plug-in will match the value to the corresponding record. There’s now an additional column (If not Id, match by) that gets populated with a list of text fields when any Id, Lookup, or Master-detail field is mapped to a column.

Here’s a simple example of changing the related account for a list of Contacts without having access to either Contact Ids or Account Ids. As you can see we only have Contact and Account names in the list:

Here are the mappings for this Update operation (note how we mapped the columns with names to the Id and master-detail fields in Salesforce):

With this setup, XL-Connector will know to first lookup the Contact and Account Ids based on the corresponding names, and then use these Ids in the actual Update operation.

Attention!
For reliable operation of the lookup functionality described in this post it is important that there are no duplicate record names (or text field you’re using) in the system. If there are duplicates – there will be an error reported and the operation will not succeed.