4

Today I was working on some Apex code that was delivered to us by a third party contractor to our organization, and ran into a line of code that I have never seen before:

upsert certsToUpsert.values() Unique_Certificate_ID__c;

Can anyone tell me what exactly is going on here? I understand that the first part of the statement is saying to update all of the values in the map certsToUpsert, but I have no idea what this external id field is doing.

To complicate matters further, I need to make some edits that remove certain elements of this map before insertion.

I attempted to do this by creating a list from the certsToUpsert.values() return, iterating through them, and then adding the values that we should be upserting into another list that I then attempted to upsert. However, despite being the same values as are in the map originally minus the duplicates, I then get an error:

INVALID_FIELD, More than 1 field provided in an external foreign key reference in entity: Certificate__c: []

I have tried both including and not including this Unique_Certificate_ID__c with the DML statement, but regardless I always come back to that same error. Given that these are the same values, and that with the field included in the DML statement I believe I am doing essentially the same thing with just a few less elements attempted to be upsert, I do not understand how this is possible.

If anyone can tell me why this may be, it would be greatly appreciated, but if nothing else it would be great to know what exactly the inclusion of this field is changing regarding this DML statement.

I am happy to provide further code if it may help identified things but as the class is very dense I didn't want to vomit code everywhere if not needed.

3 Answers 3

6

This is valid only for the upsert function. Its purpose is to provide an "External ID" field to create/update records against. This is often useful when you don't know the Salesforce ID of a record, but you know a specific ID that exists in another system. You can read more about Upsert to learn more about how this works.

As to your specific question, it sounds like the problem is actually elsewhere; the records contain references to objects with more than one key. For example, this typically works when upserting Contacts:

Contact conRecord = new Contact(LastName='Fear',Account=new Account(Name='Fear Household'));
insert conRecord;

Ultimately, you have something like this going on; an sObject is put in to a relationship object, but there's more than one key field populated. This is not allowed. Without seeing your specific code, it'd be hard to tell exactly what went wrong, but you'll need to examine your data closely.

1
  • That certainly makes a lot of sense. I will definitely be doing a run with a debug message on the data pre upsert attempt to see if I cannot track this down. Greatly appreciate the input, I think this should put me on the right track Feb 21, 2019 at 21:29
3

This line

upsert certsToUpsert.values() Unique_Certificate_ID__c;

is telling Salesforce to use the Unique_Certificate_ID__c field to match records in certsToUpsert.values() against the existing records in your org when it is deciding whether each record is new, to be inserted, or existing, to be updated.

If that list contains three records, like this:

Unique_Certificate_ID__c = '123',
Unique_Certificate_ID__c = '124',
Unique_Certificate_ID__c = null

and your org contains one existing record, with Unique_Certificate_ID__c = '123', what will happen is that the record with '123' will be updated to match the corresponding record you're upserting, and two new records will be created because their Unique_Certificate_ID__c fields didn't match anything existing in Salesforce.

An upsert can be done using the Id or an External Id field as the unique identifier to match against existing records. Since your records may have more than one External Id field, and always have an Id, you supply the field name to be clear about what you mean.

1
  • This is a fantastic explanation, really appreciate the breakdown. Certainly makes sense why this would be necessary for this case. Feb 21, 2019 at 21:25
2

From SF docs for UPSERT command

The upsert statement matches the sObjects with existing records by comparing values of one field. If you don’t specify a field when calling this statement, the upsert statement uses the sObject’s ID to match the sObject with existing records in Salesforce. Alternatively, you can specify a field to use for matching. For custom objects, specify a custom field marked as external ID. For standard objects, you can specify any field that has the idLookup attribute set to true. For example, the Email field of Contact or User has the idLookup attribute set

If you just use UPSERT accountList; then Id is used to uniquely update record. If you want to do UPSERT not on based of ID but some external field, you can define that field by specifying the field token.

upsert sObjectList Account.Fields.MyExternalId__c;

Here MyExternalId__c is the field token. The values of that field will be used for uniquely identifying records and then updating them if they exists / or insert if they dont.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .