All about Upsert and External ID in Dataloader and Apex – Videos

You may be wondering that there are tons of articles available for upsert operation and what is need of one more blog post. I wanted to note everything important about upsert operations using datalaoder and Apex with identified gotchas at single place, and that’s why its here 🙂 .

External ID is field in Salesforce to say that this field is primary key in external Database which can be used to identify that external record exists in Salesforce or not ? There are many benefits of external key like :

  • It can be used to identify if record exists or not and record automatically inserted or updated using upsert operation
  • Upsert operation is supported by Dataloader, Apex and API
  • Any field marked as external id is indexed and can be used in SOQL or report filter
  • You can create Parent child record in single statement in Apex

Using Upsert operation to create/update child record and relate to existing parent record with help of External Id

Lets assume that you have list of contacts and Account in external SQL Server database and you want to import those Account and contact weekly. It is possible that you have already imported contacts previously so don’t want to duplicate records if already created in Salesforce. To solve this problem we can use external Id on Contact and use upsert operation on dataloader. Let’s assume external Id field on Contact is “SQL_Server__Id__c” (I am assuming that Account records in Salesforce also have external Id and records are already imported. Same below steps can be used to import Account record as well).

  • Launch Dataloader installed from Salesforce and make sure settings like “Batch Size”, “Server host”, “Start at row” are correct.
Salesforce Dataloader Settings
Salesforce Dataloader Settings
  • click on “Upsert” button.
  • Login Window may appear, so enter your Username and Password+Security token.
  • Select “Contact” object in next window and choose CSV file of contact you want to import in Salesforce.
  • It will alert informing total number of records going to be processed, click OK.
  • In next screen , you will be prompted to select field from Contact which should be used by Salesforce to identify record and take decision that it needs to be updated or inserted. Id field and all fields which are marked as external Id will be listed here.  Don’t expect this screen in any other operations except upsert.
External Id choice in Upsert operation of Dataloader
External Id choice in Upsert operation of Dataloader
  • As shown in above image, select correct external Id field (in this case SQL_Server_Id__c) and click next.
  • Next screen will show list of all Parent Object and each object will provide choice to select either Id field or external id field of Parent Object. This is one of amazing feature of Salesforce where Parent child relationship can be established with help of external Id (No need to use excel sheet Vlookup functions to extract 15 digit Salesforce Id to establish relationship). In this case example, we only want to create relationship with  Account so we will use only Account’s external Id and leave others unselected.
Create Parent child relationship using External Id in Dataloader
Create Parent child relationship using External Id in Dataloader
  • At end of this article, I have provided Youtube Video link following same steps.
  • In Next window we need to map CSV file field with Salesforce fields. One point to notice in below image, how we are mapping Parent Account’s External Id with contact record.
Field Mapping to create Parent Child relationship using ExternalId in Salesforce
Field Mapping to create Parent Child relationship using ExternalId in Salesforce
  • Once all of above steps are followed, we can click on Next window and upsert operation will start. Salesforce will provide CSV file of success and failure results to perform post analysis of operation.

How to use upsert operation in Apex

We can upsert records in Apex also, as shown in below code snippet

List lstContact = new List();
Contact con = new Contact (lastName = 'Zaa', SQL_Server_Id__c='3',firstName='Jitendra');
lstContact.add(con);
//.. Other Contact records added in List
upsert lstContact  SQL_Server_Id__c;

Above code snippet will perform upsert operation on all Contact with field ‘SQL_Server_Id__c’. If any one record will fail then none of contact record will be upserted. You may want to perform partial operation where if any record fails then it should not rollback other records processing. In this scenario Database.upsert comes to rescue. Below code snippet shows how we can use Database.upsert and how to iterate through errors.

List<Contact> lstContact = new List<Contact>();
Contact con = new Contact (lastName = 'Zaa', SQL_Server_Id__c='3',firstName='Jitendra');
lstContact.add(con);
//.. Other Contact records added in List
Database.UpsertResult[] results = Database.upsert( lstSGAccOppInsert, Contact.SQL_Server_Id__c.getDescribe().getSObjectField() ,false ) ;

for(Integer i=0;i<results.size();i++){
    if (!results.get(i).isSuccess()){
        Database.Error err = results.get(i).getErrors().get(0);
        System.debug('Error - '+err.getMessage() + '\nStatus Code : '+err.getStatusCode()+'\n Fields : '+err.getFields());
    }
}

How to insert parent and child record in single statement in Apex using External Id

As informed earlier in this post, we can use external Id of Parent record to insert parent and child in Same statement

//Create instance of Child record
Contact con = new Contact (lastName = 'Zaa', SQL_Server_Id__c='3',firstName='Jitendra');

//Create instance of Parenr record and only specify External Id, No Other fields
Account accountReference = new Account( Account_External_Id__c='21');                

//relate Child record with Parent
con.Account = accountReference;

Account parentAccount = new Account( Name='Cognizant', MyExtID__c='21');  

// Create the account and the Contact.
Database.SaveResult[] results = Database.insert(new SObject[] { parentAccount, con });

 

What if there are more than one records in Salesforce with Same external Id ?

In this case Dataloader or Apex will throw an error saying “Duplicate external id specified”.

Making external Id as a unique 

While trying to create External Id field, we have option to select whether field should be unique or not. If field already exists and we want to change it as unique external Id then it must be populated with unique values before making it unique.

Upsert using Command line Dataloader

You can read this post to know more about how to configure command line dataloader. For upsert, in config file “process-conf.xml” you need make below entry.

<entry key="sfdc.externalIdField" value="Master__r.External_id__c" />

and in field mapping file below entry

Id=Id
Master__r.External_id__c=Master__r\:External_id__c

Above relationship mapping is only applicable for upsert operations.

Important consideration for Upsert operation where external Id is not unique

To use upsert operation where external Id field is not unique, user performing operation must needs to have “ViewAllData” or “ViewAllRecords” permission else below error will be thrown :

System.SecurityException: ViewAllData or ViewAllRecords required to access external id fields which do not have a unique index

 

Posted

in

, , ,

by


Related Posts

Comments

15 responses to “All about Upsert and External ID in Dataloader and Apex – Videos”

  1. Reddy Penna Avatar
    Reddy Penna

    Hey Jitendra,

    Nice post.I am new(fresher) to Salesforce. I have a requirement where there is weekly update to Accounts and contacts. The update contains new records and updates to existing records. I am thinking of using upsert operation in Apex Data loader. Correct me If I am wrong.

    1. Jitendra Zaa Avatar

      Your understanding is correct Reddy.

  2. Vandana Avatar
    Vandana

    Hello,
    Thank you for a great post.
    In the second video here where you are able to import Contacts with relationship to Accounts using External ID, how did or what caused data loader to show “Account” in Step2b (i.e. Choose your related objects) while in the first case it only show “Reportsto” which we ignored. (At 2.41 min in the second youtube video). Is it because the csv file had Parent Account ID column? If yes should I be careful about what I name this column in the file?
    VK..

    1. Jitendra Zaa Avatar

      Hi Vandana,
      its because of 2 reason :
      1. I selected Upsert Operation
      2. There was external Id field in Parent object

      Thanks,
      Jitendra Zaa

  3. Ram Avatar
    Ram

    Hi Jitendra thanks for this post… I have a question on Dataloader please help me… Suppose i want to upload 1 lakh records of 10 users from SAP to salesforce, but the twist is Salesforce userid and SAP userid’s are in different format. How can we assign records to that user’s.

    1. Jitendra Zaa Avatar

      Hi Ram, create a new field in user and mark it as external id. Upload SAP field values on user record. Upsert record and use this field in dataloader wizard to match external id.

      1. Ram Avatar
        Ram

        HI, Jitendra…when i am trying to create a field on user in salesforce it showing an error like below :
        Error: User Custom Fields that are required must have a default value, what is It. We are creating external id field on User, but how can we map it. I am upserting Account object but external Id field is on User . How can i map it

      2. Ram Avatar
        Ram

        I got some clarification if it is wrong correct me… I need to create User_SAP_External_id__c field on User then i need to map SAP Ids with User_SAP_External_id__c and push the data into User object first.
        Then uploading account object , i need to use external id field User_SAP_External_id__c at the time of related objects selection on REPORTS To: option in the dataloader. Is it correct.

  4. Arpit Sharma Avatar
    Arpit Sharma

    Hi Jitendra,

    Thank you for your post.
    I have one question, if we get null from external system then how import process will behave.

  5. Mohd Tajamul Ahmed Avatar
    Mohd Tajamul Ahmed

    Hi Jitendra,
    I didn’t understand on why we need to create new instance “accountReference”, can’t we use “parentAccount” to relate child with Parent. Please clarify my doubt, Thanks.

  6. Ram ch Avatar
    Ram ch

    Hi jitendra i used code like below
    1.Inserting two records
    list acc = new list();
    public integer j=0;
    for(integer i=1; i<=2;i++){
    Order_Item__c a = new Order_Item__c();
    a.Name = 'cars'+i;
    a.External_id__c = '66889855'+i;
    a.Shipping_Customer_Number__c ='868686'+i;
    acc.add(a);
    j++;
    }
    System.debug('number ' + j);
    Database.UpsertResult [] cr = Database.upsert(acc , false);
    System.debug('total records ' + acc.size());
    2. list acc = new list();
    public integer j=0;
    for(integer i=1; i<=4;i++){
    Order_Item__c a = new Order_Item__c();
    a.Name = 'cars'+i;
    a.External_id__c = '66889855'+i;
    a.Shipping_Customer_Number__c ='999999'+i;
    acc.add(a);
    j++;
    }

    System.debug('number ' + j);
    Database.UpsertResult [] cr = Database.upsert(acc , false);
    System.debug('total records ' + acc.size());

    ———Here in second upsert i am inserting records but two are duplicates and changing Shipping_Customer_Number__c field value.
    Expecting result: First two records with new Shipping_Customer_Number__c field values and 3rd and 4th records will be inserted.
    But first two records are not updating with new Shipping_Customer_Number__c values why?
    Where i am doing mistake.

  7. Raj Avatar
    Raj

    Hi Jitendra,

    I am going nowhere with this issue, unable to figure out where i am going wrong.. Please help.

    C:\Users\Public\Documents\salesforce\DataloaderFullSandbox>ANT all
    Buildfile: C:\Users\Public\Documents\salesforce\DataloaderFullSandbox\build.xml

    all:
    [echo] Upserting Community_Event_Registrant__c
    [copy] Copying 1 file to C:\Users\Public\Documents\salesforce\DataloaderFul
    lSandbox
    [java] 2018-11-19 15:21:45,228 INFO [main] controller.Controller initLog (
    Controller.java:496) – Using built-in logging configuration, no log-conf.xml in
    C:\Users\rgoli\AppData\Local\salesforce.com\Data Loader 43.0.0\conf\log-conf.xml

    [java] 2018-11-19 15:21:45,251 INFO [main] controller.Controller initLog (
    Controller.java:498) – The log has been initialized
    [java] 2018-11-19 15:21:45,266 INFO [main] process.ProcessConfig getBeanFa
    ctory (ProcessConfig.java:104) – Loading process configuration from config file:
    C:\Users\Public\Documents\salesforce\DataloaderFullSandbox\process-conf.xml
    [java] 2018-11-19 15:21:45,518 INFO [main] support.AbstractApplicationCont
    ext prepareRefresh (AbstractApplicationContext.java:495) – Refreshing org.spring
    framework.context.support.FileSystemXmlApplicationContext@47db50c5: startup date
    [Mon Nov 19 15:21:45 EST 2018]; root of context hierarchy
    [java] 2018-11-19 15:21:45,693 INFO [main] xml.XmlBeanDefinitionReader loa
    dBeanDefinitions (XmlBeanDefinitionReader.java:315) – Loading XML bean definitio
    ns from file [C:\Users\Public\Documents\salesforce\DataloaderFullSandbox\process
    -conf.xml]
    [java] 2018-11-19 15:21:45,916 INFO [main] support.DefaultListableBeanFact
    ory preInstantiateSingletons (DefaultListableBeanFactory.java:557) – Pre-instant
    iating singletons in org.springframework.beans.factory.support.DefaultListableBe
    anFactory@13e39c73: defining beans [Community_Event_Registrant__c]; root of fact
    ory hierarchy
    [java] 2018-11-19 15:21:46,120 INFO [TemplatedCsvExtract] controller.Contr
    oller createDir (Controller.java:289) – Config directory already exists: C:\User
    s\rgoli\AppData\Local\salesforce.com\Data Loader 43.0.0\conf
    [java] 2018-11-19 15:21:46,123 INFO [TemplatedCsvExtract] controller.Contr
    oller initConfig (Controller.java:417) – Looking for file in config path: C:\Use
    rs\rgoli\AppData\Local\salesforce.com\Data Loader 43.0.0\conf\config.properties
    [java] 2018-11-19 15:21:46,127 INFO [TemplatedCsvExtract] controller.Contr
    oller initConfig (Controller.java:450) – User config is found in C:\Users\rgoli\
    AppData\Local\salesforce.com\Data Loader 43.0.0\conf\config.properties
    [java] 2018-11-19 15:21:46,760 INFO [TemplatedCsvExtract] controller.Contr
    oller initConfig (Controller.java:460) – The controller config has been initiali
    zed
    [java] 2018-11-19 15:21:46,765 INFO [TemplatedCsvExtract] process.ProcessR
    unner run (ProcessRunner.java:123) – Initializing process engine
    [java] 2018-11-19 15:21:46,767 INFO [TemplatedCsvExtract] process.ProcessR
    unner run (ProcessRunner.java:126) – Loading parameters
    [java] 2018-11-19 15:21:46,814 INFO [TemplatedCsvExtract] config.LastRun l
    oad (LastRun.java:96) – Last run info will be saved in file: C:\Users\rgoli\AppD
    ata\Local\salesforce.com\Data Loader 43.0.0\conf\TemplatedCsvExtract_lastRun.pro
    perties
    [java] 2018-11-19 15:21:46,859 FATAL [main] process.ProcessRunner topLevelE
    rror (ProcessRunner.java:251) – Unable to run process TemplatedCsvExtract
    [java] java.lang.RuntimeException: com.salesforce.dataloader.exception.Proc
    essInitializationException: Error creating output directory: H:\
    [java] at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRu
    nner.java:169)
    [java] at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRu
    nner.java:107)
    [java] at com.salesforce.dataloader.process.ProcessRunner.main(ProcessR
    unner.java:266)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcces
    sorImpl.java:62)
    [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMet
    hodAccessorImpl.java:43)
    [java] at java.lang.reflect.Method.invoke(Method.java:498)
    [java] at org.apache.tools.ant.taskdefs.ExecuteJava.run(ExecuteJava.jav
    a:223)
    [java] at org.apache.tools.ant.taskdefs.ExecuteJava.execute(ExecuteJava
    .java:155)
    [java] at org.apache.tools.ant.taskdefs.Java.run(Java.java:835)
    [java] at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:227)

    [java] at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:135)

    [java] at org.apache.tools.ant.taskdefs.Java.execute(Java.java:108)
    [java] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.ja
    va:292)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcces
    sorImpl.java:62)
    [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMet
    hodAccessorImpl.java:43)
    [java] at java.lang.reflect.Method.invoke(Method.java:498)
    [java] at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchU
    tils.java:106)
    [java] at org.apache.tools.ant.Task.perform(Task.java:346)
    [java] at java.util.Vector.forEach(Vector.java:1275)
    [java] at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.j
    ava:67)
    [java] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.ja
    va:292)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcces
    sorImpl.java:62)
    [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMet
    hodAccessorImpl.java:43)
    [java] at java.lang.reflect.Method.invoke(Method.java:498)
    [java] at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchU
    tils.java:106)
    [java] at org.apache.tools.ant.Task.perform(Task.java:346)
    [java] at org.apache.tools.ant.taskdefs.MacroInstance.execute(MacroInst
    ance.java:394)
    [java] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.ja
    va:292)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcces
    sorImpl.java:62)
    [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMet
    hodAccessorImpl.java:43)
    [java] at java.lang.reflect.Method.invoke(Method.java:498)
    [java] at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchU
    tils.java:106)
    [java] at org.apache.tools.ant.Task.perform(Task.java:346)
    [java] at org.apache.tools.ant.Target.execute(Target.java:448)
    [java] at org.apache.tools.ant.Target.performTasks(Target.java:469)
    [java] at org.apache.tools.ant.Project.executeSortedTargets(Project.jav
    a:1399)
    [java] at org.apache.tools.ant.Project.executeTarget(Project.java:1370)

    [java] at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(De
    faultExecutor.java:41)
    [java] at org.apache.tools.ant.Project.executeTargets(Project.java:1260
    )
    [java] at org.apache.tools.ant.Main.runBuild(Main.java:849)
    [java] at org.apache.tools.ant.Main.startAnt(Main.java:228)
    [java] at org.apache.tools.ant.launch.Launcher.run(Launcher.java:283)
    [java] at org.apache.tools.ant.launch.Launcher.main(Launcher.java:101)
    [java] Caused by: com.salesforce.dataloader.exception.ProcessInitialization
    Exception: Error creating output directory: H:\
    [java] at com.salesforce.dataloader.controller.Controller.setStatusFile
    s(Controller.java:570)
    [java] at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRu
    nner.java:131)
    [java] … 46 more

    BUILD FAILED
    C:\Users\Public\Documents\salesforce\DataloaderFullSandbox\build.xml:56: The fol
    lowing error occurred while executing this line:
    C:\Users\Public\Documents\salesforce\DataloaderFullSandbox\build.xml:37: Java re
    turned: -1

    Total time: 2 seconds
    ___________________________________________________________________________________________

    Values on the left are headers from .csv i am trying to upsert and the right are values in custom object.
    #Mapping values
    Id=Id
    EvariantId=Master_r\:Registrant_Id__c
    First Name=First_Name__c
    Last Name=Last_Name__c
    Event Name=Event_Name__c
    Event Date=Event_Date__c
    Status=Status__c

    ______________________________________________________________________________________________

    My target

  8. Anonymous Avatar
    Anonymous

    Hi Jitendra,

    Thank you for this elaborate post!!
    I have a query here, if I don’t want to make my external ID field unique, so is there a way in which we could upsert record without facing any error?

    PS: I won’t be able to use any other external Id field.

  9. Visaram Solanki Avatar
    Visaram Solanki

    I have created External_Id__c on User object and using value of this field in Owner:External_Id__c field of Account.csv and Region.csv files.
    It is working fine for Account object when doing upsert operation in data loader. However it does not work for Region.csv as it is custom object. I know i can use salesforce id of user record alternatively. However I am looking for solution where I do not need to open and update csv file. Please let me know if you have any idea.

  10. Rakesh Avatar
    Rakesh

    Hi Jitendra,
    I have a requirement of upserting a record in which the account lookup should be populated using the external Id, and I’m using dynamic apex, so have to use record.put(fieldApiName, value), while using this I’m not able put the value of account as it is throwing error “Illegal assignment from Account to ID”, to avoid this if I normally get the record (not sObject) but the actual record and put record.fieldApiName__r = account, then it’s working fine, but dynamically, it’s not allowing us to put the record. Can you please suggest a work around for this? Thanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading