Foreign Key External ID not found even though value exists

Question

When performing an upsert operation via Mulesoft on a record that is a child of Account in SFDC, we are seeing the error

"Foreign key external ID: 0000336681 not found for field SWT_Account_Number__c in entity Account".

I have queried and verified that the Account Number in question does exist in Salesforce.

Here is reference to the field in the payload:

[{

SWT_Partner_Account__r: {
    type: "Account",
    SWT_Account_Number__c  : "0000336681"
},

}]    

SWT_Account_Number__c is a "Auto Number" field and marked as external ID.

We are testing this on a sandbox that has been just refreshed from Production. Wonder if this has got anything to with the issue.

Answer 1

Auto number is an auto-calculated read-only field and although you can set it as External Id, it cannot be used for upserting record.

Refer to extracts below from this Salesforce article which describes the behavior for Data loader upsert and this also applies for Mulesoft integration.

As a workaround, consider having an editable field instead as External Id.

... It is not possible to select an auto-number field to use as the external id to match records.

While it is possible to use an auto-number field as an external ID field, Data Loader does not support its use for the Upsert functionality.

The Upsert command performs an "insert or update" function, depending on whether or not Data Loader finds an existing record in the system. Since auto-number fields cannot be inserted or created, Data Loader does not allow you to match by this field due to the possibility that the upsert will need to insert new data.