9

I have a custom object, with a Text Name (ID) field. When I upsert two records with identical Name/ID, but otherwise different values, in two separate upsert statement, one record results, as expected.

When I create a list, add these records to the list, and then upsert the list, I get a DUPLICATE_VALUE error. What gives? By definition, upsert should not do this, should it? Even worse: it doesn't matter whether a record with the identical Name already exists or not, the upsert will fail in both scenarios.

By the way: by default upsert a does not use the Name/ID field as identifying/unique field, as you might expect. You need to do upsert a Name

Complete code:

List<Flight__c> flights = new List<Flight__c>();
Flight__c f1 = new Flight__c();
f1.Name = 'ABC123';
f1.Flight_Number__c = 'ABC';
flights.add(f1);

Flight__c f2 = new Flight__c();
f2.Name = 'ABC123';
f2.Flight_Number__c = '123';
flights.add(f2);

upsert flights Name;

Error message: Line: 12, Column: 1 System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, Duplicate external id specified: ABC123: [Name]

Name being the standard identifying field of a custom object. You cannot edit the properties of this field, e.g. making it unique or not unique.

  • Can you paste the whole text of the DUPLICATE_VALUE error, please? Also, when you say name (ID), what do you mean? Is it an external ID? Salesforce uses the unique ID field (help.salesforce.com/apex/…) to identify the record, not the Name field – mkorman Jan 4 '16 at 14:34
  • Pasted the code and the error message. Name is the standard identifying field of a custom object. – Sander de Jong Jan 4 '16 at 14:49
  • I think that Name is used as the 'naming' field: what you see on lists, etc. Custom objects still have a unique ID that is used to detect them uniquely for insert/upsert purposes. I think that you have defined 'Name' as an external ID in your object definition, and that is causing uniqueness. – mkorman Jan 4 '16 at 14:56
  • Nope. Name is not an external ID. When you create a custom object, you get the choice: must the key be a Text field (Name) or a generated ID (ID). I chose Text Name. And when you use that field Name as a field for upsert, Salesforce treats it as a unique field. For upserts of individual records, it works as expected. For upserts of lists of records, it has (for me) unexpected behaviors. – Sander de Jong Jan 4 '16 at 15:04
7

So, if I understood correctly, you are passing the same key twice in the list, correct? If that's the case, this is documented

Upserting Records

If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated.

  • The way I read this help page, is that it is about an individual upsert. And then the error message would make sense, provided that multiple keys already exist. But in my example, there is no match or only one. It looks like Salesforce checks the list for duplicate keys, but that would be strange. From the help page, it is not apparent to me that it performs this check. – Sander de Jong Jan 4 '16 at 14:48
  • I am not sure I understand what you mean, from the code you posted, you are using the same external id in the same list in the same transaction, therefore, getting this error (which is expected) is there something missing from the code? – PepeFloyd Jan 4 '16 at 15:01
  • No, Name is not an external ID (please see another comment higher up in this page). Upsert should, by definition, be able to handle the upsert of something with an identical key value, it must behave as an update then, not as an insert. – Sander de Jong Jan 4 '16 at 15:05
2

I think the issue is related to the uniqueness of the 'Name' field. This field is just a user-friendly name for the object that is shown in layouts, related lists, etc. But this field is not guaranteed to be unique.

For instance, you can have 2 contacts with the same First and Last name. It may be erroneous information, but SF will not prevent you from doing that.

Your issue is that by doing

upsert flights Name;

you're telling Salesforce to use the 'Name' field as an external ID. If you just do

upsert flights;

you will not see the error (but this may not be what you want... see below).

From the trailhead lesson on DML:

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

Suggested solution

If you want an absolutely unique field to identify your records, I would recommend not using the out-of-the-box 'Name' field. Instead, create a custom field (for isntance, 'FlightName') and set the metadata on that field to be External ID and/or Unique.

  • upsert flights is what the original code did and it caused duplicate values. Before that, we used a unique External ID field that was a copy of the Name field, but that doesn't work either: we had a trigger fill the field, but if the key value didn't already exist, then the upsert of two records with identical key values fails. Complicated stuff, error prone. :-( – Sander de Jong Jan 4 '16 at 15:22
  • I think it's more a question of what you want to achieve, rather than what the previous code was doing. Are you 100% that was the functionality? Was it verified by unit tests? According to the doc I linked above: '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 property set to true, – mkorman Jan 4 '16 at 15:51
0

In your case, you are upserting records into flightsobject by passing Name as the validating factor.

When you do this, you will get 2 records. Because, both the records that you are inserting are having same name as 'ABC123'.

Here Salesforce will not be able to understand which is the actual that needs to be updated. So we will get the exception and records would neither be inserted nor updated.

To avoid this exception, we can specify any unique field as the validating factor for upsert dml statement like below.

Syntax : upsert flights Line_Item_ID__c // In this scenario, Salesforce will search for Line_Item_ID__c in the flights object to validate if the record is there or not.

-1

Guys i got solution for this and it will work suppose object car has brake field which is external Id. Go and make it unique. All the car records you want to UPSERT put it in MAP instead of LIST . Note: this is the trick you have to use. Then simply create another list and add all map records to new list Then do UPSERT of list. It works magically. I to got shocked :P

 map<car> mp =  new map<car>();

  List<car> lt =  new List<car>();

   lt.addAll(mp);

upsert  lt brake;
-1

1) Name field cannot be set as unique or a external id field and if u need to make them unique u need to write custom validations or triggers 2) In your code you are mentioning providing name field to Upsert method and it first checks all the records in your list. Since you have same record twice you are getting an error when you try to upsert both records in tandem. 3) But when you split your code to insert one record at a time upsert doesn't complain as it has not found any duplicates so it goes ahead with the INSERT and your database also doesn't complain as NAME field is not an unique field.

In your case if you want to have same value in name field 'ABC123' in all records i feel you should go for INSERT instead of UPSERT. If you want to have name as unique check point 1. There are many posts in success community on how to make name field unique using validation rules etc. In your case UPSERT is no use as you don't have any external field defined. Main benefit of UPSERT is to update existing records (based on external id field that is also unique) and insert new records if they don't exists. External system sending data into SFDC need not worry abt checking if record exists etc.

https://success.salesforce.com/answers?id=9063A000000DY3AQAW https://help.salesforce.com/articleView?id=managing_duplicates_overview.htm&type=0 depends on your budget.

-2

So, as this is a production problem and I need to solve this one hour ago, this is how I 'fixed' it.

I replaced

upsert flights Name;

by

try {
    upsert flights Name;
}
catch (Exception e) {
    for (Flight__c f : flights) {
        upsert f Name;
    }
}

And now it works. So I had to de-bulkify my code. Yay. Happy now? No. I guess Salesforce would call this 'Works as designed.' Well, for me it doesn't work as expected.

  • 2
    See my answer below. You can fix it by just doing "upsert flights;", but I think this is an issue with the data model. – mkorman Jan 4 '16 at 15:14
  • As I see it, the upsert of the list and the loop of upsert should be functionally identical. But they're not. I am still looking for the documentation that will clearly state this. – Sander de Jong Jan 4 '16 at 15:26
  • I think that the difference is stated in the documentation that @PepeFloyd mentioned above: "If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated." If your list contains 2 object with the same name, you're matching the same key multiple times and you get the exception. If you update records individually, then you're matching the key only once in each individual upsert (but you'll burn through your DML statement limits quite quickly) – mkorman Jan 4 '16 at 15:56
  • 1
    In any case, very interesting question. I've learned something about DML researching into this :) – mkorman Jan 4 '16 at 15:57
  • Actually, the code will become much worse. Because of the failed upserts (they became inserts), we now have lots of duplicate records. So my fix won't work, because the unique key is no longer unique and even individual upsert statements will now fail. Removing the duplicates is going to be a headache and take many days (lots and lots of references to them), so I will replace the upserts with inserts and updates. Yeah, that really sucks. And debulkified code of course :-( Just your typical Monday for you. – Sander de Jong Jan 4 '16 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.