Understand SOQL



Objects are database tables that allow us to store information that is specific to Salesforce Org. Salesforce objects and fields can be compared to table columns and database tables.

There are standard objects and custom objects in salesforce org. Standard objects are the kind of objects that are provided by salesforce.com such as Accounts, Leads, Users, Contracts, Reports, etc.

Custom objects are objects created by the users. They provide specific information that is crucial to their business process. They are the core of every custom application. They provide an infrastructure for sharing data.

Let’s explore a few standard objects; If you take a look at your Salesforce Org and Go to App Launcher, find the Sales app and go to the accounts tab here, you see a few of the Accounts, and let’s go to all accounts here you can create a new account and just save it new account got created. Now, even though you have refreshed this page, this data will still be there because it’s stored in the object (Database Table).

If you go back to your accounts tab and click on all the accounts,

you can see your all account. So this is persistent data and this is stored in the Salesforce database. Click on Account “Edge Communication” and go on Detail Tab to see individual accounts and their fields.

And if you go back to our article https://infotechabout.com/web-technologies-1/ here we discussed how a Web application works, where we explained about the databases as well, and a database is used to store your data, and that’s what exactly happening here. All these records are stored in a database. So, there is an account table or account object in the database where we are storing all these rows or records. So, if you go to our salesforce set up and here, search for Object.

Let’s give it some time. We can see that our account is one of the objects and in Salesforce, the object is something similar to a table in the database.

And if we go to this account, we can also see all the fields of the account, and these fields correspond to the values or the columns in that particular record. So, here account name is also a field, then we have a field called rating, phone, fax, website, and there are so many other fields on the account object.

So, if you want to store our data and if you want to make our data persistent, then we need to use these Salesforce objects and we need to learn how we can query this data from these objects.  So like, if we go to this account tab, Salesforce is actually querying all the data from the account table or account object and showing all these records in this list view here.

But this is something happening behind the scene and we don’t know how Salesforce is querying all this data.

What is SOQL?

SOQL stands for Salesforce Object Query Language. SOQL can be used to access information in your organization’s database. SOQL is syntactically identical to SQL (Structured Query Language).

A SOQL query can be written and executed in Apex code, or in the Developer Console’s Query Editor.

How can we write our own query?

How can we retrieve all these records from the account object and maybe modify these records and then save them back to the database again? So, that’s what we’ll be learning in this article. We’ll be talking about SOQL, which is a Salesforce object query language, which is used to query the records from the Salesforce objects.

Doesn’t matter whether it is a standard object like account, contact, lead, opportunity, or maybe a custom object that you’ve created. So, just to give you a quick brief of how to retrieve this data from the account object, let’s go back to our developer console,

I’ll go to my file, and here I’ll click on Open resource. So, you will see a pop-up window like this.

And here we are going to search for our lead object first. So, type in Lead here, and you will see that there is a list item called Lead. Obj, and here obj stands for the object. So, this is our Lead object. You can double-click on it to open in your developer console and here if you want to retrieve some of the field values. So, let’s say you want to retrieve the Lead name, Company, and email from this Lead object. So what you can do, you can select the name, Company, and email from this list, and to select multiple fields or multiple list items here, you can press the control on windows and command on macOS and select these multiple fields and then click on the query button here.

For the first time, It’s going to open the Query Editor here, click on this Query button again, and then it’s going to write a query for you. So, this is a SOQL query that is going to retrieve the name, company, email, and others fields you selected from the lead object. Let’s click on this execute button and this is going to retrieve all the data or all the records from the Lead object.

So, here we see that there is a total of 22 Lead records, and that’s what we can confirm from our list view as well. We see 22 items here and we see the name of the Lead, Company, email, and selected fields. So this was just a quick overview to show you how a SOQL query works.

How to write your own query in SOQL?

Let’s explore to learn the SOQL from scratch and we will be building our own queries. We retrieved some records from the Lead object. So, let’s try another object “Account” to understand this SOQL statement.

First of all, we need to focus on these Red keywords. The first one is SELECT and the second one is FROM, every SOQL query must have these two keywords, where SELECT is the first keyword of each and every SOQL query, and FROM is a keyword that you use to define from which object you want to retrieve your records. So, you will definitely see these two keywords in each and every SOQL query. Between your SELECT and FROM a keyword, we have three more words here, ID, Name, and phone. And these are the fields or columns that we are retrieving for each and every Account record. Now, there is one more word left in this SOQL query, which is the last word called Account, and this is the object name. The word that you write after from keyword is called the object name and here, our account is the object because we are retrieving these records from the account object.

Now let’s understand it with the help of an example.

Here I have my Account object with four different fields or columns, the first field is Account Name, the second one is Phone, the third one is the Website and the fourth one is the Annual Revenue. Now, this account object, or Account Table, has three records. So, we can see that there are three different records in this table. And we want to retrieve these records using our SOQL query. So, let’s try to build a SOQL query for this account object. So first of all, we are going to have our two main keywords.

The first keyword is SELECT, and the next keyword is FROM. Now, between our select and FROM keywords, we are going to specify the columns or the fields that we want to retrieve. So, first, we want to retrieve the account name. So, we are going to write the Name here, then we want to retrieve the Phone, then the Website, and then the Annual Revenue. After these fields, we also want to specify, from which object we want to retrieve these records, and we need to specify that next after our FROM keyword. And since we are retrieving these records from the Account object, we are going to write Account here You may be noticing the name that I have given in my SOQL query are different than the name that I have in my table. 

For example, in my table, I have Account Name, but in my SOQL query, I have Name. In my table, I have Annual Revenue with a space in between, but in my SOQL query, I have one single word called AnnualRevenue without any spaces. So a couple of things here. The first thing is, that none of your column Name in your SOQL query can have any space in between. So, all of these should be single words, like the way we have specified AnnualRevenue without space. And the second thing is, in our Account table, the Account Name is a label, but its API name is Name. And I’m going to show you that in our Salesforce Org.

Like, why did I write Name here in my SOQL query but on the Account Object page it shows Account Name? Let’s open any of the accounts here. Let’s go to the details tab,

and we see that here, the label says Account space Name, and the Annual Revenue Field says Annual space Revenue. But if you go to the definition of this account object in the setup, so let’s go to the setup just by clicking on this gear icon and clicking on the setup here. And after that, let’s go to the object manager. And here we will see the Account object.

Click on the account object and then click on these fields and relationships, so we will notice that our field label is Account Name, but its field name is Name only.

We have another field called annual revenue and its label is Annual Revenue, but its field name is AnnualRevenue without any space. So, when you write a SOQL query, you need to write the field name instead of the field label.

If you write the field label, it’s not going to work unless your field label and the field name are the same, like the industry field. We have the same label and the same name. And for the custom object, this field name could be quite different. For example, we have a custom field called active under the account object and its field name is Status__c, where __c defines that it’s a custom field. So, always pick the field name.

So, you need to write Status__c in your SOQL query, instead of writing Status, or else it’s not going to work. So, I hope you made yourselves clear here. Now let’s go to our developer console and let’s try to write a SOQL query. Before that, let’s pick a different object this time, we are not going to write a SOQL query on the Account object, maybe let’s pick our lead object. Do we have any lead records? Yeah, we have some. So first of all, let’s go to our setup menu, and here we are going to open the lead object so that we can see the API name or the field name of the lead object. On the details tab, you will see the API name of your object.

So, this is what we are going to write after our FROM keyword, and let’s go to the field and relationships, and here we can see all the field names. So, let’s see what we want to retrieve from the lead object. Let’s retrieve the lead name, the company name, the email, and the lead status from the lead object. So, let’s go to the developer console.

remember, the first keyword that we need to write in a SOQL query is, SELECT. It doesn’t matter whether you write it in capital letters or small letters or if you use mixed cases, it’s going to work. So, I generally tend to write my keywords in capital letters and all other words in capitalized form. For example, if I want to write a name here, then I’ll keep my ‘N’ capital and the rest of the word in small cases. But my keywords will be in capital letters. And let’s write our second keyword as well, which is FROM. Now, since we are retrieving the data from the Lead object and we have seen that the API name of the Lead object is Lead. So, we are going to write from the Lead. Now here, between our SELECT and FROM keywords, we need to define the fields that we want to retrieve. So, we want to retrieve the Name, the Company, the Email, and the Lead status. So, let’s go to the setup and check the field name of all these fields.

For the name, where is it? So, here is our name field, and its API name also names, so we need to focus on this second column here. So, we need to write the Name, let’s copy it and paste it here. Name, so, yeah, I forgot to mention it, all the fields that you are going to define here, all need to be separated by a comma. So, that’s why I’m giving a comma after the Name field. The second field is Company. So, let’s find it here. And its field name is also Company again, let’s add a comma here and then the third field, which is called email. Where is it? and again, it has the same API name or the field name, so we can simply paste it here.

Once you are done with your query, we can execute it. And this is going to return some results and we have got 22 results here with the first column as the Name because we have defined Name here first. The second column is Company and the third is email. If you want to switch the position of these columns, you can, you know, switch it here in your SOQL query, maybe like you want to retrieve the email first and then the Company name.

So, we are going to remove the email from the end and add it after the Name. If we execute it again, then we will see that the email is our second column now. So, we have got 22 results and I guess we have got all our fields. We are going to make it more complex where we are going to, you know, limit the number of results that we are retrieving. For example, in this case, we are retrieving all the lead records. But what if we want to retrieve only those leads which are closed? so Wait for the next article.

If you have any queries feel free to comment or contact yousuf@infotechabout.com



Before reading this, you must read the article https://infotechabout.com/understand-soql/ for understanding salesforce objects or the Use of SELECT…


Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like