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.
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:
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
||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
||The ID fields are not displayed in the Field definitions
||The reference fields are always displayed in the field definitions
||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
||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.
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.
Image 2: A position record seen through UI
Let’s switch back to SSMS to see how this record looks like.
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;
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.
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 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 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 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.
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.