[SalesForce] Updating a record based on external id which are not unique

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?

Best Answer

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.

Related Solutions

[SalesForce] the use of External ID field, not unique

Unfortunately you are right, using external IDs can lead to multiple matches during upsert operations.. taken from the documentation on Upserting examples:

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

So in answer to your question, yes you will get an error! Frustrating as it is.

Edit: This would need checking, but there is a specific exception type DmlException, which I imagine is what would be thrown, and you could (and probably should anyway) look to catch after an upsert call

try
{
    upsert yourList;
}
catch (DmlException e)
{
    // Report your error and/or try to re-handle the list?
}

Edit 2(!): Don't forget of course as well you can set your external ID to have unique (and case sensitive, and required) properties, which I would personally recommend if you ever intend on using the field in upsert commands.

[SalesForce] updating a records using external Id in Apex

You need to use the external if in the update call

upsert flist Externald__c;

For part two of your question, seeing that you want to relate the parent and child foo records you can t do it the way you were trying but I also think that you need to create the parent separately, however I think you either need to build a map to successfully relate the parent back to the child or have a process builder/flow do the parent mapping for you to relate the parent to the child accurately something like this:

List<Product2> products = [SELECT Name,PrdExternalId,Product__r.PrdExternalId FROM Product2 WHERE Warehouse_SKU__c!=Null];
    system.debug('Product list ********* ' + products);
        List<Foo__c> flist = new List<foo__c>();
        Map<String, Foo__c> parentfMap = new Map<String, foo__c>();
        for(Product2 p : products){
            foo__c f = new foo__C();
            f.Externald__c = p.PrdExternalId;
            f.Name = p.Name;
            if(!parentfMap.containsKey(p.PrdExternalId)){
                parentfMap.put(p.PrdExternalId, new Foo__c(Externald__c = p.Product__r.PrdExternalId))
            }

            flist.add(f);
            }
        upsert parentfMap.values() Externald__c; 
        for(Foo__c f : flist){
           f.foo__c = parentfMap.get(f.Externald__c).Id;
        }
        upsert flist Externald__c;

In theory that should work or be close to what you want to do

Best Answer

Related Solutions

[SalesForce] the use of External ID field, not unique

[SalesForce] updating a records using external Id in Apex

Related Topic