Match Salesforce relationship fields to records with no ID

Most Salesforce admins have come across the recurring problem of correctly linking one record to another via a Lookup or Master-detail relationship field when mass updating/creating data. Luckily for all admins that struggle with this, at Xappex we have found a solution that rescues them from the hell of matching records to the correct relationship. 

In this article we’ll go through the following items:

• Main functionalities of Lookup and Master detail fields in Salesforce and their most relevant differences.
• The problems most Salesforce Admins face with these relationships when performing data updates in bulk.
• How to easily overcome this with the XL-Connector solution.

Salesforce relationship fields: What are Lookup and Master-detail Fields?

In Salesforce, both Lookup and Master-Detail fields are used to create relationships between objects, but they function in slightly different ways:

How do Lookup Fields work in Salesforce?

1. Relationship: Creates a “loose” link between two objects. The relationship is not tightly bound; deleting the parent record does not necessarily affect the child records.
2. Ownership & Security: The child object does not inherit the security settings or ownership from the parent object.
3. Nullability: Lookup fields can be set as optional, meaning that a record in the child object does not have to be associated with a record in the parent object.
4. Cascade Delete: There is no cascade delete. Deleting a parent record will not delete associated child records.
5. Usage: Often used for scenarios where the relationship between objects is not critical for the structure and security of the data.

How do Master-Detail Fields work in Salesforce?

1. Relationship: Creates a “tight” link between two objects. The child object is highly dependent on the parent object.
2. Ownership & Security: The child object inherits its owner and sharing settings from the parent object.
3. Nullability: Master-detail fields must be set; a child object must have an associated parent record.
4. Cascade Delete: Deleting a parent (master) record will also delete all related child records.
5. Roll-Up Summary: Master-detail relationships allow you to create roll-up summary fields on the master record to aggregate values from the child records.
6. Usage: Used for objects that are integral to the application and require strict data integrity and security inheritance.

What is the difference between Lookup and Master-Detail relationship fields in Salesforce?

In summary, Lookup fields offer more flexibility but less control, while Master-Detail fields offer tighter control at the expense of some flexibility. Choose the appropriate relationship type based on the specific data modeling and security requirements of your Salesforce application.

Problem With Lookup and Master-detail Relationship Fields when Doing Mass Data Loads in Salesforce

When mass updating/creating data in Salesforce with the help of Salesforce Data Loader or any other similar software, to correctly link one record to another via a Lookup or Master-detail relationship field – one needs to pass the correct 15- or 18-digit Salesforce Id as the value for that field.

This is not the most user-friendly format and there’s no way to see which record any particular Id represents, it’s just a sequence of numbers and symbols. So when mass loading data one hopes that all the Id matching was done correctly beforehand and starts the data load.

Salesforce provides a way to use special fields called External Ids in a similar way to regular Ids, but those often carry not much more meaning than regular Ids and only work with the Upsert operation. There’s no way to match records using External Ids when using Insert or Update. And there’s no way to use any other text field (not indexed or marked as External Id) for this kind of matching.

That’s why we often hear complaints like “I wish it was possible to mass update contacts using the Email field”, “I wish we could do Upsert, but without the Insert part” or “I wish we could do VLOOKUP in Excel against the whole Salesforce database”.

The XL-Connector Solution

We heard this a lot and came up with a solution. When using XL-Connector, you now have the ability 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.

For example, if you are assigning a list of contacts to accounts, but only have account numbers available – you can map the column with account numbers to the AccountId Master-detail field on the Contact object and XL-Connector will match these account numbers to the corresponding accounts and assign the contacts to them.

Starting from version 3.4.4.5 of XL-Connector, columns mapped to an Id or Reference field don’t necessarily have to contain Salesforce Ids. They 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:

This is now available in all mappings for all operations so you are not limited to Upsert and External Id fields to match your records.

The text field you choose to look up by does need to have unique records across Salesforce though, we haven’t yet come up with a way to guess which of the two identical names or emails you would like to match by, but I’m sure we’ll get there some day 🙂