As a Salesforce administrator, I'm sure you've seen a lot of errors when loading data to your org. Today I want to focus on a particular error: Invalid Cross Reference Id.

If you do a quick Google search, you'll see that most users get this error when inserting or uploading data, but it isn't clear precisely under which circumstances. In addition, not all data loading failures throw this specific error.

I know this error happens when the data has an invalid reference to an ID on a lookup or master-detail field, but again, some questions remain: does this affect all lookups? What does it mean for an ID to be invalid? Is it the same as an ID that was deleted?

When this occurs

While writing this article, I went down the rabbit hole to understand what causes this error. I started by testing a few scenarios.

For all these, I'm using the Salesforce Inspector Chrome extension to try to reproduce the error when inserting contacts.
‍
Invalid ID

‍I started by inserting a contact and specifying an AccountId that is not even a real ID, just the word NotReal.
‍

When I tried to insert this, I got back a MALFORMED_ID error.

I understand from this that Salesforce is aware that NotReal is not an ID. This is not the same as saying that it isn't an account ID. Instead, Salesforce is saying: This is not an ID under any circumstances.

Ok, so that didn't throw the Invalid Cross Reference Id error. Let's try something else.
‍
Wrong ID

‍Now I want to try specifying an existing ID but of the wrong object type. So I grabbed an opportunity ID and put it under AccountId:
‍

This time I got a FIELD_INTEGRITY_EXCEPTION.
‍

So now Salesforce tells us that the Id is valid, but it doesn't belong to an account record.

Let's try to understand what this error means: Under the hood, Salesforce uses a relational database to store your data. In such database systems, every column will have what is known as an integrity constraint.

An integrity constraint defines what type of values or data types are allowed in a particular column. So, from this error, it sounds like the internal Salesforce database column has a constraint that only allows IDs that belong to the account object.

Still no sign of our precious error message, though.
‍
Non-existing ID

‍Now let's try with an account ID, but let's change a few characters to make it invalid. This should be different because it resembles an ID, it is of the correct object type, but it just doesn't exist (and it has never existed):
‍

Now we get INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY.
‍

Well, we are almost there! At least now we are seeing the words cross-reference.
‍
Deleted ID

‍Now let’s try specifying the ID of an account record that was deleted. This throws an ENTITY_IS_DELETED error message.
‍

I hadn't seen this particular error before, but it's good to know that Salesforce will not throw a vague error message when a record is deleted. You'll know immediately that is the case.

When I got to this part of the exercise, I was a little confused about why I hadn't seen the Invalid Cross Reference Id error. How come it's all over Google, but I don't see it?
‍
Non-existing User Id

‍Finally, I decided to update an existing account record and update the OwnerId field to a non-existent user ID. Lo and behold, this finally threw the error message I was looking for:
‍

It appears that this error message is reserved for user and profile IDs. Again, this seems to be an error from the Oracle database that Salesforce uses behind the scenes, as cross references are a type of mapping in such systems.

How to get rid of it

Based on everything we've seen throughout the article, we can see that we need to make sure that any ID in our data exists in the target org to prevent these and other errors.

A good exercise is to take the ID throwing the error and put it at the end of the Salesforce URL (easier in Salesforce Classic) to confirm if the record actually exists or is of the correct type.

For example, this is what I see if I put an ID that doesn't exist.
‍

So that’s it! I hope you learned something (I did!)

Until the next post.