Thursday, April 20, 2017

Using Database.upsert with external ID field

External Id plays very important role if you want to update records without knowing the record Ids or want to relate the child record with parent record without knowing the parent record Id.

As a best practice, you should always make External Id unique. If you are performing upsert with External Id, then following situations will occur:

  1. If no record is found in table with provided External Id, then it will create record in table.
  2. If 1 record is found in table with provided External Id, then it will update record in table.
  3. If more than 1 records is found in table with provided External Id, then system will throw an error.

I am going to cover 2 different aspect of using external Id in apex.

  • Updating a record with External Id

Create a External Id field on Account as Account_Unique_Number__c and mark it as External Id and unique while creating it.Now we will create a new record using upsert.

Execute below command in developer console

List<Account> acclist=new list<Account>();
acc.name='Demo test1';
acc.Account_Unique_Number__c='00001';
acclist.add(acc);
Schema.SObjectField ftoken = Account.Fields.Account_Unique_Number__c;
Database.UpsertResult[] srList = Database.upsert(acclist,ftoken,false);
for (Database.UpsertResult sr : srList) {
    if (sr.isSuccess()) {
        // Operation was successful
    }
    else {
        // Operation failed, so get all errors                
        for(Database.Error err : sr.getErrors()) {
            System.debug('error has occurred.' + err.getStatusCode() + ': ' + err.getMessage());                    
            System.debug('fields that affected this error: ' + err.getFields());
            
        }
    }
}

As there is no record in Account with Account_Unique_Number__c as 00001, system will create a new record.

Now again we will run same script in developer console and will specify some more field values:

List<Account> acclist=new list<Account>();
Account acc=new Account();
acc.name='Demo test1';
acc.Account_Unique_Number__c='00001';
acc.type='Other';
acc.Industry='Banking';
acclist.add(acc);
Schema.SObjectField ftoken = Account.Fields.Account_Unique_Number__c;
Database.UpsertResult[] srList = Database.upsert(acclist,ftoken,false);
for (Database.UpsertResult sr : srList) {
    if (sr.isSuccess()) {
        // Operation was successful
    }
    else {
        // Operation failed, so get all errors                
        for(Database.Error err : sr.getErrors()) {
            System.debug('error has occurred.' + err.getStatusCode() + ': ' + err.getMessage());                    
            System.debug('fields that affected this error: ' + err.getFields());
            
        }
    }

Now you will see that system will update the record as it was able to find a Account record with Account_Unique_Number__c as 00001

  • Relating a child record with parent record by using parent record Id

In order to understand this, we will create contact record and will relate to account using Account_Unique_Number__c. Execute below code in developer console:

List<Contact> conlist=new list<Contact>();
Contact con=new Contact();
con.lastname='Kumar';
con.Firstname='Kumar';
con.email='sunil02kumar@gmail.com';
Account acc=new Account(Account_Unique_Number__c='00001');
con.Account=acc;
conlist.add(con);
Database.UpsertResult[] srList = Database.upsert(conlist,false);
for (Database.UpsertResult sr : srList) {
    if (sr.isSuccess()) {
        // Operation was successful
    }
    else {
        // Operation failed, so get all errors                
        for(Database.Error err : sr.getErrors()) {
            System.debug('error has occurred.' + err.getStatusCode() + ': ' + err.getMessage());                    
            System.debug('fields that affected this error: ' + err.getFields());
        }
    }
}

This will create a new contact for Account which have Account_Unique_Number__c as 00001.

In above code snippet, you can see that in order to relate contact with account, we are not specifying the account 15 or 18 digit record id. We are just specifying the external Id of account and system will maintain the relationship.

If you refer custom object as parent object then refer it with __r. For example in above scenario, if i have to relate contact with custom object say Parent_Obj__c, then I will use below code:

Parent_Obj__c  obj = new Parent_Obj__c(Unique_Number__c='00001');
con.Parent_Obj__r = obj;

Why it is recommended to mark External Id as unique?

Imagine you are creating a contact and specified External Id of parent. Suppose there are 2 records in account table with same value, then system will not able to identify with whom it needs to relate the contact and will throw error saying more than 1 match found.

Same is applicable when you update the record with External Id.



More Blogs>>: 
DYNAMIC APEX IN SALESFORCE
SOQL INJECTION IN SOQL
CUSTOM METADATA AND CUSTOM SETTINGS IMPLEMENTATION TRICKS
SMART TABLE USING ANGULARJS IN VISUALFORCE PAGE
REST API TUTORIAL FOR SALESFORCE
VISUALFORCE COMPONENT FOR RECORD STATUS BAR
FETCHING FILE FROM EXTERNAL/PUBLIC URL AND STORING IT IN SALESFORCE

48 comments:

  1. Some phone systems distribute the database across multiple servers. ShoreTel, for example, distributes components of the database to application servers and distributed voice mail servers that characterize the single image architecture in a multi-site environment. mysql dashboards

    ReplyDelete
  2. I am getting the error as VARIABLE_ASSIGNMENT [7]|ftoken|"common.apex.runtime.impl.ApexFieldToken@3db6c477"|0x3db6c477 when updating a record with external id.Could you please guide me how to resolve this?

    ReplyDelete
  3. Great blog thanks for sharing Looking for the best creative agency to fuel new brand ideas? Adhuntt Media is not just a digital marketing company in chennai. We specialize in revamping your brand identity to drive in best traffic that converts. Buckle up for a ride that is going to be filled with SEO, Social media marketing, unique Graphic & Logo design and efficient ads strike the perfect chord! Join the Adhuntt Media adventure right at Adhuntt Media.
    digital marketing company in chennai
    seo service in chennai
    web designing company in chennai
    social media marketing company in chennai

    ReplyDelete
  4. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    https://360digitmg.com/digital-marketing-training-in-hyderabad

    ReplyDelete
  5. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.

    Data Science Institute in Bangalore

    ReplyDelete
  6. 360DigiTMG GunturJuly 8, 2020 at 8:40 AM

    I will really appreciate the writer's choice for choosing this excellent article appropriate to my matter.Here is deep description about the article matter which helped me more.

    data science course in guntur

    ReplyDelete
  7. I simply want to mention I am just all new to blogging and site-building and truly loved you’re web page. Almost certainly I’m planning to bookmark your site . You really have outstanding stories. Many thanks for revealing your webpage.…

    Azure Training in Chennai

    Azure Training in Bangalore

    Azure Training in Hyderabad

    Azure Training in Pune

    Azure Training | microsoft azure certification | Azure Online Training Course

    Azure Online Training

    ReplyDelete
  8. Wow, What a Excellent post. I really found this to much informatics. It is what i was searching for.I would like to suggest you that please keep sharing such type of info.Thanks Bethlehem Website Development

    ReplyDelete
  9. Do you Need Instant Loan from $2,000 to $50,000,000.00 with no collateral required and receive a no obligation loan approval.We welcome the opportunity to show you how effective we are at helping our clients obtain the money they need to succeed. Our professional staff is available for a Free Consultation.At Patiala legitimate company Pvt , contact us now 2% interest rate, both long and short term cash reply to us (Whats App) number: +919394133968 patialalegitimate515@gmail.com 
    Mr Jeffery

    ReplyDelete
  10. Thanks for the valuable Content. I really learned a lot here. Do share more like this.
    Sales Force Testing
    Salesforce Project Documentation

    ReplyDelete
  11. Through this Digital Marketing Institute in Panchkula, you will become an expert in modules such as SEO, Social Media Marketing, PPC, Analytics, Content, Mobile, and Email Marketing. Our Social Media Marketing and PPC courses in Panchkula have trained end number of students. Work on real-world projects, learn the latest tools, and attend masterclasses led by the Google and Facebook certified team.

    - Get hands-on training on live projects!
    - Upgrade your resume
    - Get prepared for your dream job.
    - 120+ hours of high-quality training.
    - 10+ live projects.
    - 25+ digital marketing tools and platforms.


    Call: +91 9887046666
    Location: SCO-9, First Floor, Sector-11, Panchkula, Haryana 134109

    ReplyDelete
  12. Authorization by government, state and neighborhood authorities is normal, and many bars, supermarkets, cafés and dance club are scrambling for a way of remaining one stride in front of those wearing phony ID cards to keep away from infractions.2022 fake id
    fake ids

    ReplyDelete
  13. Through this Digital Marketing Training Institute in Panchkula you will become an expert in modules such as SEO, Social Media Marketing, PPC, Analytics, Content, Mobile, and Email Marketing. Our Social Media Marketing and PPC courses in Panchkula have trained end number of students. Work on real-world projects, learn the latest tools, and attend masterclasses led by the Google and Facebook certified team.



    - Get hands-on training on live projects!

    - Upgrade your resume

    - Get prepared for your dream job.

    - 120+ hours of high-quality training.

    - 10+ live projects.

    - 25+ digital marketing tools and platforms.

    ReplyDelete
  14. In straightforward words, the clients don't need to stress over the uninitiated or read mail that they needn't bother with any more. Low spam scores

    ReplyDelete
  15. Nice blog, informative and knowledgeable content. Thanks for sharing with us.
    Best Data Science Training in Hyderabad

    ReplyDelete
  16. I love your wp format, where did you get a hold of it? IRISH FAKE ID

    ReplyDelete
  17. There is little distinction between the ranges of abilities moved by information modelers and those moved by database managers.college placement cells database

    ReplyDelete
  18. Recent Identity theft statistics and exactly what identity theft is. The five types of identity theft. How to help avoid being a victim of fraud. What are ways the thieves get personal information??? Medical Identity Theft can be every persons worst nightmare. Is it possible for somebody to get information and use it for child identity theft? fake id

    ReplyDelete
  19. We are a leading Best SEO Company in Hyderabad who believes in human-first SEO strategies. Understanding the sentiment of your business needs, we realize that any business is driven by much more than just SERPs. It is driven by the right audience - real people, whose presence matters.

    ReplyDelete
  20. I think this is a standout amongst the most critical data for me. What"s more, i"m happy perusing your article. Be that as it may, ought to comment on some broad things fake id top

    ReplyDelete
  21. Thank you for sharing an amazing & wonderful blog. This content is very useful, informative and valuable in order to enhance knowledge. Keep sharing this type of content with us & keep updating us with new blogs. Apart from this, if anyone who wants to join the Python Advanced training institute in Delhi, can contact 9311002620 or visit our website-
    https://www.htsindia.com/Courses/python/online-python-training-institute

    ReplyDelete
  22. eDigital is a trusted Best digital marketing agency in Hyderabad If you are looking to gain from an active local presence, we can help you grow across platforms and devices - helping you increase online visibility, website traffic, lead generation, local reputation etc.

    ReplyDelete
  23. i'd later to make perceived that this blog in actuality fulfilled me to appreciate it! much appreciated, each and every one proper case. thanks! Wishes For Sister Birthday

    ReplyDelete
  24. This is a great post. Your blog is very informative I have learned a lot of information from your blog. We are top Website Development Company in India.



    If anyone is looking for Web Development services contact today: +91 9501406707

    ReplyDelete
  25. Nice blog.
    Digital Marketing Course in Chandigarh holds together the professional modules of a comprehensive learning and experience which can land you in the arena of a shining career and wide professional knowledge.

    ReplyDelete
  26. Happy to have read such a helpful post, which is quite helpful. It provided me with a wealth of knowledge, which I will definitely remember. Continue to share custom erp

    ReplyDelete
  27. best digital marketing agency in hyderabad and web design company weboptmise.in

    ReplyDelete
  28. This comment has been removed by the author.

    ReplyDelete
  29. This comment has been removed by the author.

    ReplyDelete
  30. agar aap ek student hai aur Hindi Matra or Hindi Vyakran se sambandhit jaankari chaahiye to hamare blog ko visit kar sakte hain.

    ReplyDelete
  31. DO YOU NEED FINANCES? ARE YOU LOOKING FOR FINANCES? ARE YOU LOOKING FOR FINANCING TO ENLARGE YOUR BUSINESS?via email:bullsindia187@gmail.com

    ReplyDelete
  32. Discover the stunning bridal shower dresses by Ammara Khan, perfect for your barat or bridal shower. Explore our couture collection and find the dress of your dreams.

    ReplyDelete
  33. Thank you this is the one information im searching for
    GMAT Coaching in Hyderabad

    ReplyDelete