Use Any Text field to Identify Record References in ID, Master-detail, and Lookup Fields

In a lot of cases our users face a situation when 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 365 has a built in functionality of looking up record Ids based on any text field on the linked object, whether it’s record name, external Id, email address, phone number etc. The only requirement is that the field doesn’t contain duplicates across all records of a given object. If there are duplicates, XL-Connector will return an error identifying how many duplicates were found in the system.

In a way, this allows to use all operations (Insert, Update, Upsert) as Upsert, and without limiting to external Id fields in Salesforce.

To make this possible, simply map a column of text values that will be used for identification to the corresponding field (it can be either the Id field on your object or to any relational field (either lookup or master-detail), for example AccountId or OwnerId).

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 365 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.

In the example above, the record Name fields were chosen, but as we have mentioned above, it’s possible to select any text field in the If not Id match by drop-down:

Attention!
For reliable operation of the lookup functionality described in this post it is important that there are no duplicate values in the system in the field selected for record identification. If there are duplicates – there will be an error reported specifying how many duplicates were found.