Getting Started with SOQL (2014)

Chapter 1. Introduction to SOQL

You will be introduced to SOQL in this chapter. This chapter will also discuss the API names of standard objects, custom objects, standard fields, and custom fields. These API names are used while querying using SOQL statements. This chapter explains when and where we use SOQL statements in Salesforce.

SOQL syntax will give us more information, such as reserved keywords in SOQL, how to write SOQL statements, and so on. We will get started by writing our first SOQL statement in this chapter.

What is SOQL?

Salesforce Object Query Language (SOQL) is used to build queries for fetching data in the Force.com platform. Just as we write a query in Structured Query Language (SQL) with some columns and a table, here, in SOQL, we write a query with some fields and an object. However, SOQL does not support all the features of SQL. For example, the * character in the SELECT statement denotes all columns in a table in SQL, but it cannot be used in the SELECT statement in SOQL. So, to retrieve all fields in SOQL, we have to mention all the fields separated by commas.

SOQL is case insensitive. For ease of use, we suggest you to maintain SOQL keywords in uppercase and fields in initial case (first letter in uppercase and the rest in lowercase). Throughout this book, all SOQL keywords will be written in uppercase and object names, field lists, conditions, and so on will be written in lowercase.

SOQL is very easy to understand if you have prior knowledge in SQL. As mentioned earlier, however, it does not support all the features available in SQL. If we think of tables as objects and columns as fields in Salesforce, writing SOQL becomes easier. Salesforce has standard objects (objects defined by Salesforce) and custom objects (objects defined by the user). The custom object ends with __c for identification purposes.

Good knowledge of SOQL helps us to optimize our code. If we are looking for data from different objects, SOQL helps us a lot in accomplishing that. Instead of writing complex code to achieve this, an administrator or developer with vast knowledge of SOQL may easily accomplish these kinds of tasks. The functions available in SOQL reduce our workload and save time.

The sample queries used in this book are real-time examples with step-by-step explanations. Beginners will gain confidence as we go ahead. Administrators and developers can also get ideas on how to optimize their code for faster execution of queries. An administrator can easily build any kind of complex report in an Excel file by extracting data from the objects using SOQL and delivering it to the clients in a timely manner if he or she has good knowledge of SOQL. SOQL eases the tasks of administrators, who are always looking for data.

A developer also faces many situations where they may have to write SOQL queries in Apex programming. If the developer has wide knowledge of SOQL, they can easily accomplish their task without reiterating again and again to form data for manipulation.

Make use of the tools available at Salesforce.com to execute the query instantly to clarify any doubts that arise. Salesforce provides tools, and third-party tools are also available. Steps with installation procedures and guidelines are available in Chapter 6, Tools with Installation Guidelines. The Developer Console can also be used for the easier and instant execution of queries.

To use SOQL, we need to know the API name of the objects. To know the API names of the standard objects in Salesforce, visit the following reference link provided by Salesforce:

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_list.htm

Since custom objects are user-defined objects, information about these objects will not be available under Customize in the Force.com setup.

Note

The API names of custom objects always end with __C.

The steps to get the API name of the custom objects change from environment to environment. In my organization, it is Setup | Build | Create | Objects, as shown in the following screenshot. We can view an object's API name on selecting it.

The Objects link displays all the custom objects available in our organization, as shown in the following screenshot:

The Employee link redirects us to the Employee object custom definition, as shown in the following screenshot:

The SELECT statement is used to retrieve data from objects. Relationships must exist among objects in case we want to retrieve data from two or more objects.

Note

It is not possible to write a single SOQL query to fetch records from two objects without any relationship among the two objects in Salesforce.

Relationship queries (queries for fetching records from more than one object) will be discussed in Chapter 2, Basic SOQL Queries.

Purpose of SOQL

The main purpose of SOQL is to fetch data from Salesforce objects. SOQL can be used in the following places:

· The queryString parameter in the query() call

· Apex statements

· Visualforce controllers and the getter methods

· The schema explorer of the Force.com IDE

SOQL syntax

Similar to SQL, SOQL also makes use of the SELECT statement to fetch data. Let us explore the following SOQL syntax:

SELECT fields

FROM Object

WHERE Condition

Ordering LIMIT

FOR VIEW Or FOR REFERENCE

OFFSET

UPDATE VIEWSTAT

The preceding query is explained as follows:

· fields: This denotes the API names of the fields of an object

· Object: This denotes the custom or standard object

· Condition: This is used for filtering records (optional)

· Ordering: This is used for ordering the result (optional)

· Limit: This is used for limiting the number of fetched records (optional)

· FOR VIEW: This updates LastViewedDate for fetched records (optional)

· FOR REFERENCE: This updates LastReferencedDate for fetched records (optional)

· OFFSET: This denotes the starting row for fetching (optional)

· UPDATE VIEWSTAT: This updates the articles' view statistics for fetched records (optional)

SELECT, fieldList, FROM, and Object are required. The others are optional in SOQL.

We should use the API names of the fields in the SELECT statement. We should not use the labels of the fields. The API names are available in the object definition. For Standard Fields, the Field Name column refers to the API name, and for Custom Fields, theAPI Name column refers to the API name.

To get the API names of standard objects in Salesforce, navigate to Setup | Build | Customize | Object | Fields.

Note

In the Force.com setup, we can get all the information related to standard objects in Salesforce by navigating to Build | Customize.

Let us see how to get the API names of the Account object fields. To get the API names of the Account object fields, navigate to Setup | Build | Customize | Accounts | Fields as shown in the following screenshot. The Standard object fields are present underCustomize and custom objects are present under Create | Objects in Salesforce.

In the Account Standard Fields section, the Field Name column refers to the API name of the standard fields, as shown in the following screenshot:

In the Account Custom Fields & Relationships section, the API Name column denotes the API name of the fields, as shown in the following screenshot:

To get the API names of custom objects in Salesforce, navigate to Setup | Build | Create | Objects, as shown in the following screenshot, and select the object:

This Objects link displays all the custom objects available in our organization, as shown in the following screenshot:

The Field Name column in the Standard Fields section denotes the API names of the fields, as shown in the following screenshot:

The API Name column in the Custom Fields & Relationships section denotes the API names of the fields, as shown in the following screenshot:

Writing your first SOQL statement

Before getting started with writing our first SOQL statement, we have to install a software to execute our queries. Salesforce offers a couple of tools to write and execute SOQL queries instantly. Salesforce also supports other third-party tools to write and execute queries. Let us write a simple SOQL query to fetch the IDs and names of accounts from the Account object.

Note

Account is a standard object in Salesforce. We use the Account object to store information about our customers and partners with whom we do business.

A sample query is given as follows:

SELECT Id, Name FROM Account

Refer to the following screenshot:

Here, Id and Name are standard fields of the Account object.

Note

Custom objects and custom fields always end with __c in Salesforce.

Let us see another example of how to fetch custom fields in standard objects. Refer to the following screenshot:

Here, Id and Name are standard fields and Active__c and CustomerPriority__c are custom fields.

A sample query is given as follows:

SELECT Id, Name, Active__c, CustomerPriority__c FROM Account

In the preceding example, we saw how to retrieve records from Account (standard object). Let us write a simple SOQL query to fetch records from a custom object. In this example, let us make use of a custom object, Employee__c, which has custom fields such asEmployee_Name__c, State__c, City__c, and so on.

A sample query is given as follows:

SELECT Name, Employee_Name__c, State__c, City__c FROM Employee__c

Refer to the following screenshot:

Here, Employee__c is a custom object; Name is a standard field; and Employee_Name__c, State__c, and City__c are custom fields.

Each and every object in Salesforce has system fields. System fields are read-only fields. The following is a list of system fields:

· Id

· IsDeleted

· CreatedById

· CreatedDate

· LastModifiedById

· LastModifiedDate

· SystemModstamp

Note

All system fields are not editable. Only a few system fields are editable. To get edit access to system fields, we have to contact Salesforce support.

The following table describes field names:

Let us see a sample SOQL query to fetch the system fields:

SELECT Id, Name, CreatedDate, LastModifiedDate FROM Account

Refer to the following screenshot:

Here, CreatedDate and LastModifiedDate are system fields.

Let us see another example to fetch FirstName and LastName from the User object.

Note

The User object is also another standard object in Salesforce. The User object stores all the information about the users in the organization. The IsActive field is used to check whether the user is active or inactive.

A sample query is given as follows:

SELECT FirstName, LastName FROM User

Refer to the following screenshot:

Let us see another example to fetch Name and StageName from the Opportunity object.

Note

Opportunity is an important standard object in the Sales application in Salesforce. Opportunity is a potential revenue-generating event.

A sample query is given as follows:

SELECT Name, StageName FROM Opportunity

Refer to the following screenshot:

Let us see another example to fetch Name and Status from the Lead object.

Note

Lead is also a standard object in Salesforce. Lead is used to store information about an organization or individual persons who are interested in our product. A Lead can be converted into a single Account, multiple Contacts, and multiple Opportunities objects.

A sample query is given as follows:

SELECT Name, Status FROM Lead

Refer to the following screenshot:

Let us see another example to fetch Id and Name from the Product object. The API name of the Product object is Product2.

Note

The Product2 object stores all the information about the products available in our organization.

A sample query is given as follows:

SELECT Id, Name FROM Product2

Refer to the following screenshot:

Let us see another example to fetch Id and Name from the Price Book object. The API name of the Price Book object is Pricebook2.

Note

Pricebook2 is another standard object in Salesforce. In the Pricebook2 object, we use stored information on the different prices of products.

A sample query is given as follows:

SELECT Id, Name FROM Pricebook2

Refer to the following screenshot:

Let us see another example to fetch ProductCode, Product2Id, Name, and UseStandardPrice from the Price Book Entry object. The API name of the Price Book Entry object in Salesforce is PricebookEntry.

Note

PricebookEntry is another Salesforce standard object. We store the list price of the product under the Pricebook2 object in the Pricebookentry object.

A sample query is given as follows:

SELECT ProductCode, Product2Id, Name, UseStandardPrice FROM PricebookEntry

Refer to the following screenshot:

Summary

In this chapter, we learned what SOQL is and got to know its purpose. While discussing the purpose, we saw where exactly we use SOQL statements in Salesforce development and administration.

We discussed the fetching of the API name of the custom object with detailed descriptions and steps. Moreover, we saw the steps for fetching API names of the standard and the custom fields. The usage of system fields and querying system fields, with a description of each system field, was provided in a table.

Basic syntax of SOQL statements with all reserved keywords was discussed. We also saw some examples for fetching records using the SOQL queries from standard objects and custom objects. Finally, methods to find the difference between custom objects and standard objects and custom fields and standard fields were introduced.