Entries in sosl (2)


SOSL WITH HIGHLIGHTING (part of Spring '17)

I'm super excited about this update being released as part of Salesforce Spring '17 (works in both SOAP/REST API 39). I've been testing it out in my sandbox. Below is a simple example of the syntax:

FIND {salesforce} IN ALL FIELDS RETURNING Lead (Company, Description) WITH HIGHLIGHT

This functionality works on the following fields types (as of right now):


  • Email
  • Text
  • Text Area
  • Text Area (Long)


You can run this simple example in the Developer console and see that it returns the results with the <mark> tag around the term.

With this functionality, it is now pretty easy to search through a lot of record data for very specific things and return back a nice highlighted list of the results without having to use or build some form of custom control.




When to use SOSL vs SOQL in Salesforce

Both SOQL and SOSL are extremely powerful query tools in Salesforce but they have different uses. In this article we'll explore what these two query languages are best suited for and how to take advantage of them. First let's start out with some simple definitions.

SOQL (Salesforce Object Query Language)

If you are at all familar with SQL (Structured Query Language), SOQL is very similar to SQL in that you use to a SELECT commond to speficy a source object (example Lead), a list of fields to grab, and the conditions for selecting the rows.

Select Id, LastName, FirstName, Company, Email from Lead where email = ''

Just like any database, Salesforce uses indexes to make queries return results faster. SOQL and SOSL use different indexes.

What are some common indexes that can be used to make a SOQL query more efficient? Answer:

  • Primary Keys (Id, Name, Owner Fields)
  • Foreign Keys (lookup and master-detail relationship fields)
  • Custom fields marked as External ID or Unique - often times these are used to tie Salesforce records to external systems.
  • Audit Dates (i.e. LastModifiedDate)

Fields that CANNOT be indexed (and part of the reason SOSL is important) include:

  • Long text fields
  • Multi-select picklists
  • Binary fields (i.e. file, blob, encrypted text)
  • Currency fields (when multicurrency is enabled)

SOSL (Salesforce Object Search Language)

SOSL is used to construct text searches. SOSL's true power is that it enables you to search text accross multiple objects whereas SOQL is just a single object. Example: 

FIND {Salesforce General} IN Name Fields RETURNING lead(name, phone)

When to use SOQL
When to use SOSL
You know which object, fields you want to retrieve
You don't specifically know which object or field the data resides in but you need an efficient way to find the records
You want to get a count of the number of records that meet a specific criteria. i.e. where OwnerId = X
You want to get data from multiple objects and field in the most efficient way - the objects may or may not be related to one another.
You want to sort the results as part of the query
As described above when you are trying to query data in fields that cannot be indexed yet you have a large data
You want to get data from number, checkbox or date
You want to get data from a single object or multiple objects that are related to each other (i.e. Accounts and Contacts)