3

I have an external system that needs to update a record in my org. This system only knows the external id, but the problem is that this external id is not unique, e.g. multiple records might have the same external id. I would like to update only the most recently created record out of these with the same external is. When I try upsert, I of course get an error saying multiple records found. Is there any way I work around this and achieve my goal?

  • I don't think you can do this without custom coding or making that field unique. – Mike Chale Aug 27 '13 at 17:58
  • 1
    Consider having a composite external id, which can be calculated (via workflow on salesforce, and via manipulation on the external system) if that helps. External Ids will error if they are duplicate in the import file, or in salesforce. – techtrekker Aug 27 '13 at 18:14
5

Upsert depends upon the value of External ID fields being unique. If you choose to not make External ID unique, you cannot leverage this feature. This means that you would need to leverage external logic in order to perform the upsert operations. Either clean up your data, or use external logic. One potential solution could be to write a trigger to eliminate a previous External ID matching the record's value when it is inserted or updated; you'll still need to clean up data that existed before this trigger was implemented. Off the top of my head, your trigger might look like this:

trigger clearOldIds on Contact (after insert, after update) {
    Set<String> newIds = new Set<String>(); // All new ID values
    for(Contact record:Trigger.new)
        newIds.add(record.External_Id__c);
    newIds.remove(null); // Ignore blank values
    Contact[] others = [SELECT Id FROM Contact WHERE External_Id__c IN :newIds AND Id NOT IN :Trigger.new];
    for(Contact record:others) // Clear old IDs
        record.External_ID__c = null;
    update others;
}

This would probably work in most cases, but you'll need to clean up your data at least once for this to operate correctly.

Another solution would be to create a service that your external service could consume. Here's a primitive example that would cover this:

global with sharing class SystemUtils {
    global webservice void upsertLast(Contact[] records) {
        Map<String, Id> uniqueValues = new Map<String, Id>();
        Contact[] newRecords = new Contact[0], oldRecords = new Contact[0];
        for(Contact record:records)
            uniqueValues.put(record.External_ID__c,null);
        for(Contact record:[SELECT Id,External_ID__c FROM Contact WHERE External_Id__c IN :uniqueValues.keySet() ORDER BY SystemModStamp ASC])
            uniqueValues.put(record.External_Id__c, record.Id);
        for(Contact record:records)
            record.Id = uniqueValues.get(record.External_Id__c);
        for(Contact record:records)
            (record.Id==null?newRecords:oldRecords).add(record);
        insert newRecords;
        update oldRecords;
    }
}

I haven't implemented error checking, that'd be the last step, but this sort of code would conceivably work assuming there were no errors.

| improve this answer | |
  • Thanks for that; one thing I thought of doing it to change the operation to insert, in the before insert logic to find the last created record having the same external id, update it, and fail the insert. Your thoughts? – Dedo Aug 28 '13 at 7:51
  • You can't fail the insert because it would roll back the entire transaction (the update wouldn't happen). You could, though, @future delete the inserted duplicate records. – sfdcfox Aug 28 '13 at 13:54
  • So maybe I can asynchronously update the record and after fail the the insert? – Dedo Aug 28 '13 at 20:17
  • Future methods also fail to commit if the original DML entries fail to commit. So, to answer your question, no, it won't work that way. Delete the newly inserted records in a @future method; they must survive unto the point of actually reaching a final commit before the database will commit any pending actions, such as sending emails, workflow field updates, or queueing future methods. – sfdcfox Aug 28 '13 at 20:22
0

you can set the batch size to 1 and load the records. you can overcome this error.

| improve this answer | |
  • This won't fix the error because the multiples are in the org, not in the data being uploaded. – Matt Lacey May 28 '15 at 0:55

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.