Can You Use DML or SOQL Inside the Loop?
A loop in Salesforce Flow is performed using a loop element that allows you to iterate through a collection of records or items. It is essential for performing actions or calculations on multiple items in a list, such as a group of related records or a set of inputs. This process often involves DML (Data Manipulation Language) and SOQL (Salesforce Object Query Language) to manage data and queries efficiently. Loops are critical in scenarios where you need to update statuses, send notifications, or apply changes across numerous records. To maintain performance and adhere to Salesforce governor limits, best practices recommend placing DML and SOQL operations outside the loop to minimize system load and maximize efficiency.
Key Components of a Loop:
- Collection Variable: The loop requires a collection variable that holds the records or items to iterate over. This could be a list of contacts, opportunities, or custom objects retrieved from a Salesforce query or manually created.
- Loop Variable: During each iteration, the loop assigns the current item in the collection to a single variable (often called a “current item” variable), allowing you to work with that individual record or item.
- Loop Path: The actions or logic to execute for each item in the collection are defined within the loop’s path. For example, you might update a field, perform calculations, or send an email for each record.
- Direction: Loops can iterate through the collection either from the first item to the last or in reverse order.
You retrieve a collection of open cases for a specific account and use a loop to: Update the status of each case to a different value, such as “In Progress” and “Working.”
Send an email notification to the case owner.
While there are situations where you may need to break the rule, DMLs and SOQLs (Create, Update, Delete, and Get) should be placed outside the loop.
What is the reason for this?
What are DMLs and SOQLs?
In Salesforce, DML (Data Manipulation Language) refers to operations that create, update, delete, or retrieve records in the database. These actions include Insert, Update, Delete, Upsert (Apex), Merge (Apex), and Undelete (Apex). DML operations allow you to interact with Salesforce objects (like Accounts, Contacts, Opportunities, or custom objects) to manage data programmatically using Apex code or declaratively through tools like Flows. They are subject to Salesforce governor limits, such as a maximum of 150 DML statements per transaction, which ensures system performance and scalability in multi-tenant environments. Proper handling of DML operations is critical for ensuring performance and avoiding errors like exceeding limits or creating orphaned records.
SOQL (Salesforce Object Query Language) is a query language in Salesforce used to retrieve data from Salesforce objects and related records. It is similar to SQL but specifically designed for Salesforce’s object-oriented database structure. SOQL enables users to select fields, filter records, and sort results, supporting powerful data retrieval through queries like SELECT Name, Id FROM Account WHERE Industry = ‘Technology’. It is used in Apex code, Visualforce, and Lightning components, as well as tools like Developer Console or Workbench. SOQL is subject to governor limits, such as a maximum of 100 queries per transaction, emphasizing the need for efficient query design in Salesforce development. The Salesforce Flow Get element, despite lacking some of the powers of SOQL statements in code, consumes one SOQL execution against the governors limit.
The Issue With Flow Loops
The problem with loops in flow is that you don’t know how many times it will iterate. Today you may have a maximum of 5 cases per account, in the future when the business grows and you start accumulating more history, you may have 200. If you want to ensure your flow design is resilient into the future, you should consider keeping your DMLs and queries (SOQLs) outside the loop.
Execution Governor’s Limits tell us that you can only perform a maximum of 100 SOQLs, and 150 DMLs in a Salesforce transaction. When dealing with a record-triggered flow, these limits apply to the Salesforce transaction as a whole. In other words, your transaction may include items listed on the 20 steps listed on this Order of Execution reference page. If you don’t want to cause errors, you should stay well below these limits in a single record-triggered flow.
That requires that you keep the create, update, delete and get elements outside your loop. Because if you put one get inside your loop and you iterate 101 times, you will receive the famous 101 SOQLs Salesforce error.
Best Practice Design
As stated above, the loop element in Salesforce Flow requires a collection as input. This collection can be a collection of any type, such as text or number, but it is often a record variable.
Remember: A variable is a container that temporarily holds values for us inside our flow. We use these values for further processing.
Let me explain the best recommendation design using the collection record variable as an example. The principle is the same for any type of loop collection. Let’s say we want to update several field values on individual cases in a collection.
How do we defer our DML and SOQL operations until we are done with looping. We need to use a container to hold the modified values, and perform our update outside the loop. To achieve this, do the following:
- Create a record collection variable: For this example, you need to create a new resource; a case collection record variable for your modified case field values (output).
- Loop your input case collection variable (this collection typically comes from a get element)
- Modify the field values for the current case in the collection using an assignment inside the loop.
- Add the current case in the loop record variable to the output case collection variable. This item in the collection holds the modified case record values now.
- Add an update element after your loop (outside). Point your update element to the output case collection variable. This update will perform all changes to the database in one single shot.
How many updates (DMLs) did this design use? Only one. It does not matter whether your loop iterates 10, 200 or 1,000 times. This design will only use one DML against your allotment of 100 per transaction.
The Exceptions
There are certain scenarios where you can break this rule. You become an expert by learning the rules, and then learning when and how to break the rules. You should still follow the best practice when you can.
A few scenarios where this rule can be ignored:
- You have the user modify records that they select from a list in a screen flow. The list is designed to show a max of 10 items.
- Your get element produces your input collection. The get element is set to retrieve a max of 10 items (new functionality).
- You have a scenario where the number of iterations are expected to be low. You introduce a decision and a counter inside your flow, and ensure that the iterations can not go over a certain number, for example 10.
- You are dealing with an action that involves a DML or SOQL, but it does not accept a collection as input. In this scenario, you should still limit the iterations with the use of a counter.
Performance Implications
Remember that regardless of the scenario, a single DML or SOQL are still faster than multiple. If you want to optimize your design for speed and performance, always try to complete your transaction in less number of DMLs and SOQLs.
Please comment and add your questions below.
Read other posts in this series, plus related content:
Salesforce Flow Best Practices
Can You After-Save When You Can Before-Save?
Transform Element and HTTP Callout for Random Test Data
#Collection #DML #Loop #Record #Salesforce #SOQL #useCase #Variable
