Category Archives: DbAmp

Integrating with Force.com Platform using DbAmp: Part-03: Insert

Typically, when data is uploaded to Salesforce, it will be usually an upsert operation, but it is not uncommon to use insert operation. For e.g. if you are importing a fresh set of data or importing data for new object(s), there is no need to do an upsert and insert is a perfect candidate for these kind of scenarios.
The insert operation using the DbAmp can be done either directly against a Salesforce org or can be done locally in a staged database and then can be inserted into Salesforce org. The main advantage for using the staged database is that while the direct access method is very slow, especially for bulk uploads, the local access method (the staged database) is much faster. Also, the direct access method affects the governor limits at a faster rate when compared to local access method, since in the direct access method, each record inserted count against the API limit, while in the local access method, even without using BULKAPI switch, it takes 200 record inserts to count against the API limit. We will cover both the scenarios here and demonstrate them with couple of examples.
Direct Access Method:
It’s very simple to use the direct access method. The following statement inserts new Contact record in the Contact object:
INSERT INTO sforce contact ( LastName) VALUES (‘TestLastName’ )
As you may guess, the auto-populate system fields such as the Id, CreatedBy, CreatedDateTime, LastModifiedBy, LastModifiedDateTime fields should be left blank. These fields will be auto populated by the Salesforce itself.
Staged (Local) Database Method:
In the staged database method, a database is created locally in the SQL server and the data is inserted using a temporary table called load table. Here is the brief steps on accomplishing this:
  • Create a database in the SQL server (one-time task)
  • Replicate / Refresh the table that you want to insert the record(s)
  • Create the load table. The load table is a physical table, but temporary in nature. This load table should include the following fields
    • Id, Error and any fields that are necessary or that you want to populate.
      • The ‘Id’ field should be left empty and should not be populated. When insert succeeds, this field is populated with the ‘Id’ returned by the Salesforce.
      • The ‘Error’ field is populated by DbAmp with the status of the insert operation.
  • Populate the load table
  • Insert the data from the table into Salesforce using the SF_BULKOPS stored procedure.
Note that the SF_BULKOPS is one of the several stored procedure that ships with the DbAmp. We will cover the options, nitty-gritty’s of this stored procedure in a separate blog post.
Now, let’s look at an example of doing insert with the staged database method. The following script populates a list of contacts in the load table and then does the SF_BULKOPS to insert those contacts into the Salesforce.
EXEC SF_REFRESH ‘SALESFORCE’, [Contact]
CREATE TABLE [dbo]. [Contact_Load]
  (
    [Id] [nvarchar] ( 100 ) NULL,
    [Error] [nvarchar] (4000 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LastName [nvarchar] ( 100 ) NULL
   )
INSERT INTO Contact_Load ( LastName) VALUES ( ‘Smith’ );
INSERT INTO Contact_Load ( LastName) VALUES ( ‘Adams’ );
INSERT INTO Contact_Load ( LastName) VALUES ( ‘Paul’ );
 
EXEC dbo . SF_BulkOps @operation = ‘Insert’, @table_server = ‘SFORCE’ , @table_name = ‘Contact_Load’
Let’s analyze what this script does.
  • The first line ‘EXEC SF_REFRESH ‘SFORCE’, [Contact]‘ refreshes the contact table from the Contact object in the Salesforce. The ‘SFORCE’ is the linked server name and the ‘[Contact]’ is the Salesforce Contact object. The reason that we have to refresh always before doing any CRUD operation is because, there could be many changes made between the last time the Contact object was refreshed and now. The SF_REFRESH brings the delta changes from the Salesforce Contact object to the local contact table in the SQL server.
  • The ‘CREATE TABLE [dbo]. [Contact_Load]…‘ creates the load table. As described previously, this table SHOULD have at least two fields which is ‘Id’ and the ‘Error’.
  • The ‘INSERT INTO Contact_Load…‘ statements inserts the records into these table. In the ideal world, the load table will be populated from a SELECT query that you will use to select the records from your tables.
  • The line ‘EXEC dbo . SF_BulkOps‘ is what actually inserts the data into Salesforce.
When you execute the script and the execution completes successfully, the contacts would have been inserted into the Salesforce Contact object. The ‘Id’ field will be populated with the Id for these new records and the ‘Error’ field will be populated with the status message. The older versions of the DbAmp used to leave the field empty, but somewhere in the documentation, I read the latest version of  DbAmp populates this field for even successful operations. Note that if you accidentally populate the Id field in the Contact_Load table with the existing record, the SF_BULKOPS will not detect that this is an existing record and will always insert this as a new record in the Contact object. In other words, if you have any records in the Contact_Load table, the SF_BULKOPS procedure will treat it as new records to be inserted and will do so.
Using Bulk API for INSERT
Salesforce.com provides two different types of APIs for sending (Insert, Upsert, Update, Delete) data to Salesforce.com. All the commercial tools such as Dell Boomi, Snaplogic, Cast Iron, Jitterbit, DbAmp, Pragmatic Works Task Factory, etc… and the open source tools such as Mulesoft, dataloader.io and even the Salesforce.com’s Data Loader use the same API to send the data to Salesforce.com. There is no other back door to deal with the Salesforce.com. The first of them which is the Web Services API is what highly used in most of the scenarios. The Web Services API is a synchronous API and can sends records in a batch of 200. That means if you 1000 records, then it makes 5 API calls to send all the 1000 records. The web services API should cater to the most integration needs because in a typical integration scenario the data moved is usually in few hundreds to thousands. But there are situations where bulk data movement is necessary which may run in few thousands to few hundred thousands of records. For such scenarios, Salesforce has a different set of API called the Bulk API. The Bulk API can support upto 10,000 records in one single API call. It is also asynchronous in nature which means that when a bulk API is used, the call is queued and executed whenever the system has the resources, which is usually, quite sooner. It is the responsibility of the caller to query and get the status of the queued call. So, if you need to send 100,000 records, with bulk API, it would take only 10 API calls, while the web services API would take 500 API calls.
DbAmp supports both the APIs and uses the web service API by default. If you need to use the bulk API, then it can be specified as an option. For e.g.
EXEC dbo . SF_BulkOps @operation = N’Insert:bulkapi’, @table_server = ‘SALESFORCE’ , @table_name = ‘Contact_Load’
 
One important thing to remember is that when you use the bulk API, the ‘Error’ column will be updated with a token and not with the status and you can use this token to track the status. To get the status, use the following command.
EXEC dbo . SF_BulkOps @operation = N’Status’, @table_server = ‘SALESFORCE’ , @table_name = ‘Contact_Load’
Conclusion
This article explored the basics of using DbAmp to insert data from local SQL database to Salesforce. There are much more things can be done with the insert operation and complete details can be found under the DbAmp documentation. The next article will focus on using the DbAmp to update the existing data in Salesforce from local SQL server.

Integrating with Force.com Platform using DbAmp: Part-02: Basics

In the first part of the article series titled ‘Integrating with Force.com platform using DbAmp’, we got introduced to the DbAmp tool and its benefits. We also went through the fundamentals and learned how it works and components of the DbAmp. In this article, we’ll look at some basics of the DbAmp as this is very essential in developing the DbAmp Jobs to integrate with Salesforce. The official DbAmp documentation is an excellent source of information to learn about the basics and this article series will touch upon few topics that you can find in the documentation, but will not go into those topics in detail, as they have been already covered in that documentation.

Execution Types

When writing SQL queries to perform any CRUD operations against Salesforce, there are two ways a developer can choose to write them.

Direct Access:

The first method is to write the queries directly against the Salesforce. In this method, the developers use specific type of syntax to access the Salesforce objects and the queries are executed against the Salesforce in real-time. The format is described as follows:

LINKED_SERVER_NAME…OBJECT NAME.

For e.g. to select all records from the Account object, you simply execute the following command (assuming your linked server name is SFORCE)

   1: SELECT * FROM SFORCE...Account

This has few drawbacks; Firstly, the performance takes a big hit as it has to execute directly against the Salesforce. Secondly, this will have a big impact on the API call limit. The reason is if you use INSERT or UPDATE in this manner, every record that gets inserted or updated count against the API call.

Local Access:

With local access method, you first replicate the required objects and store it in your local SQL server database and you write your SQL query against this table for SELECT queries. For CUD queries, you create a load table (as explained in part-1). This method has couple of advantages: Firstly, the performance is much improved, because your SELECT queries run against local database and not against the Salesforce. Secondly, as a developer, you have the option of using bulk API. With bulk API, upto 10,000 records can be sent in one single call. Also, when using the SF_BULKOPS (without the bulk api option), DbAmp batches upto 200 records in one single call.

Object-Table representation

DbAmp supports both child to parent relationship queries and parent to child queries. When replicating the Salesforce objects from Force.com platform to the local SQL server database, DbAmp uses a special mechanism to flatten the Salesforce objects in the local SQL server database. Here is a list of things that happens when an object is replicated:

  • The DbAmp creates every table with a column titled ‘Id’ which is mapped to the Object Id. Note that Salesforce never displays the Id field in the object definition. You can check my earlier article on Object Id’s to learn more about it. DbAmp also makes this field as primary key.
  • For lookup fields, the columns are created with the same name and suffixed by ‘Id’. For e.g. the ‘CreatedBy’ field becomes ‘CreatedById’ column in the local table and DbAmp stores the record id of the record it is pointing to. For e.g when replicating Case object, the field ‘CreatedBy’ is created as ‘CreatedById’ column in the local Case object and if the case is created by the user ‘John Adams’ and if the ‘Id’ of the User object for this user is ‘005014A7CA434AC’, then the ‘CreatedById’ column in the local Case object will have the value of ‘005014A7CA434AC’.
  • When a parent object in a lookup or master-detail relationship is replicated, there will be one record for each of the record in the related list of the parent object. For e.g. If an Account object has 3 contacts, then there will be totally three account records. If there are multiple related related lists, then there will be one record for each of the record in each of the related list. For e.g. if an Account object has 2 contacts with 5 cases, then there will be totally seven account records.

Handling DateTime

The value for the datetime types are always represented as UTC in Salesforce. When the user sees a value for the DateTime field, the value is represented in the user’s timezone (configured in the user’s profile). When the DateTime fields in Salesforce are manipulated using DbAmp, it is converted into local timezone. This is handled by DbAmp automatically and users need not convert back and forth from UTC to local and local to UTC. This is a very important point to remember when dealing with DateTime fields.

Using Column Subset Views

The Salesforce API has a limit on 10,000 characters for the SELECT queries; hence objects with huge number of columns cannot be replicated or refreshed who cross this limit. The DbAmp documentation mentions that if field count is above 325, the API may return error. In my experience, we got this error when the field count went above 400. Anyways, DbAmp suggest that the object can be split and then queries and this is called as Column Subset Views. In this method, the developer writes a SELECT query with a specific form of syntax to split the objects. The syntax is as follows:

   1: SELECT * FROM <LinkedServerName>...<ObjectName>_ColumnSubset<StartingAlphabetPrefix><EndingAlphabetPrefix>

For e.g. to split the Case object into two tables, the following query can be issued.

   1: SELECT * FROM SFORCE...[Case]_ColumnSubsetAM

   2:

   3: SELECT * FROM SFORCE...[Case]_ColumnSubsetNZ

   4:

Note that the Case object is enclosed by square brackets. This is because, ‘Case’ is reserved keyword and any reserved keyword in the table/column names should be enclosed by square brackets. The above query will get all the records from the Case table and will create two separate tables, named ‘Case_SubsetAM’ which will contain all the columns which start with A to M, both inclusive and another table named ‘Case_SubsetNZ’ which will contain all the columns that starts with N to Z, both inclusive. The column subset view can be used with SF_REPLICATE stored procedure as well, as follows:

   1: EXEC SF_REPLICATE 'SFORCE', 'Case_ColumnSubsetAM'

   2:

   3: EXEC SF_REPLICATE 'SFORCE', 'Case_ColumnSubsetNZ'

Governer Limits

In order to work with Salesforce data, every tool in the market has to use the Salesforce API. There is no other shortcut or preferred treatment for certain vendors. To ensure fair usage, Salesforce restricts the resource usage by putting a limit on the API calls. The following list provides high level limits on the API:

  • SELECT/SF_REPLICATE/SF_REFRESH – 1000 records => 1 API call
  • UPDATE & INSERT – 1 record => 1 API call
  • SF_BULKOPS without bulkapi switch 1 API call for each batch of 200 records
  • SF_BULKOPS with bulkapi switch => 1 API call for each batch of 10,000 records

Summary

Understanding the fundamentals of DbAmp helps developer to write better queries against Salesforce. In this article we covered such fundamentals and in the next article we will explore how records can be retrieved from the Salesforce. For list of the articles in this article series, please check the first part of this article series.

Integrating with Force.com platform using DbAmp: Part-01: Introduction

Introduction
The Force.com platform is becoming a ubiquitous platform of choice in the cloud for many organizations to move their Sales, Service and custom cloud applications. These applications or solutions cannot exist in vacuum and in almost all cases, they need to tightly integrate with the enterprise data that is housed local on-premise network or even with other cloud services such as Windows Azure or AWS. For e.g. if an organization implements a sales cloud, then they need their user information from their internal active directory to load in the Contact object, the customer lists from their SAP or other system for Account or Contact objects. Similarly custom Force.com applications might require data from the organization’s internal systems. Integration is a big concern for the organizations as they grow their presence in the cloud and it’s numerous tools and services are flooding the market to integrate with any systems.
This article series will cover one such tool called DbAmp from the ForceAmp.com. The goal here is to demonstrate the capabilities of this tool and show how the common tasks such as Insert, Delete, Upsert, Update operations can be done using this tool to accomplish the integration between a SQL Server deployed in the on-premise network with the Force.com platform. All of these topics are discussed elaborately in the following articles:
  • Integrating with Force.com Platform using DbAmp: Part-01: Introduction
  • Integrating with Force.com Platform using DbAmp: Part-02: Basics
  • Integrating with Force.com Platform using DbAmp: Part-03: Insert
  • Integrating with Force.com Platform using DbAmp: Part-04: Update
  • Integrating with Force.com Platform using DbAmp: Part-05: Upsert
  • Integrating with Force.com Platform using DbAmp: Part-06: Delete
  • Integrating with Force.com Platform using DbAmp: Part-07: Using SSIS
  • Integrating with Force.com Platform using DbAmp: Part-08: SF_REPLICATE
  • Integrating with Force.com Platform using DbAmp: Part-09: SF_REFRESH
  • Integrating with Force.com Platform using DbAmp: Part-10: SF_BULKOPS
  • Integrating with Force.com Platform using DbAmp: Part-11: Under the hood
  • Integrating with Force.com Platform using DbAmp: Part-12: Common integration techniques
DbAmp
DbAmp is a simple, yet very powerful tool, that exposes the Force.com as another database to your SQL Server. It allows the developers to use their familiar SQL (and SOQL as part of Open Query) to do all the CRUD operations on the Salesforce objects. The data on the Force.com platform can be backed up completely to an on-premise SQL Server using DbAmp with very little programming.
Benefits
Complete backup of Force.com data
DbAmp can be used to backup the data from the Force.com platform completely to an SQL Server database located on-premise. It is not limited to any specific Salesforce edition or application or objects; it can backup standard and custom objects and even custom settings for all types of Force.com cloud, such as Sales, Service, Marketing, Custom clouds, etc. However it is not a universal Force.com backup tool, meaning, it cannot backup other Force.com features such as profiles, permission sets, groups, roles, etc., because it is only a data backup tool. But it does support both full backup and incremental backup for the data. Full backups enable to completely backup all the objects with all the records, while incremental backups refreshes only the delta changes from Force.com to local on-premise database.
Build reports
DbAmp allows developers to build reports that can use data both from local repository and Salesforce in both real-time and offline fashion using simple SQL SELECT queries. For offline, DbAmp provides stored procedures (which will be covered shortly) to bring the data from Force.com platform to on-premise. Developers
Integrate local and cloud
DbAmp can be used to integrate local on-premise applications with Force.com data in both directions. That means that data can be inserted or upserted or deleted in Force.com from the on-premise SQL database.
How it works?
As outlined previously, DbAmp exposes Force.com as another database to your SQL Server. It does this by providing a OLE DB Provider to Force.com. As you might know, the SQL Server can connect to other data sources (that can support OLE DB, such as another SQL Server or Oracle, and expose them as another database using Linked Server. The following diagram (taken from the above link) illustrates this idea:
 
A3-P1-001-LinkedServer
This allows the developers to use the DbAmp OLE DB Provider to create a linked server that can connect to Force.com. Once this linked server is created, now developers can use regular SQL queries to manipulate the data on the Force.com platform. Developers can also use SOQL directly with the SQL using the Open Query, but keep in mind that there are some limitations in using the SOQL with the Open Query. Check the DbAmp documentation for more details.  One important thing to highlight here is that DbAmp takes care of all governer limits, so a developer need not to worry about governer limit exceptions when he/she tries to upsert with 5000 rows/records. DbAmp even allows developer to specify the limit per operation so that the developer can precisely control how the data should be batched.
Components
Now that we have understood how DbAmp works, let’s see the components of the DbAmp to better understand about how DbAmp works.
DbAmp OLE Db Provider
The DbAmp OLE Db Provider is used to connect to the Force.com by the linked server configuration. This is the engine of the DbAmp and takes care of all the background processes such as converting the SQL to appropriate Force.com web services API, sending the data from on-premise to Force.com and bringing the data back to on-premise from Force.com, etc.
DbAmp System Stored Procedures
The DbAmp system stored procedures does the bulk work to backup completely or incrementally. It also does the bulk inserts, deletes, updates and upserts. Here are those stored procedures.
  • SF_REPLICATE – The SF_Replicate stored procedure creates a new table which is an exact replica of a Force.com object and imports every single record from that object into the local, similarly named table. If this table already exists, it takes a temporary backup and drops and recreates this table.
  • SF_REFRESH – The SF_Refresh stored procedure synchronizes the local table with it’s corresponding Salesforce object. It deletes the records in the local table if these records got deleted in the Salesforce and gets any all the records that was added in Salesforce and doesn’t exist locally and the records whose fields got updated in the Salesforce.
  • SF_BULKOPS – The SF_Bulkops stored procedure is the key stored procedure that is used to send any new records or updated records in Force.com and even to delete any existing records.
There are few other stored procedures that ships with DbAmp, but these are the most widely used stored procedures. In a later article in this article series, we will explore these three stored procedures in depth to gain deep understanding about how it affects the way a developer designs/develops the integration job.
Anatomy of a DbAmp Job
Using DbAmp to integrate on-premise data with Force.com requires SQL skills, as the developers will write their integration entirely using SQL. The DbAmp integration can be either done using regular SQL jobs or SSIS packages. For the most part, this article series will be using only SQL jobs to implement the integration.
SQL Jobs
The anatomy of a SQL job that uses DbAmp slightly varies depending on the task involved and the time it runs. For uploads, we first refresh the local tables and populate a load table and then perform the upload operation (CUD). The load table is a temporary (physical, but temporary in nature) table that mimics the Salesforce object. The load table name should start with the name that is similar to the object that it deals; for e.g. to perform upload operation on ‘Case’ object, the load table name can be ‘Case_Load’ or ‘Case_BillingIssues_Load’, but it can’t be ‘ABC_Load’, etc. The load table need not have every column as in the Salesforce object and can have fields/columns that the job is going to affect. The only other column that is required is named ‘Error’ which will be used by the DbAmp to update the status information after the SF_BULKOPS call. The load table can be dropped or left as it is after the SF_BULKOPS call. In general, it’s a best practice to first check for its existence and create it if it doesn’t exist and to drop it just before the end of the logic in the SQL job.
At a high level the anatomy of a DbAmp SQL job look like as follows:
To upload data from local on-premise SQL database to Force.com platform for frequent jobs.
  • Refresh the local table(s) from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from local on-premise SQL database to Force.com platform for nightly jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’.
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from Force.com platform to local on-premise SQL database for frequent jobs.
  • Refresh the local table from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Perform CUD (Create, Update, Delete) operation(s) on your other databases retrieving data from the refreshed table(s)
To upload data from local on-premise SQL database to Force.com platform for frequent jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Perform CUD operation(s) operation(s) on your other database retrieving data from the refreshed table(s).
Conclusion
This article introduced the DbAmp, a tool to integrate local on-premise databases with Force.com data and discussed about the benefits and the underlying mechanism of how it works. It also discussed the basic constructs of the DbAmp tool and the anatomy of a DbAmp SQL job. In the next part, we will explore how the data can be inserted from local SQL database to Force.com using a DbAmp SQL job.
Stay tuned.

Demystifying Object IDs in Force.com platform

The demystifying series will take a single concept, idea, technology, standard, feature and will dissect it and provide a thorough understanding about that particular thing.

In Salesforce.com platform, the fundamental mechanism of storing data is through objects. Objects define the schema for storing related information for a particular ‘thing’. This ‘thing’ can be either standard objects such as Accounts, Contacts, Cases, Leads, Opportunities or custom objects that the users define to meet the business requirements. Objects can contain standard fields such as CreatedBy, LastModifiedBy and custom fields that the users define. A record is an instance of an object and every object can have zero or more records. Every record has a unique identifier represented by a standard field named ‘ID’. The subject of this blog post is all about this ‘ID’ and how it plays an important role in handling the objects and the relationships.

Note: I’m going to use ‘ID’ (upper case) to denote the ID type, while ‘Id’ (proper case) to denote that it is a value.

ID

The ID field is defined automatically by the platform whenever an object is created. The ID field is indexed and it is never shown in the field definition, hence this field cannot be edited or deleted. The ID field acts like a primary key for each record, hence every record can be uniquely identified using an ID. The value of this field for a record cannot be updated either through UI or through the web services. The IDs are unique within an organization and they are represented as either 15 digits or 18 digits. The 15 digit ID is base-62 encoded and is case sensitive and contains alpha-numeric characters (0-9,a-z,A-Z). The 18 digit ID is base-62 encoded and is case-insensitive and contains alpha-numeric characters (0-9,a-z,A-Z). The 18 digit IDs are introduced since some applications like Microsoft Access/Visual Basic are case-insensitive and do not distinguish between 00B30000007Mlka and 00B30000007MLKA.

The first 3 characters of an ID indicates what type of entity it references. For e.g. the following table list the first 3 characters for various entities:

# Entity Type Starts With
1 Groups/Queues 00G
2 Profiles/Roles 00E
3 Permission Sets 0PS
4 Object Records

Starts with a alphanumeric such as 5003, 0010.

The Salesforce UI always uses 15 digit IDs when viewing/editing records of the objects, users, roles, groups, profiles and permission sets, while web services always use 18 digit IDs. To get the 15 digit IDs from 18 digit, simply get the first 15 digits and ignore the last 3 digits. Salesforce recommends to use 18 digit IDs as the web service API’s always use 18 digits.

IDs in Relationship

The IDs are used as references field types in relationship fields. This means that when a lookup or master-detail relationship field is defined, it uses the ID to reference to the lookup or the master object that it refers to. There are subtle differences how the Ids are used in the ID fields and the reference fields.

# ID Type Fields Reference Fields
1 The Ids are generated by the platform and the user/developer cannot update the ID field The reference fields can be updated with the Ids from other objects
2 The ID fields are not displayed in the Field definitions The reference fields are always displayed in the field definitions
3 The Id of a record is always unique within an object. For e.g. every case record will have a unique id. The Ids in the reference fields are not unique. For e.g. the Last Modified By field can have the same user id
4 An object cannot have a null Id The reference fields in an object can be null for lookup objects, but in master-detail objects, the Id cannot be null

Relationship fields such as lookup or master-detail relationship fields are always defined in the child. When the user creates a new record, the platform automatically provides a lookup icon next to the field to choose the value from the related object. When the user select a value from the lookup dialog window and saves it, the platform saves a ‘reference‘ in the child object to the related object. This ‘reference‘ is the ‘Id‘ from the related object. But when you view this record, you would see the lookup value which is the record name (defined by the ‘Record Name’ during the object creation) and not the related object’s id.

Let’s understand how this works with an example. I’m going to use DbAmp for this example. DbAmp is an integration tool that exposes Salesforce as another database in your SQL Server. The advantage with this approach is that you can use regular SQL (not SOQL) to to all your CRUD operations. I’m not going to show how to setup the DbAmp as this is not in scope and the assumption here is it is already setup and ready for use.

For this demonstration, I created a new object named ‘Position’. You can see that the Position object definition screen doesn’t have the ID field. Also, note the standard lookup fields that are created for this object. These fields such as ‘Created By’, ‘Last Modified By’ are lookup fields. We will cover these fields shortly.

A3-P2-PositionDefinition(1)

Image 1: Position object definition

The following screenshot shows how the position record looks like when it is created. You can see that the ID field is never displayed in the UI (because it is not part of the field definition), but the URL has the Id which uniquely indicates this particular record. Actually, Salesforce always uses Id in the URLs to access the records for both viewing and editing. The highlighted fields indicate the lookup fields and in this case these fields are standard fields which gets created automatically when an object is created and populated by the platform every time a record is created.

A3-P1-NewPositionRecord

Image 2: A position record seen through UI

Let’s switch back to SSMS to see how this record looks like.

A3-P1-PositionRecord

Image 3: A position record seen through DbAmp

As previously said, DbAmp allows to manipulate records using regular SQL and the above command gets all records from the Position__c table (mapped to the Salesforce object Position__c). If you compare the image 1 and 3, you can find couple of differences. First, there is a difference in the field names; for e.g. Image 1 shows the field name as ‘Created By’ and ‘Last Modified By’, while the Image 2 shows it as ‘CreatedById’ and ‘LastModifiedById’. The reason is that every field has 2 fields to represent the field name;

  • Field Label – used in the page layouts
  • Field Name/API Name – used by the web services.

The standard fields use the ‘Field Name’ convention, while the custom fields use the ‘API Name’ convention. Nevertheless, the reason the Image 3 shows these fields as ‘CreatedById’ and ‘LastMofidiedById’ is because, DbAmp knows these fields are lookup fields, hence it appends the ‘Id’ to the column name.  Second, in the UI, the Id is 15 digits (which is displayed in the URL), but in DbAmp, the Id is 18 digits. The reason is that the web services always use 18 digit and DbAmp uses the web services behind the scenes to convert the SQL to web service API call to perform the CRUD operations. The point to note here is that the relationship fields use Ids as reference to link other objects and these Ids can be updated in the relationship fields, while the ID field cannot be updated.

IDs in CRUD

IDs play a crucial role in the CRUD operations. In fact, most CRUD operations use IDs in one way or another. The following sections details how the CRUD operations use IDs.

Create

As explained previously, whenever a record is created, the ‘ID’ field is auto-populated by the platform and the user has no way to update or delete. A record in Salesforce object cannot exist without an ID and it is always unique within the organization.

  • Create through Web Services: When the Create() method in the web service is called, it creates a new record and returns the 18 digit ID that the platform creates as part of the record creation.
  • Create through UI: When a new record is created through the Salesforce UI, it always displays the 15 digit ID.

Upsert

Upsert either creates new record if it doesn’t exist or updates if it exists. The upsert() call can be used in custom objects only if the object has an external field. When upsert() method is called, it uses the ID field to see if a record already exists. If it exists, then it updates; otherwise, it creates a new record and returns the 18 digit Id from the newly created record.

Update

Update can accept either the ID field or any other field in the condition list. But it cannot accept the ID field in the updateable list. If one or more Id is listed in the condition, the same number of records are updated, while if a non-Id is used in the condition list, it may update 0 or more number of records depending on how many records matches the condition.

  • Update through web services: Certain fields of an object (both in standard & custom objects) can never be updated such as ‘Created By’, ‘Last Modified By’, ‘Last Modified Date’, etc. The ID field is one such field. However, the reference (lookup & master-detail relationship) fields can be updated such as ‘OwnerId’.
  • Update through UI: Similar to web services, certain fields cannot be updated including the ID field, while the reference (lookup & master-detail relationship) fields can be updated.

Delete

Delete can accept either the ID filed or any other field in the condition list. If one or more Id is listed in the condition, the same number of records are deleted, while if a non-Id is used in the condition list, it may delete 0 or more number of records depending on how many records matches the condition.

Conclusion

The concept of ID is very simple, but not effectively understood many times. Understanding how the IDs are used in the Force.com platform helps developers to design and write applications in Force.com platform effectively.

%d bloggers like this: