Mastering Dynamic SOQL, SOSL, and DML in Salesforce: A Beginner’s Guide

Let me take you on a journey. Imagine you’re a manager of a large library with books scattered across countless shelves. Some visitors come asking for books by title, others by author, and some want a list of all available books in a specific genre. You can’t prepare every possible answer in advance. Instead, you need a system to adapt on the fly to their ever-changing questions. Salesforce provides this flexibility with Dynamic SOQL, Dynamic SOSL, and Dynamic DML—tools that help developers create applications capable of handling the unknown.

Through this blog, we’ll explore these concepts with relatable examples, understand their significance, and uncover how to use them effectively. Whether you’re just starting with Salesforce or looking to enhance your knowledge, you’ll find this journey enlightening!

The Power of Dynamic SOQL: Building Flexible Queries

Let’s start with Dynamic SOQL. SOQL (Salesforce Object Query Language) is like a search engine for Salesforce data. But sometimes, you don’t know what the user will search for until the last moment. This is where Dynamic SOQL comes into play. It allows you to create SOQL queries at runtime.

How Does It Work?

Dynamic SOQL queries are built as strings and executed in Apex code.

For example:

String query = 'SELECT Id, Name FROM Account WHERE BillingCity = \'San Francisco\''; List<Account> accounts = Database.query(query);

Here’s what’s happening:

• The query string is dynamically constructed using a city name.
• The Database.query method executes the query and returns the result.

Why Use Dynamic SOQL?

• Custom Searches: Build search functionalities where users can define their criteria.
• Dynamic Field Selection: Query fields or objects determined at runtime.
• Flexible Reporting: Generate reports based on user inputs.

Example: Imagine an app where users search for contacts by city. Instead of writing a query for each city, you use:

String city = 'New York';

String query = 'SELECT Id, Name FROM Contact WHERE MailingCity = \'' + String.escapeSingleQuotes(city) + '\'';

List<Contact> contacts = Database.query(query);

Preventing SOQL Injection

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Dynamic SOQL is powerful, but it has risks, like SOQL injection, where malicious users manipulate inputs. To safeguard:

1. Use String.escapeSingleQuotes() to sanitize inputs.
2. Use Database.queryWithBinds.

Map<String, Object> binds = new Map<String, Object>{'city' => 'Chicago'};

String query = 'SELECT Id FROM Account WHERE BillingCity = :city';

List<Account> accounts = Database.queryWithBinds(query, binds);

The Magic of Dynamic SOSL: Searching Across Objects

While SOQL focuses on structured queries for specific objects, SOSL (Salesforce Object Search Language) is like a global search—it scans multiple objects at once. Dynamic SOSL allows you to create these queries at runtime.

Example in Action

Imagine a visitor asks, “Show me anything related to ‘TechCorp’ across all records.” Here’s how Dynamic SOSL works:

String searchTerm = 'TechCorp';

String searchQuery = 'FIND \'' + String.escapeSingleQuotes(searchTerm) + '\' IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)';

List<List<sObject>> searchResults = Search.query(searchQuery);

Why Use Dynamic SOSL?

• Global Search: Search multiple objects (e.g., Accounts, Contacts, Cases) with a single query.
• Customizable Criteria: Adjust search terms dynamically.
• Efficiency: Quickly locate related records without querying each object separately.

Dynamic DML: When Updates Need Flexibility

Now that we’ve searched and retrieved data dynamically, let’s talk about DML (Data Manipulation Language), which helps us create, update, or delete records in Salesforce.

Dynamic DML is used when field names or record types are not predefined. This makes it ideal for scenarios where you need to modify records dynamically.

Dynamic Field Updates

Imagine an app where users select fields they want to update. Here’s how Dynamic DML handles it:

String fieldName = 'Industry';

String fieldValue = 'Technology';

Account acc = new Account(Id = '001xxxxxxxxxxxxxxx');

acc.put(fieldName, fieldValue); // Dynamically setting the field

update acc; // DML operation

In this example:

• The put method dynamically assigns a value to the field.
• The update operation saves the changes.

Bulk Dynamic Updates

Dynamic DML is particularly useful for bulk operations. For example, updating fields on multiple records:

List<Account> accountsToUpdate = new List<Account>();

for (Account acc : [SELECT Id FROM Account WHERE Industry = 'Finance']) {

    acc.put('Industry', 'Technology');

    accountsToUpdate.add(acc);

}

update accountsToUpdate;

When to Use Dynamic SOQL, SOSL, and DML

Here’s a quick comparison of when to use each tool:

Key Considerations for Dynamic Queries and DML

1. Governor Limits: Dynamic SOQL, SOSL, and DML are subject to Salesforce’s strict governor limits (e.g., 100 SOQL queries per transaction).
2. Security: Always sanitize inputs to avoid injection attacks.
3. Error Handling: Validate field names and object types to avoid runtime errors.

Wrapping Up: Why Master Dynamic Queries?

Dynamic SOQL, SOSL, and DML are tools that transform static applications into dynamic, user-friendly systems. They allow you to:

• Build flexible and scalable apps.
• Handle complex scenarios with ease.
• Provide users with tailored, real-time data experiences.

Think of these tools as the backbone of any dynamic Salesforce application. With a little practice, you’ll be ready to create apps that adapt to your users’ needs seamlessly. So go ahead, experiment, and unlock the true power of dynamic Salesforce development!