Apex Code Development You need to sign in to do that
Don't have an account?
Elizabeth Bryant Database.Upsert()- Need to Force upsert of first record with duplicate External ID
Is there a way to do this?
Here's a breakdown of what has been requested of my batch code:
We are working on a large integration with our Salesforce and various other divisions/mainframes. The goal is to completely eliminate the mainframes as they are acting as "middle men" between the other divisions and us not to mention they were built 25+ years ago and are outdated at this point. One division will be pushing us data to our Mule in the form of a .txt file and then Mule will insert the lines in that file as a text field into a Staging object. My batch then picks new records up from Staging and parses the record details into fields in a Master object. I use Database.Upsert() with an external ID to move those details into Master.
With the current example we have gotten from the division we are integrating into our SF org, there are some lines of data that have the same external ID (these would be concidered updates to the record). The below image shows an example (there are 3 external IDs here).
As expected when I pass my list of Master data to be upserted, I get the dreaded "Duplicate external id specified: 59XXXXXXXX01" error when the batch gets to these records and they do not upsert at all. I've been told that this scenerio should not exist outside of my test data I was sent, and that when we go live I will not receive multiple external ids in a day; but of course, we want to code to handle this situation just in case.
Is there a work around for this scenario where I can force the first record to upsert and then come back and process the second record? We will have change history tracking set up for the Master object and it is required that we have history of every update for each Master record. Inserting or updating all records that we are sent is mandatory.
I was thinking that I could change my Upsert to accept a single record and process the records in the batch individually, but would that defeat the purpose of a batch class and cause DML governor limits to be hit for large transactions like I suspect? Should I use Database.Insert() and Database.Update() separately instead of Database.Upsert()? I'm trying my hardest to keep my DML operations to bare minimum (with Database.Upsert() I am performing 4 DML operations.)
Any advice on how to create a workaround would be extremely helpful and greatly appreciated.
Here's a breakdown of what has been requested of my batch code:
We are working on a large integration with our Salesforce and various other divisions/mainframes. The goal is to completely eliminate the mainframes as they are acting as "middle men" between the other divisions and us not to mention they were built 25+ years ago and are outdated at this point. One division will be pushing us data to our Mule in the form of a .txt file and then Mule will insert the lines in that file as a text field into a Staging object. My batch then picks new records up from Staging and parses the record details into fields in a Master object. I use Database.Upsert() with an external ID to move those details into Master.
With the current example we have gotten from the division we are integrating into our SF org, there are some lines of data that have the same external ID (these would be concidered updates to the record). The below image shows an example (there are 3 external IDs here).
As expected when I pass my list of Master data to be upserted, I get the dreaded "Duplicate external id specified: 59XXXXXXXX01" error when the batch gets to these records and they do not upsert at all. I've been told that this scenerio should not exist outside of my test data I was sent, and that when we go live I will not receive multiple external ids in a day; but of course, we want to code to handle this situation just in case.
Is there a work around for this scenario where I can force the first record to upsert and then come back and process the second record? We will have change history tracking set up for the Master object and it is required that we have history of every update for each Master record. Inserting or updating all records that we are sent is mandatory.
I was thinking that I could change my Upsert to accept a single record and process the records in the batch individually, but would that defeat the purpose of a batch class and cause DML governor limits to be hit for large transactions like I suspect? Should I use Database.Insert() and Database.Update() separately instead of Database.Upsert()? I'm trying my hardest to keep my DML operations to bare minimum (with Database.Upsert() I am performing 4 DML operations.)
Any advice on how to create a workaround would be extremely helpful and greatly appreciated.
Thanks for your help through all of this. I've actually resolved all of these issues.
In order to get my batch to perform as needed, I had to change my Upsert call to accept a single record instead of a list and limit my chunk size for the batch to avoid DML governor limits being hit. By doing that, the second records with a duplicate code__c value were treated as updates instead of being seen as duplicates. This also helped me to get rid of a bunch of extra lists/variables that I had doing extra, unnecessary work.
Thanks,
Liz
All Answers
A tricky situaltion, I think if you have the need to upsert the same record a number of times you can't avoid a separate DML operation for each,
Firstly if you limit your batch size to 100 then at least the batch will comeplete if you receive the 1 record 100 times, from there I would try to split the records similar to what you suggested, loop over the records puting the first instance into a map, using it's unique Id as a key, then when the next record gets checked if the map already contains that unique Id in it's keyset put the next record in a list.
Upsert the map values which will hopefully take care of most of your records, then if there are records in the list, loop over the list upserting them one at a time.
I think you will still need to go with the upsert function for both the map values and the list incase your record updates are split across multiple batches.
best of luck
I'm getting an error at line 156: "Expression must be a list type: Map", but I am using a Map here so I'm confused.
Here, I add duplicates to the Map or the List using the error results from my original Database.Upsert() operation:
for(Database.Error inErr: insertResult.getErrors()){ if(String.valueOf(inErr.getStatusCode()) == 'DUPLICATE_VALUE'){ if(dups.containsKey(addList[sInt].CODE__c)){ dupList.add(addList[sInt]); } else{ dups.put(addList[sInt].CODE__c,addList[sInt]); } }Then I perform the new Upserts:
I have tried 3 different ways to fix the error that I'm receiving:
for(Database.UpsertResult res: dupRes){ if(res.isSuccess()){ //successIds.add(insertResult.getId()); //if(dups.containsKey(dups[d].CODE__c)){ // dups.get(dups[d].CODE__c)).Upload_DateTime__c = system.now(); // } //if(dups.containsKey(String.valueOf(dups[d].values().CODE__c))){ // dups.get(String.valueOf(dups[d].values().CODE__c)).Upload_DateTime__c = system.now(); //} if(dups.containsKey(String.valueOf(dups[d].CODE__c))){ dups.get(String.valueOf(dups[d].CODE__c)).Upload_DateTime__c = system.now(); //THIS IS LINE 156 } Batch_Log__c sInLog = new Batch_Log__c(); sInLog.Response__c = 'Succeeded'; sInLog.Source__c = 'Staging'; sInLog.Eligibility_ID__c = dups[d].CODE__c; sInLog.Uploaded_Date__c = date.today(); sInLog.Uploaded_Date_Time__c = dateTime.now(); successLogs.add(sInLog);Thanks for any input that you can give me to fix this error!
-Liz
Would you mind providing the whole class and the exact error message? it's a bit hard to get the full context from partial bits, thanks
Thanks for your help through all of this. I've actually resolved all of these issues.
In order to get my batch to perform as needed, I had to change my Upsert call to accept a single record instead of a list and limit my chunk size for the batch to avoid DML governor limits being hit. By doing that, the second records with a duplicate code__c value were treated as updates instead of being seen as duplicates. This also helped me to get rid of a bunch of extra lists/variables that I had doing extra, unnecessary work.
Thanks,
Liz