Salesforce Data Operations

Salesforce is one of the leading customer relationship management (CRM) platforms that helps businesses improve customer relationships and drive overall business performance. The built-in tools in Salesforce allow businesses to generate leads, track and close more deals in one place.

Salesforce provides robust data management languages, such as SOQL and SOSL, for effective data governance and preparation. By mastering these languages, you can improve data accuracy and enable informed decisions.

This Salesforce data operations tutorial discusses in detail how to perform data operations in real-world Salesforce scenarios.

Table of Contents

• Overview
• Salesforce Object Query Language
• Salesforce Object Search Language
• DML Operations in Salesforce
• Frequently Asked Questions

Salesforce Data Operations - An Overview

Salesforce allows you to import, export, cleanse, maintain, and analyze data. SOQL and SOSL help you query and transform business data into accurate, consistent information.

By applying Salesforce DML operations, you can deliver scalable, secure data solutions to organizations. Specifically, you can provide data accessible to multiple teams and generate valuable insights for customer success.

Introduction to Salesforce Object Query Language (SOQL)

SOQL is a query language used in Salesforce and is very similar to SQL. SOQL allows you to retrieve records from Salesforce databases.

Let’s explore more about SOQL here.

• Characteristics of SOQL
  • SOQL queries are necessary for Apex Programming.
  • It minimises time and workload.
  • You can understand SOQL more easily than SQL.
  • It helps you optimize code.
  • SOQL is case-insensitive, so you can use both lowercase and uppercase letters in queries. 
  • It retrieves data from both standard and custom Salesforce objects.
• SOQL Syntax: Every SOQL query includes the following fields.
  • WHERE: You can use this field to specify a condition.
  • Object: The object field represents standard and custom objects.
  • OFFSET: It represents the starting row of the record.

Example:

You can use the ‘Select’ Keyword to retrieve data from objects and fields.

SELECT ID, Salary FROM Employee

Using the above SOQL Query, you can retrieve the ID and salary of the custom object called ‘Employee’.

Furthermore, you can use Application Programming Interface (API) names, which are system-defined names for fields in SOQL queries.

In standard objects, field names represent the API names.

If you want to gain hands-on expertise in Salesforce and master real-time data operations, consider enrolling in a comprehensive Salesforce training program that covers SOQL, SOSL, and DML in depth.

• How to write a SOQL query?

SOQL queries are usually enclosed in square brackets.

The below SOQL Query returns records from the database where the salary field equals ‘50000’

Employee e = [ Select ID, Name from Employee Where Salary='50000']

Here, 'e' stores the ID and employee fields for records where Salary equals '50000'.

• Using the WHERE clause in SOQL

You can use the ‘WHERE’ clause to return records based on a condition.

Example:

SELECT SID FROM Student WHERE Percentage>85

In the above SOQL Query, you use the ‘WHERE’ clause to retrieve the selected data from the ‘student’ object. The ‘WHERE’ clause applies the ‘Percentage>85’ condition to filter the records in the ‘student’ object.

• Using Comparison Operators in SOQL

You can use comparison operators in SOQL to compare two values and return TRUE or FALSE. 

Let’s describe various types of SOQL operators here.

1. 1. Equals(=) Operator
      
      The query below demonstrates how to use the Equals (=) operator.

      Select S_Name, S_ID FROM Student WHERE S_Percentage="80."

      
      This query retrieves the S_Name and S_ID of students with a Percentage of 80.
      
      
   2. Less-than Operator
      
      The following query uses the Less-than operator.

      SELECT S_ID, S_CLASS FROM Student WHERE S_FEE<10000

      This query returns the S_ID and S_CLASS of students whose fees are less than 10000.

   3. Greater than Operator
      
      The following query demonstrates the use of the greater-than operator.

      SELECT S_Name S_CLASS FROM Student WHERE S_FEE>10000

      This query retrieves the S_Name and S_CLASS of students whose fees are greater than 10000.

   4. Less than or equal to Operator
      
      The following query illustrates how to use the less-than-or-equal-to operator.

      SELECT S_Name, S_ID FROM Student WHERE S_Percentage <= 85

      This query returns the S_Name and S_ID of students with a percentage less than or equal to 85.

   5. Greater than or equal to Operator
      
      The following query demonstrates the use of the greater than or equal to operator:

      SELECT S_Name, S_ID FROM Student WHERE S_Percentage>=90

      This query retrieves the S_ID and S_Name of students with a percentage greater than or equal to 90.

   6. Like
      
      SOQL allows you to use the LIKE operator to retrieve records that contain the specified string. The following example shows the use of the Like operator.

      SELECT S_NAME S_ID FROM Student WHERE S_ID LIKE '00%

      This query finds records where S_ID contains '00'.

• ORDER BY Clause

In SOQL, you can use the ORDER BY clause to retrieve records either in ascending or descending order, depending on the given condition.

Salesforce supports two types of ORDER BY Clause, as follows:

• • ASC
  • DESC

Let’s discover them with examples.

• 1. ASC:
     
     You can use the following SOQL query to return records in ascending order.

     SELECT S_NAME S_FEE FROM Student ORDER BY S_FEE ASC

     Source Table:
     
     

     S_NAME	S_FEE
     Suresh	15000
     Ramesh	10000

      
     This query returns the following records.
     
     

     S_NAME	S_FEE
     Ramesh	10000
     Suresh	15000

      

  2. DESC
     
     You can use the following SOQL query to return records in descending order.

     SELECT S_NAME S_PERCENTAGE FROM Student ORDER BY S_PERCENTAGE DESC

     
     Source Table
     
     

     S_NAME	S_PERCENTAGE
     Suresh	92
     Rajesh	89
     Ramesh	95
     Rakesh	90

      The above query returns the following records. 
     
     

     S_NAME	S_PERCENTAGE
     Ramesh	95
     Suresh	92
     Rakesh	90
     Rajesh	89

      

• Logical Operators in SOQL

In SOQL, you can use logical operators to link one or two conditions in a single query. 

Let’s go through the logical operators used in SOQL.

• 1. AND
     
     The AND operator returns records when all query conditions are met, as shown below.

     SELECT S_NAME S_FEE FROM Student WHERE S_FEE>5000 AND S_FEE<10000

      

     S_NAME	S_FEE
     Ramesh	6000
     Suresh	7000
     Rakesh	9000

      

  2. OR
     
     In SOQL, you can use the OR operator to retrieve the data if any of the conditions present in the SOQL query are satisfied.

     SELECT S_NAME S_FEE FROM Student WHERE S_NAME =" VISWANATH" OR S_NAME=" RAKESH."

     
     The above query returns the following records. 

     S_NAME	S_FEE
     Rakesh	9000

      

In Summary, SOQL is a query language for retrieving data from the database. By using SOQL operators, you can perform various data manipulation operations.

SOSL in Salesforce

Salesforce developers use SOSL to construct text-based search queries against the search index. In other words, SOSL lets developers quickly find records or data by searching for specific words or phrases.

Let’s learn further about SOSL.

• By default, SOSL searches fields such as phone, name, and email depending on the search scope.
• It works on multiple object types simultaneously.
• The results list is always returned in the same order as it was specified in the SOSL query.
• SOSL statements return a 'list of lists' of sObjects. An sObject in Salesforce represents a record in a Salesforce database table.
• Each list contains the search results for a particular sObject type. An sObject type is a specific kind of Salesforce object, such as an account, a contact, or a custom object created by users.
• If no records match, SOSL returns an empty list for that sObject type.

Furthermore, you can execute SOSL queries in the following contexts.

• • Visualforce controllers
  • APEX Statements
  • REST or SOAP calls

 

How to write SOSL?

An SOSL query starts with the FIND clause. You can then add required clauses to filter the object type, data, and fields in the query. 

The format of an SOSL query is shown below.

Syntax for SOSL Query

FIND {SearchQuery}
[ IN SearchGroup ]
[ RETURNING FieldSpec [[ toLabel(fields)] [convertCurrency(Amount)] [FORMAT()]] ]
[ WITH DivisionFilter ]
[ WITH DATA CATEGORY DataCategorySpec ]
[ WITH SNIPPET[(target_length=n)] ]
[ WITH NETWORK NetworkIdSpec ]
[ WITH PricebookId ]
[ WITH METADATA ]
[ LIMIT n ]

[ UPDATE [TRACKING], [VIEWSTAT] ]

We’ll understand the different fields of an SOSL query.

The FIND field - It specifies the text (word or phrase) to search for. 

In Search Group - It specifies the scope of fields for the search. It defines the search in more detail. The possible values in it can be:

• Email FIELDS
• Name Fields
• Phone Fields

The Returning field - It represents the information to be returned in the search results. A list of one or more objects can be specified after this keyword. If you skip this, the results will include the IDs of all objects found.

The LIMIT field - It specifies the maximum number of rows returned by the query. If this is unspecified, the default is 200, the maximum number of rows that can be returned.

Differences between SOQL and SOSL

SOQL	SOSL
You can make a query only on one object at a time.	You can make a query on many objects at a time.
You can query on all fields of any data type.	You can query fields with data types including name, phone & email.
You cannot perform DML operations on query results.	You cannot perform DML operations on search results.
Return Type: List	Return Type: List Of Lists.

How to test SOSL queries?

You can perform test SOSL queries using the Anonymous Window.

Below is the step-by-step procedure for the same.

• Step 1: Log in to Salesforce → Developer Console → Press the bottom arrow of the window 
• Step 2: Select the ‘Query Editor’ tab
• Step 3: Place the code and execute.

SOSL Query Example

FIND {john) IN ALL FIELDS RETURNING Naukri_com_JobForm_C
FIND {john} IN ALL FIELDS RETURNING Naukri_com_JobForm_c(name)
FIND {john} IN ALL FIELDS RETURNING Naukri_com_JobForm_c, Levis_C
FIND {john} IN ALL FIELDS RETURNING Naukri_com_JobForm_c(First_Name_c), Levis_c(Price_c)
FIND {john} IN ALL FIELDS RETURNING Naukri_com_JobForm_c(First_Name_c), Levis_c(Price_c WHERE Price_c >1000)
FIND {john} IN ALL FIELDS RETURNING Naukri_com_JobForm_c(First_Name_c LIMIT 2), Levis_c(Price_c WHERE Price_c >1000)
FIND {john) IN ALL FIELDS RETURNING Naukri_com_JobForm_c(First_Name_c LIMIT 2), Levis_c(Price_c WHERE Price_c >1000 ORDER BY Price_c )

How to process SOSL in APEX Class?

Steps to Create SOQL Apex Class

Log in to Salesforce org → Developer Console → Ctrl + E → Write the code and execute.

Example

list<Levis_c > ListOfJean = new list<Levis_c >();
ListOfJean = [SELECT Price_c FROM Levis_c WHERE Price_c > 1000];
system.debug('The Result ='+ ListOfJean);

Output

12:54:26:007 USER_DEBUG [3] |DEBUG| The Result =(Levis_c:{Price_c=1200, Id=a066F00001MGfdVQAT})

How to declare a List of List Type Variable?

You can perform this operation as follows.

• list<sObject> → List
• list<list<sObject>> → List of List declaration

Example:

list<list<sobject>> ListofResults = new list<list<sobject>>();
ListOfResults = [FIND 'Blonde' IN ALL FIELDS RETURNING Naukri_com_JobForm_c(First_Name_c LIMIT 2), Levis_c(Price_c WHERE Price_c >
500 ORDER BY Price_c)];
system.debug('The Results=' + ListOfResults );

//add additional code for List Seperation of Output

list<Naukri_com_JobForm_c> ListA = new list<Naukri_com_JobForm_c>();
list<Levis_c> ListB = new list<Levis_c>();

ListA = ListOfResults[0];
ListB = ListOfResults[1];

system.debug('The Result in List A =' + ListA);
system.debug('The Result in List B =' + ListB);

Output

The Results=
(
(Naukri_com_JobForm_c: {First_Name_c=Blonde,Id=a006F000039j3UgQAI}
),
(Levis_c:{Price_c=1100, Id=a066F00001MGxGbQAL},
Levis_c: {Price_c=1400, Id=a066F00001MGxEzQAL},
Levis_c:{Price_c=1500, Id=a066F00001MGxGqQAL}
)
)

After Adding Additional Code for List Separation, the Output is:

14:42:56:127 USER_DEBUG [11] |DEBUG| The Result in List A =
(Naukri_com_JobForm_c:{First_Name_c=Blonde, Id=a006F000039j3UgQAI})

14:42:56:127 USER_DEBUG [12] |DEBUG|The Result in List B =
(Levis_c:{Price_c=1100, Id=a066F00001MGxGbQAL},
Levis_c: {Price_c=1400, Id=a066F00001MGxEzQAL}, Levis_c:{Price_c=1500, Id=a066F00001MGxGqQAL})

DML Operations in Salesforce

Salesforce supports data manipulation operations, including insert, delete, and update. 

The DML operations are implemented in the following way:

1. Insert Operation
   
   

   You can use the INSERT statement to insert new data into the database. Data can be added to any custom or standard object.

   Example

   Student newStud= new Student(Stud_Name='Rakesh');
   Try {
   Insert newStud;
   } catch(DmlException d) {
   Exception Code
   }

   The code above shows how to insert a new student's name. The try-catch block is used to check whether a student named Rakesh is already in the database.

2. Delete Operation
   
   You can use the delete operation to remove existing records for a standard or custom object. The records can be anything, such as employee or student details.
   
   Example
   

   Student[] oldStuds= [SELECT Stud_ID , Stud_Name FROM Student WHERE Stud_Fee<10000];
   Try{
   Delete OldStuds;
   } catch (DmlException d) {
   Exception Code
   }

   The code above deletes the records of students with fees less than 10000.

3. Update Operation
   
   This DML operation updates records in a standard or custom object.
   
   The following example shows how you can update an existing record:

   Student s = new Student(Name=" Rakesh")
   insert(s)
   Student myStud = [ SELECT Stud_ID, Stud_Name, Stud_Fee FROM Student WHERE Stud_ID= s.Stud_ID];
   myStud. Fee='10000';
   try{
   Update myStud;
   } catch (DmlException d) {
   Exception Code
   }

4. Upsert Operation
   
   You can use this DML operation to update the existing record and create a new record in a single statement.
   
   Example:

   Upsert vobject[optional_field]
   Upsert vobject[] [optional_field}

   The upsert statement matches records based on a specified field or the record ID.
   When you invoke the Upsert statement without specifying the domain, Salesforce uses the sObject ID to compare with existing records. If you provide the ID, it is used for comparison.
   For custom objects, include a custom field labelled 'external ID' that serves as a unique identifier for records. 
   For standard objects, you must specify a field with the idLookup attribute set to true, meaning the system can use this field to find records by value.

   • How Upsert selects Update or Insert
     
     Upsert inserts a new record or updates an existing one based on the sObject's ID.
     Conditions for Update or Insert:
     
     • A new record is inserted when no matching key is found.
     • When the key is matched more than once, an error will be generated, and the object record cannot be inserted or updated.
       
       
5. Undelete Operation
   
   You can use this DML operation to restore existing records for a standard or custom object.
   
   Syntax

   Undelete vobject Name

6. Merge Operation
   
   You can use this DML Operation to merge three records of the same object into one record.
   
   Syntax:

   Merge vobject vobject
   Merge vobject ID

   
   The first argument shows the superior record to which the other records are merged. 
   The second argument shows one or two records to be merged; after that, they can be deleted.
   In Salesforce, you can use DML operations to modify data and play a key role in handling and formatting data.

Frequently Asked Questions

1. I am a beginner. Can I learn Salesforce easily?

Ans: Yes, you can learn Salesforce easily. If you have some experience with data management and basic business processes, learning Salesforce quickly would be helpful.

2. What is data cleansing in Salesforce?

Ans: Data cleansing in Salesforce includes correcting or removing inaccurate and outdated data to produce high-quality data.

3. What is a data loader in Salesforce?

Ans: A data loader is an application you can use to perform bulk import, export, update, and delete operations on large volumes of data.

4. How would you ensure data security while performing data operations?

Ans: You can ensure data security by using role-based access control, encryption, and field-level security, and by applying data-handling best practices.

5. What is the salary range of Salesforce Developers?

Ans: Salesforce developers can earn a salary between 17 LPA and 19 LPA in India with 1–6 years of experience, according to AmbitionBox. They can earn between 83k and 166k USD in the USA, according to Indeed.

6. Can I get any online Salesforce learning resources?

Ans: Yes, the following Salesforce e-learning resources help you strengthen your understanding of Salesforce.

• Salesforce Tutorial
• Salesforce Interview Questions
• Salesforce Quiz

Conclusion

Performing Salesforce data operations enables you to manage, transform and maintain high-quality data across your organization. We hope this tutorial has provided you with a strong foundation in mastering SOQL and SOSL.

Are you curious to learn more about Salesforce data operations? You can check out a Salesforce course in MindMajix. By the end of the training, you will acquire the skills to work with SOQL and SOSL effortlessly and be prepared to become an industry-ready Salesforce professional.