Is it possible to not use an ExternalId field for Database.Upsert?

Question

I'm trying to upser a list of Asset records via Database.upsert(List, externalIdField, boolean) and on the externalIdField, I want to pass the standard field 'SerialNumber'.

List<Database.UpsertResult> upsertAssetResult = Database.upsert(finalAssetList, Asset.SerialNumber, false);

However, an error occurred saying an External Id custom field OR a Standard Indexed field must be used.

Invalid field for upsert, must be an External Id custom or standard indexed field: SerialNumber

Now if you go to the Asset standard object, you will see that SerialNumber is an INDEXED standard field and if we believe the error message, it can be used.

Am I missing something? OR is the error message is incorrect and that only External Id custom fields are allowed?

Thanks.

Answer 1

The error message is phrased confusingly. It means there's three qualities that must be met for the field to be used:

• Must be an External Id
• Must be custom or standard
• Must be indexed

This doesn't really express much more than that it has to be an External Id.

For clearer discussion, see Upserting Records and the Upsert SOAP call. The nomenclature there is explained as

On custom objects, this call [upsert] uses an indexed custom field called an external ID to determine whether to create a new record or update an existing record. On standard objects, this call can use the name of any field with the idLookup field property instead of the external ID.

(More on idLookup).

Unfortunately, you cannot upsert using any indexed standard field.

Answer 2

In addition with response to David-

Here we need to understand, key field responsible for upsertion must be UNIQUE in same way as Salesforce Record Id is. Lookup Fields, Other non-unique text fields (like Asset Number) do not guarantee record uniqueness.

Upsert operation looks for record in database based on given EXTERNAL FIELD. Let's suppose if Salesforce allows upsert based on lookup then it would be ambiguous to find out unique record because multiple records may exist for that lookup Id.

For example, If there are two Assets with same Account Id and you upsert, how Salesforce will indentify which Asset to be updated.

So,

1. Must be an External Id and Unique AND

2. Must be custom or standard AND

3. Must be indexed