Searching the records by text in Salesforce is done using SOSL. With this, we can get the records from multiple objects. The disadvantage of using SOQL is that it searches the records in a single object/related object. In this guide, we will see how to query on Salesforce Database with SOSL in different scenarios with examples.
Salesforce Object Search Language (SOSL)
SOSL stands for Salesforce Object Search Language which retrieves all the records from one or more objects that match the text that is provided in the query. If the pattern matches, the relevant records will be returned. Before going to the SOSL queries, we need to know where these queries are executed in the Salesforce org. Let’s discuss them step by step.
1. Open the Salesforce org and click on the gear icon. Select the “Developer Console”.
2. A new window will open where you can write the SOSL queries in the Query Editor.
-
a. Click on the Query Editor.
- We can refer to the text/pattern/wildcards as SearchQuery in which the query fetches the records that match this SearchQuery.
- SearchGroup (optional) specifies the scope of the fields, like if you want to search on phone fields or email fields, you can specify the PHONE/EMAIL. It is ALL by default.
- If you want to check the text in particular field/s, you need to specify the ObjectsAndFields which refers to the fields of an object. Otherwise, it is optional. We can also specify multiple objects in this.
b. A Console will open under this to write the SOSL Query.
c. After writing the query, click on the “Execute” button.
d. If the query is fine without any error, the records with respect to the query are displayed at the top console.
e. If the query fails, the errors are displayed below the Query Editor.
SOSL
SOSL query starts with the FIND keyword followed by the SearchQuery.
Syntax:
Let’s see how to write an SOSL query.
Example 1: With Text
In this example, we will write an SOSL query to find the records that include the text like “Linux Hint” on all objects and fields.
Search Results:
The “Linux Hint” text is found in three objects with four records.
In “Contacts” and “Account” objects, the total records that matched with this text is one and the total records that matched with this text in the “Opportunity” object is two. The Id record is displayed for each record.
Example 2: With Multiple Texts
In this example, we will write an SOSL query to find the records that include the text like “Linux Hint” or “Java based” on all objects and fields. Here, we use the OR operator.
Search Results:
The “Linux Hint”/”Java based” texts are found in four objects. The total number of records is 5.
Example 3: With Wildcard – *
Let’s modify the previous SOSL query. Now, we add a wild-card character – * which gets the records that start with “Java”.
Search Results:
There is only one “Java based” record. It exists in the “Case” object.
Query on Specific Object
Until now, we learned how to find the records in all fields and objects. Now, we will see how to get the records in specific fields and objects.
Example 1: Single Object
In this example, we will write an SOSL query to find the records that include the text like “Linux Hint” only on the case object.
Search Results:
There is only one case with the “Linux Hint” text.
Example 2: Multiple Objects
Let’s perform a search on the “Case” and “Account”. The text is “Linux Hint”.
Search Results:
There is only one “Case” and one “Account” with the “Linux Hint” text.
Example 3: Returning Fields
Utilize the previous query by returning the Id, SuppliedCompany (field name) from “Case” object and Id, Account Name from the “Account” object.
Search Results:
We can see that the mentioned fields are displayed under the “Case” and “Account” objects.
SOSL – WHERE
It is possible to filter the records that are returned by the SOSL query by specifying the conditions with the WHERE Clause. This WHERE clause is specified along with the object fields.
Example 1: With Comparison Operator
In this example, we will write an SOSL query to find the records that include the text like “Account 1” only on the “Account” object only if the Industry (picklist field) is “Agriculture”.
Search Results:
There are three rows with the “Account Name” as “Account 1” and with “Industry” as “Agriculture”.
Example 2: With the IN Operator
Modify the previous SOSL query by changing the condition. Get the records if the “Industry” is in “Agriculture”/”Banking”.
Search Results:
There are four rows with the “Account Name” as “Account 1” and with the “Industry” as “Agriculture” and “Banking”.
Example 3: With the AND Logical Operator
Get the records that match the “Account 1” text where the “Industry” is “Agriculture” and the “Rating” should be “Warm”.
Search Results:
There is only one record with the name as “Account 1”, the “Industry” as “Agriculture”, and the “Rating” as “Warm”.
Example 4: With the OR Logical Operator
Get the records that match the “Account 1” text where the “Rating” is “Hot” or “Warm”.
Search Results:
There are three records with the “Name” as “Account 1” and the “Rating” as “Hot” or “Warm”.
Conclusion
Salesforce Object Search Language (SOSL) retrieves all the records from one or more objects that match the text/wildcard pattern that is provided in the query. In this guide, we learned the different scenarios by considering the text and wild-card patterns. Then, we discussed how to apply SOSL only on specific objects and return the particular fields. It is possible to filter the records that are returned by the SOSL query by specifying the conditions with the WHERE Clause. In this scenario, we discussed five examples with different operators.
