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

18 comments:

  1. AngularJS is a toolset for building the framework most suited to your application development. It is fully extensible and works well with other libraries. Every feature can be modified or replaced to suit your unique development workflow and feature needs. Read on to find out how.

    AngularJS Training in Chennai

    ReplyDelete
  2. 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
  3. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp great deal more around this condition.



    Digital marketing in chennai

    ReplyDelete
  4. I accept there are numerous more pleasurable open doors ahead for people that took a gander at your site.we are providing ReactJs training in Chennai.
    For more details: ReactJs training in Velachery | ReactJs training in chennai

    ReplyDelete
  5. 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

  6. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to article very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    DedicatedHosting4u.com

    ReplyDelete
  7. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..please sharing like this information.

    Thanks
    Cpa offers

    ReplyDelete
  8. Your post is very good. I got to learn a lot from your post. Thank you for sharing your article for us. it is amazing post
    what is seo
    types of seo

    ReplyDelete

  9. This is a fantastic idea! I like it a lot because it's super easy for the audience to see the value of opting in. wonderful and amazing post very use full your post thanks for sharing your article
    Android Application development
    Web application

    ReplyDelete
  10. 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
  11. Nice blog thanks for sharing Is this a special day for you? Beautiful and fragrant flowers are sure to make it even more amazing of a day no doubt. This is why Karuna Nursery Gardens offers you the best rental plants in Chennai that too at drop dead prices.
    plant nursery in chennai
    rental plants in chennai
    corporate gardening service in chennai

    ReplyDelete
  12. Excellent blog thanks for sharing Buy the best beauty parlour products wholesale in Chennai at Pixies Beauty Shop. Thousands of global top-tier brands to choose from and friendly faces all over, we would love to make your Salon journey, one the world recognizes.
    beauty Shop in Chennai

    ReplyDelete