Introduction & Database Background
Welcome to the “Introduction to Salesforce SOQL” course! This course covers how to get Salesforce data out of the database using “Standard Object Query Language”, also known as SOQL. This is done using a tool such as Workbench and within Apex code.
Getting data out of the Salesforce database is one of the most important and common tasks a Salesforce Developer and Admin will perform. The data is often used to answer various business questions, like “what are my top 10 selling customers?”, “what are my potential duplicate records?”, and “how many customers are in Europe?”.
Let’s start with an overview of the Salesforce database and how the data is stored in Salesforce. This helps with understanding how to get the data out later. If you’re already familiar with the Salesforce database, skip to the next lesson.
What is a Database?
A database is a way of storing electronic information in a structured and organized way. It is designed to handle large amounts of data with many different users concurrently inserting, updating, deleting, and querying the data.
The data are stored in tables where each table has multiple columns and zero or more rows of data. The Salesforce database is a relational database where records in one Object (a database table) can be related to records in another Object.
What are Objects?
In Salesforce, an Object is a database table that has multiple columns available for a record of data. An Object can have zero or more records in it. A record is also known as a row of data in an Object.
Each Object is a particular representation of something, for example, the “Account” object stores company information. It has columns such as Name, Website, and Billing Address. Each Account record is typically a company that your company sells to. The Contact Object stores an individual’s information such as their name, contact information – plus the Account or Accounts they’re associated with and what their relationship is with each Account.
The Objects that are provided by Salesforce out-of-the-box are called standard objects, but you can also create your own Salesforce Objects. These are called a Custom Object and have their own columns as needed. You can also create custom columns on Standard Objects too. This makes the Salesforce database very customizable.
What Is a Field?
A field is a column on an Object that stores a single piece of data. Field and column are used interchangeably.
For example, the “Name” field on the Account object represents the company’s name. The “Website” field on the Account Object represents the company’s website. The “Billing Street”, “Billing City”, “Billing State”, “Billing Postal Code”, and “Billing Country” fields comprise the Billing Address for a company.
How Are Objects Related?
Objects in Salesforce are related to each other using “Relationship” fields. In a regular database, these are known as foreign keys. Salesforce provides two relationship fields, a master-detail relationship and a lookup relationship.
Both tie a particular record in one Object with a record in another object (and also sometimes to different records in the same Object). For example, the Account lookup field on the Contact object optionally specifies the Account record that this Contact is associated with.
A master-detail relationship is a required field that must have the id of the parent record when a record is created or updated. It is a required foreign-key field.
It provides a lot of functionality such as determining the record’s owner, when the record can be created, amongst other things (some that are out-of-scope for this course). You can have up to two master-detail fields on an Object.
A lookup relationship is an optional field that may or may not have the id of the parent record when a record is created or updated. It is an optional foreign-key field.
You can have up to 40 lookup fields on an Object depending on the Salesforce edition you have.
The Schema Builder is a browser tool within Salesforce’s setup that an Admin can use to view the Objects and the relationships between them – helping to build an Entity-Relationship Diagram (ERD). This is very helpful so you can see what Objects are available in the database and the relationships between them at a glance.
Opening the Schema Builder
To open the Schema Builder:
- Open Setup.
- Type “Schema” in the Setup’s quick find.
- Click the “Schema Builder” link.
Account & Contact Example
With only the Account and Contact Objects selected, this example shows that a Contact has a lookup to an Account. Lookups are in blue. An Account can have zero or more contact records tied to it. A Contact may have zero or one account associated with it.
It also shows that an Object can have a relationship with itself. This is called a self-referencing relationship. A Contact can be associated with another Contact via the “Reports To” lookup. One Contact may have zero or more reports, meaning they may report to another Contact or they may not.
Similarly, an Account record can be associated with another Account record via the “Parent Account” lookup. This is used to build the Account Hierarchy. Note: The Parent Account is a lookup even though it’s red.
Every record in the Salesforce database has at least two identifiers that can be used to identify it. One is the Record Id and the other is the Record Name.
The Record Id is a system assigned unique value that identifies a specific record in an Object. The Record Id is assigned when a record is created.
This value is 15 characters long and is case-sensitive by default. Each character can be the lower case letters a-z, the upper case letters A-Z, or the digits 0-9 – meaning each character in the value can be one of 62 possibilities.
Each Id is encoded with certain information in it:
- The leftmost three characters identify the object. For example, 001 is the prefix for all Account records.
- The next two characters identify the pod, also known as the Salesforce data center, where this record is stored.
- The sixth character from the left is reserved for future use.
- The remaining 9 characters are used for the record identifier. That means each Object can have up to 13,537,086,546,263,552 records in it.
The 15 character long Record Id can also be used to generate an 18-character long case-insensitive value. This is sometimes helpful when working with Ids outside of Salesforce and when comparing them with other Ids.
The Record Id is meant for system use and not directly by regular users since they are not very memorable.
The Record Name, also known as the “Name” field, is the human friendly record identifier that regular users use. It is typically either an auto-number field or a value assigned by someone such as the Account Name. The auto-number is generated automatically by the system when a record is created and is typically found on Custom Objects.
Every Salesforce Object has a Record Id. Almost every Object has a “Name” field. However, there are some exceptions such as the Task Object which uses a “Subject” for its “Name” field.
Account & Contact Example Data
Now, let’s look at some sample Account and Contact records to see how they look…
The Account Object has three fields: Id, Name, and Website.
- Id: Stores the system assigned id. (All Account record ids begin with 001.)
- Name: Contains the company’s name.
- Website: Contains the company’s website.
There are currently three Account records.
The Contact Object has five fields: Id, AccountId, FirstName, LastName, and Name.
- Id: The system assigned identifier. (All Contact record ids begin with 003.)
- AccountId: The id of the Account that this Contact is tied to. For example, John Smith is tied to Company A and Sarah Smithski is tied to Company B. Salesforce uses the Record Id and not the Record Name because the Record Name may change but the Record Id will not.
- FirstName: The Contact’s first name.
- LastName: The Contact’s last name.
- Name: The Record Name field for Contacts. It is a computed field that is formed by combining the FirstName and LastName fields.