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). Continue reading “All about Upsert and External ID in Dataloader and Apex – Videos”

Using Dataloader and ANT to backup Salesforce data on FTP or SFTP server – Video

Tutorial on how to use command line dataloader to save exported Salesforce backup file on FTP or SFTP server

We will be using command line dataloader from Salesforce and ANT script to extract data from Salesforce and backup on FTP server. I would recommend to go through this article first to get familiar with basics of commandline dataloader and followed by this article to get some insight that how we can use ANT with dataloader.

There can be hundreds of use cases where we need to backup data from salesforce and we have thousands of options. We can use Jitterbit, Mulesoft, Dataloader.io or some big ETL tools like BOOMI or informatica. However sometimes clients are not willing to pay hefty  amount on licensing cost of ETL tools and I love open source. In this article, we will be using open source solution to very common problem of backing up data on FTP server.

Complete code is available on my github account as well in case you need it.

Use Dataloader and ANT to back CSV file on FTP Server
Use Dataloader and ANT to back CSV file on FTP Server

Considering you know how to use command line dataloader, first challenge would be how to get it worked using ANT. Below code snippet show how we can create ANT macro to invoke dataloader to extract data. Continue reading “Using Dataloader and ANT to backup Salesforce data on FTP or SFTP server – Video”

Automate Command Line Dataloader using ANT with Dynamic arguments and SOQL query

As we know, there are multiple ways to load data inside Salesforce like using Workbench, Jitterbit, API etc… Salesforce also provides standard tool to load data into Salesforce using DataLoader. Dataloader by default supports User Interface or Command Line interface. Most of us already know that User interface based Dataloader needs manual interaction by end user. However if you want to automate data related operations, better choice would be Command Line Data Loader. Assuming you already have basic knowledge about how CLI Data loader works, I will go more advance in this article.

Automate Command Line Data Loader
Automate Command Line Data Loader

Following are basic steps for CLI Dataloader :

  1. Download and install DataLoader
  2. Generate Secret key file
  3. Generate Password on basis of Secret key generated
  4. Create “Process-conf.xml” (here is the problem)
  5. Create field mapping (sdl) file if required
  6. Execute Data loader from Command line

Continue reading “Automate Command Line Dataloader using ANT with Dynamic arguments and SOQL query”

Salesforce interview questions – Part 19

Salesforce interview questions for developers and admins around Apex, Visualforce, getting Salesforce object name on basis of Id, Apex API limits

181. Lets consider your custom Object named “Training__c” has field “Trainer__c”. You have set some default value in that field. Will that default value apply to new record created by apex code ?
OR
How to make sure that record created from apex code should respect default value of fields ?
OR
Default value in field from Apex code.
Ans :

After API 20, it should automatically populate However there is known issue for same here, click here if it impacts you.
Workaround :
If Default value of field is not getting populated by Apex then we have to use “Dynamic Apex”.  Create instance of object from sObjectType like shown below:

Training__c tr= (Training__c) Training__c.sObjectType.newSObject(null, true);

//Check if Value in field "Trainer__c" is default value
System.assertEquals('Jitendra', tr.Trainer__c);

182. What is best practice to refer dynamic custom messages in Visualforce with multi-language support ?
Ans :
Using Custom Label or OutputField or InputField tag, Platform itself will take care of internationalization. However in some cases, Message needs to be dynamic at the same time it should also support muti-language. In Custom Label, we cannot save dynamic String.

Let’s assume we want to show message something like “DEVELOPERNAME is not authorized to access this page”.
Here, Developername should be dynamically changed in visualforce which supports multilanguage. For each developername, it is not feasible to create custom labels. So below workaround can be used :

Step 1 : Create a Custom Label with text “{0} is not authorized to access this page“. In every language, dynamic value should represented by {0}.

Step 2 : In Controller of Visualforce write something like this :

String developerName = 'Some DeveloperName';
String message = String.format(Label.DEVELOPERNA, new String[] { developerName });

Continue reading “Salesforce interview questions – Part 19”

Large Data Volumes (LDV) in Salesforce | FAQs | Interview Questions

You would like to go through this useful cheat sheet for “Query and Search” optimization, few of this is discussed in this post as FAQ.

171 : How Standard Fields and Custom Fields related information is saved inside Salesforce Database? Is every Standard and Custom Object is created as a different Database table?
Ans :
Salesforce is using Multi-tenant architecture, means many organizations (Tenants) are using same infrastructure. Salesforce Database saves Metadata Information in hundreds of table. Run time engine then generates organization specific query to get information about their organizations and Data from common table as shown in below diagram. Below Database tables are partitioned by Organization ID and generates virtual table specific to Org.

Salesforce Metadata Related Information in Database
Salesforce Metadata Related Information in Database

172 : As a Developer, how can you optimize SQL query to fetch data from Salesforce Database?
Ans :
As Salesforce doesn’t save data in traditional way. Data of all tenants are in common table, so traditional Query optimization query and technique will not work in this case, so there is no such tool available to optimize final generated SQL. We only have option to create SOQL which is optimized by custom inbuilt Force.com Query Optimizer.

In Summer14, Salesforce released Query Plan Tool to analyze how query is performing. With help of this tool, we can get an idea how we can change our query to perform better.


173 : When records are created in Salesforce, How it is queued for Indexing?
Ans :
If newly created records are equal to or less than 9000, then it will be indexed in 1 to 3 minutes. However if records are more than 9000, then servers perform bulk indexing at a lower priority, so processing might take longer. Continue reading “Large Data Volumes (LDV) in Salesforce | FAQs | Interview Questions”

Import User Quotas in Quantity (Unit) for Collaborative Forecasting

In Previous article we have seen that how to import Quota in Amount (Revenue) for Collaborative forecasting.  I got many request on how to upload Quota in Units, as in Forecasting tab you can see that there is option to see Forecast in Unit or Amount.

Salesforce Display Forecast as Quantity or Revenue
Salesforce Display Forecast as Quantity or Revenue

If we switch from Revenue to Quantity the Quota of user will show blank user. In this article we will upload User Quota in Quantity.

After Winter 14 release also salesforce has not provided any Out of the box tool to upload quota from User Interface. So in this article also, we will depend on Dataloader. Continue reading “Import User Quotas in Quantity (Unit) for Collaborative Forecasting”

Import User Quotas in Amount for Collaborative forecasting

In Customizable Forecast, we can add the Quota for user by navigating to User Page and going to Quota related list However in Collaborative Forecasting aka Forecast 3 the only way is using Data Loader.

1. Log-in to the Data Loader and use the Insert function.
2. Click “Show all Salesforce objects and select “Forecasting Quota (ForecastingQuota)”.
Note: Only Data Loader version 25 and above can be used for importing Quotas in Collaborative Forecasting
3. Select the import csv file and click Next (click OK in the pop-up that shows the number of records detected in the file).

Note: The import csv file should contain the following columns:

  • Currency ISO Code
  • Quota Amount
  • Owner ID
  • Quota Month (Start Date, In Date Format , 10/1/2013 – 1 Oct 2013)
Import Quota Using DataLoader
Import Quota Using DataLoader for Forecast 3

Continue reading “Import User Quotas in Amount for Collaborative forecasting”

Command line dataloader in Salesforce – Solve Common errors

How to use Command line dataloader in Salesforce with troubleshooting steps and answers to common errors

I had this article in my list for long time and today i got chance to share with you all. Data Loader is great native tool provide by the Salesforce to insert, upsert, update, export and delete data. Standard Data Loader wizard needs interaction however there are many scenarios where we need to perform these data loading tasks repeatedly like every night 1:00 AM (Nightly Services) or something. So, for these situations we can use standard data loader tool from Command line.

In this article, i will explain in detail on how to use Data Loader from command line.

The most important part of setting up Command line Data loader is to generate the encrypted password using utility provided by the Data Loader.

Commandline Data Loader tools
Commandline Data Loader tools

This directory contains two utility batch file : “encrypt.bat” and “process.bat” Continue reading “Command line dataloader in Salesforce – Solve Common errors”