Category: SOQL

Difference between Static SOQl and Dynamic SOQl

Difference between Static SOQl and Dynamic SOQl

Salesforce Object Query Language (SOQL) is used to search records in your Salesforce organisation. SOQl queries are two types they are Static SOQL and Dynamic SOQl.

Static SOQl statements. 

Static SOQl query is written in [ ] (array brackets). It is good to use when you didn’t have any dynamic changes in the soql query. when the fields names or where conditions is needed to be defined dynamically we didn’t use static SOQL.

Dynamic SOQL

Dynamic SOQL refers to the creation of a SOQl string at run time with Apex code. Dynamic SOQL enables you to create more flexible applications. For example, you can create a search based on input from an end user, or update records with varying field names. This is the major difference between soql and dynamic soql.

  • Dynamic SOQL enables to create more flexible applications.
  • To create Dynamic SOQL query at run time use Database.query(); method.

To create Dynamic SOQL query at run time, use the database query method as shown below.

Difference between SOQL and SOSL in Salesforce

Difference between SOQL and SOSL in Salesforce.

SOQL in Salesforce.

  • soql stands for”Salesforce Object Query Language”.
  • It returns Records.
  • Records are stores in collection. Records are pertaining to single sObject.
  • Search in single sObject and it retains records.
  • SOQL retrieves the data from the database using “SELECT” keyword.
  • It works on multiple objects at the same time.
  • SOQL is not used in Triggers and can be used only in Apex classes and anonymous block.
  • SOQL against the same field will be slow.

SOSL in Salesforce.

  • SOSL stands for “Salesforce Object Search language”.
  • It returns fields.
  • Records are pertaining to different sObjects.
  • Search in entire Org and returns fields.
  • SOSL retrieves the data from the database using the keyword “FIND“.
  • It doesn’t work on multiple objects at the same time and need to write different SOQL queries for different objects.
  • SOSL can be used in Triggers and Apex class.
  • All fields are already text indexed.

Apex SOQL – SOQL IN Operator with example

In this Salesforce Object Query language SOQL tutorial, we are going to learn about IN operator in SOQL statements and why we use IN operator in WHERE clause.

SOQL IN Operator.

SOQL IN Operator is used to fetch the data from the matched values specified in the the SOQL statement. SOQL IN operator is mainly used to compare a value to a list of values that have been specified, and it retrieves the records if it matches the values specified in the list. The IN operator is used if you want to compare a value with multiple values to ensure the retrieved records are accurate.

SOQL IN Operator Example.

SOQL In Operator

From above SOQL query, the preceding query will return all users where the firstname name equals to ‘adarsh’ and ‘Prasanth’. As shown above the values for IN must be in parenthesis and string values must be added in between single quotes. IN and NOT IN operators are also used for semi-joins and anti-joins.

SOQL NOT IN Operator.

SOQL NOT IN operator is similar to NOT operator. This operator is used to specify multiple values in the WHERE clause for non matching and filtering records. This operator retrieve the data if the values does not equal to any of the specified values in a WHERE clause.

SOQL NOT IN operator example.

Output :

NOT IN Operator

As shown above, the result will not contain any user which equals to ‘Prasanth’. 

Conclusion :

In this Salesforce developer tutorial, we have learned about SOQL IN operator and SOQL NOT IN operator. In our upcoming SOQL tutorials, we learn about relationship between custom objects in SOQL.

Apex SOQL- Alias Notation in SOQL with Examples

Alias notation in SOQL statements are used to distinguish different object used in a single SOQL Statement. To establish the alisa, first we should identify the object and then specify the alias. In Salesforce SOQL, we have some reserved keywords which are not used as alias names they are AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, WHERE and WITH.

In this Salesforce Apex tutorial, we are going to learn about the usage of Alias notations in SOQL. Different types of operators used to filter the records retrieved vai the SOQL query. In this Salesforce developer tutorial, the object is Contact and it’s alias name is Acct.

Alias Notation in SOQL with example.

From the above code, Acct is the Alias name for Account object. Here we fetching Account Id and Name  of the Account object. We can also fetch the data without out alias notation.

Alias Notation in SOQL : Output.Alias Notation in SOQL with Examples

In Salesforce.com, using SOQL statement we can fetch data using Alias notation from different objects. Let us understand with an example.

As shown above, Con is the alias name for contact and Acct is the alias name for Account object.

Alias Notation in SOQL with Examples

Here we fetching firstname and lastname from two standard objects called Contact and Account. In Salesforce.com, we have account name called ‘GenePoint’. As Contacts are associated with Accounts, account name list will be available in contacts.

Alias notations

Conclusion.

In this Salesforce developer tutorial, we have learned about how to use SOQL Alias notations and how SOQL Alias notations are helpful to fetch data from multiple objects. In our next salesforce SOQL tutorial we will learn about SOQL IN operator.

Apex SOQL – Field expression Syntax

The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name. In this Salesforce tutorial, we are going to learn about escape sequences in SOQl, Data formats and Date literals used in SOQL statement.

SOQL defines several escape sequences that are valid in queries so that you can include special characters in your queries. You can escape new lines, carriage returns, tabs, quotes, and more. The escape character for SOQL is the backslash (\) character. Some of the escape sequences in SOQL are

SOQL Escape Sequences.

  \n or \NNew line
\r or \RCarriage return
\t or \TTab
\b or \BBell
\f or \FForm field.
\”one double quote character
\’Backslash
\_Matches a single underscore character ( _ ).
\%Matches a single percent sign character ( % ).

Escaped Characters Examples.

From above query, Select all accounts whose name begins with the three character sequences ‘Tut’.

It selects, all accounts whose name exactly matches the four character sequences ‘Tut%’.

It selects, all accounts whose name begins with the four character sequence ‘Tut%’.

Date literals and Date Formats – fielde expression.

Date literals in salesforce are used to compare a range of values to the value in the Date or dateTime field. When querying the record using the date field in SOQL statement, date literals are used and the each literal is a range of time beginning with midnight (00:00:00).

Some of the Date literals that can be used in SOQL statements are

 YESTERDAY
TODAY
TOMORROW
LAST_WEEK
THIS_WEEK
NEXT_MONTH
LAST_-MONTH

Date Formats used  in SOQL statements.

In SOQL statement, fieldExpression used different  date formats for different Date and DateTime fields in Salesforce. This Date formats in SOQL are used to filter on dateTime fields.

Date formats in SOQL are as follows.

FormatFormat SyntaxExample
Date onlyYYYY-MM-DD1999-01-01
Date, time, and time zone offset
  • YYYY-MM-DDThh:mm:ss+hh:mm
  • YYYY-MM-DDThh:mm:ss-hh:mm
  • YYYY-MM-DDThh:mm:ssZ
  • 1999-01-01T23:01:01+01:00
  • 1999-01-01T23:01:01-08:00
  • 1999-01-01T23:01:01Z

How to use Salesforce Developer Console – Learn

What is Salesforce Developer Console ?

Salesforce Developer Console is a browser based developing environment which is used to Create, test and debug application in salesforce organization. Using salesforce developer console we can and edit execute SOQL and SOSL queries.

What are the tools and features in Salesforce Developer Console.

Salesforce developer console has a menu bar, Workspace and Logs tab which contains several items like logs, Tests, Checkpoints, Query editor, Progress and problem. In Menu bar we File, Edit, Debug, Workspace and Help. 

  • File menu :- We can create new apex class, Apex trigger, visualforce page, Visualforce components, Static resource and many more.
  • Edit menu : Using edit menu we find text in the current view, Find / Replace. Find / Replace All / Search in files and fix indentation.
  • Debug Tab : We can open new window to enter apex code for testing using execute anonymous windows and can execute last Apex code.
  • Workspace : Workspace in developer console is a collection of resources represented by tabs in the main panel. Here we can create new workspace, can switch workspace, can rename current workspace and can delete workspaces.
  • Help : Here help documents, shortcut keys and preferences settings will be available.

How to use Salesforce Developer Console ?

  • To open Salesforce Developer Console navigate to Name | Developer console.

Salesforce Developer Console

Now developer Console will be opened with menu bar, Workspace and log menu. Using developer console in Salesforce we can create Apex Class, Apex Trigger, Visualforce page, Visualforce Component, Static resources, Lightning application and many more as show below.

Salesforce Developer Console

To create new Visualforce page in Salesforce developer console navigate to File | New | Visualforce page.

  • Now in Workspace enter Apex code and Save the Code.

Salesforce Developer Console

  • To execute apex code click on Preview button and a new window with visualforce page result will be opened in new window.

How to execute SOQL Query in Salesforce Developer Console?

  • To execute SOQL and SOSL queries in Salesforce developer console click on Query Editor tab.

SOQL query in Salesforce Developer console

  • A new workspace will be displayed to enter SOQL query as shown above.
  • Enter SOQL query.
  • Click on execute button.

As shown above SOQL query, All User name in the organisation will be extracted and displayed in the SOQL query result. For every function in Developer console, a new tab will be opened below the menu bar.

How to Enable Development mode in Salesforce

Development mode in Salesforce enables developers to embed Visualforce page editor in the browser. Enabling Development mode in Salesforce is very essential for developer to build visualforce pages in the view state. After enabling we can see both code and preview of the visualforce page in the same window.

How to Enable Development mode in Salesforce

  • Salesforce development mode can be enabled. Naviage to Name | Pesonal Advanced user.

Enable Development mode in Salesforce

In advacned user details, we can edit assignments, Can create new groups, Can create new Queue and can access Salesforce login history details like login Time, Source IP, Login Type, Login URL, Location. To enable Salesforce Development mode click on Eidt button.

Enable Development mode in Salesforce

  • Now go to user details section and Enable Development mode check box.

Enable Development mode in Salesforce

  • Click on Save button.

How check Salesforce Development mode.

To check development mode in Saleforce, naviaget to Setup | Build | Visualforce pages.

  • Now create a new visualforce page and quck Save the page.
  • Now click on Preview button and the result will be opened in new window.

VisuALFORCE PAGE

  • Click and drag the button. Now visualforce page code and result will be displayed as shown below.

Enable Development mode in Salesforce

What is ViewState in Salesforce?

ViewState in Salesforce holds the stage of the Visualforce page which includes components, field values and Controller state. Salesforce developer environment has a view state inspector, which let user to view the content of the view state. Performanc of the page depends efficiently managing the view state.

How to write first SOQL Query – SOQL getting Started

SOQL Statements and Salesforce Object Search language (SOSL) statements can be evaluated by surrounding the statement with square brackets [ ].  Salesforce Apex code contains many programming elements like Variable declaration, SOQL Query, Control Structure, Array (list), Data (DML) operation.

SOQL and SOSL queries are case-insensitive like Salesforce Apex. A SOQL query is the equivalent of a SELECT SQL statement and searches the organisation database. We suggest salesforce user to use Salesforce keywords in uppercase and fields in Lowercase.

When SOQL Query is used?

  1. SOQL queries is used to retrieve data from single object or from multiple objects.
  2. SOQL is used to count the number of records that meets the evaluation criteria.
  3. It is used to sort results.
  4. It is used to retrieve data from number, data and checkbox fields.
  5. Salesforce Object query language (SOQL) is used in the queryString parameter in the query ( ) call.
  6. In Apex statement.
  7. In visualforce controllers and getter methods.
  8. In the schema explorer of the force.com IDE.

How to write first SOQL Query in Force.com Explorer?

In Salesforce Apex coding, the API names of the object are required in SOQL. SOQL statements evaluates to a list of sObjects, a single sObject, or an Integer for count method queries. Before getting started with writing our first SOQL statements we need to install Force.com Explorer. We can also use third party tools to write and execute queries in Salesforce.com.

First SOQL Statement

How to write first SOQL Query

As shown above, Phone number and name for standard field of the Account object are extracted.

How to write first SOQL Query

As shown in above example, we fetching custom fields in the Standard Object. Here Name and Phone are Standard fields where CustomePriority__c is the custom field.

First soql statement using force.com explorer

As shown in above SOQL statement, Student__c is a custom object where State__c and College__c are custom fields. Student name , state and college details are retrieved from the custom object Student__c.