Jennifer Lee and Michelle Hansen in a new, live episode of "Automate This!"

Automate This! — Resolve Mixed DML Errors with Platform Events

By

Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by #AwesomeAdmin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Michelle Hansen eliminates those pesky Mixed DML errors in record-triggered flows by leveraging platform events.


Use Case

Our company currently uses Cases to track internal tickets, including New User requests. Due to an increase in requests for new users, the team has been looking for ways to streamline user creation. Since each new user is created with the same Read Only profile until they complete initial Salesforce training, the team decides to automate new user creation with Flow. Specifically, when a New User Case comes in, a record-triggered flow should create the new user and then close the Case. Sounds great, right? Well, right up until the dreaded “Mixed DML” error rears its head.

What’s the deal with Mixed DML?

If you’re new to Salesforce, or just not familiar with all the “lingo,” DML stands for Data Manipulation Language. This is how Salesforce Admins and Developers interact with the data inside a Salesforce org. Types of database manipulation include inserting, updating, deleting, merging, or retrieving records. A common error encountered by Salesforce professionals when getting started with automation is a Mixed DML error. DML operations on certain sObjects, sometimes referred to as setup objects, can’t be mixed with DML on other sObjects in the same transaction. Some examples of ‘setup objects’ include Users, Groups, Permission Sets, Queues, and Territories—keep in mind this is not a comprehensive list! The worst part is that this is one of those errors that can’t be corrected; the user is simply stuck and everything they tried to do gets rolled back.

Why does this happen? Well, this restriction exists because these sObjects affect the user’s access to records in the org. You must insert or update these types of sObjects in a different transaction to prevent operations from happening with incorrect access-level permissions. For example, you can’t update an Account and a user role in a single transaction because the update to the user’s role may impact access their to Account records, and the change to an Account possibly should no longer be allowed for that user as a result. If they happen in the same transaction, there’s no way to ensure proper security is maintained.

How do we fix this?

Because of the Mixed DML restriction, it’s not possible to complete all of these steps with a single flow. Instead, we need to break this apart into three separate transactions to handle the Case and User separately, which uncouples the manipulation of the ‘regular’ object, Case, from the manipulation of the setup object, User.

  1. Create the Case.
  2. Create the User (or at least try—we will also build in some simple error handling).
  3. Update the Case.

Salesforce has awesome standard functionality that can help us break this up: platform events.

What are platform events?

Platform events are not just for developers! As admins, we’re used to being able to trigger automations based on changes to records, which is a type of event that happens on the platform. However, unlike a record creation or change, platform events are notifications that don’t necessarily have to be tied to record changes. The official definition of platform events is “…secure and scalable messages that contain data. Publishers publish event messages that subscribers receive in real-time.” Clear as mud? No problem! Let’s break it down.

The publisher is the system that creates the message, or event. In our case, it’s Salesforce. The subscriber is the system that wants to know when an event happens so it can take some sort of action. In our case, it’s also Salesforce, but more often than not either the publisher or the subscriber will be a separate system. These two entities are part of the Publish-Subscribe (pub-sub) architecture. The benefit of this model is twofold. Publishers don’t have to worry about anything beyond publishing their messages, while subscribers don’t have to constantly ask for updates—just wait until they’re notified that the relevant event happened.

It’s kind of like listening to the radio or a podcast. Radio stations or podcast creators don’t know who, specifically, they’re creating their content for. They simply produce the content and make it available for others to tune in or subscribe to. Another example is social media. When I post something to Facebook, Twitter, or LinkedIn, I don’t know who’s going to read that post; however, that post going up will notify others (especially anyone who uses what I call the “Twitterstalk” feature to notify on every tweet) that new content is available in near real time so they can consume it. That’s the closest analogy I can make to what happens with platform events.

Bottom line, platform events give us a way to broadcast when something specific happens. They also give us a way to listen for that specific event happening to be able to take some sort of action.

It’s important to note that while similar to a custom object, platform events don’t create records in Salesforce. While you do create the Event or message, they don’t show up in the user interface (UI), you can’t search for them or report on them, etc. it’s kind of like they vanish as soon as they’re published (although there are ways to retrieve them for a limited period of time after they’re published, but that’s well beyond the scope of this post).

Building the platform event “object”

Before we can create a platform event in a flow, we need to create the platform event itself. It’s as easy as creating a custom object!

Navigate to Setup and type “Platform Events” in the Quick Search.

Searching for Platform Events.

On the Platform Events page, click New Platform Event.

Creating a new platform event.

 

Give your platform event a Label, such as “User Creation”, populate the Plural Label field, and check the “Starts with vowel sound” box if appropriate.

Fill out the Description so future admins and developers (and your future self) will understand why you built this platform event and what its purpose is. In this case, the platform event is created by a flow that attempts to create a new User record to update the related Case with success or error information.

For Publish Behavior, choose “Publish After Commit”. This ensures that the full transaction to create the User completes successfully before the platform event fires, saying, “Hey! I created a User!” Otherwise, we could receive a false positive message because the platform event fires with a message that a User was successfully created even if the transaction encounters an error and rolls back, which we definitely don’t want. Check out the help bubble next to this field if you ever forget the difference!

Finally, set the Deployment Status to “Deployed” and click Save.

The configured platform event.

Now that you’ve created the new platform event “object,” you can add the fields needed to hold the information that must be passed back to the original Case in Step 3, Update the Case. Like with custom objects, a few standard fields have been created automatically.

Click New in the Custom Fields & Relationships section.

New button in the Custom Fields & Relationships section.

You’ll notice that fewer field types are available for platform events than objects that are available through the UI. Create a Text field to hold the ID of the Case record we want to update when our platform event fires.

Creating a Text field for the platform event.

For the Label, enter “Record to Update ID”, set the length to 18, complete the Description field, and click Save. Looks very similar to creating a custom field on any other object, right? The only difference is that you don’t have to worry about Page Layouts, Record Types, or Security settings.

Customizing the new field.

Repeat this for your next two fields: “Status” - Text - length 50, and “Message to Update” - Long Text Area - length 50000.

Custom field details.

User creation flow overview

While we aren’t going to cover the entire flow in detail, there are a few items we’ll call out that are relevant to today’s topic. First, this is set up as an after-save flow not only because we’re creating ‘related records,’ but also because we want to leverage the “Run Asynchronously” option to break up the transaction. This allows us to complete Step 1, Create the Case, before moving on to Step 2, Create the User.

Configuring the Flow Start Criteria.

Configuring the Flow Start Criteria.

Within the flow itself, creating a platform event is no different than creating a record of any other object. We can use a combination of variables or literal text to set the values of the platform event. We’ve set this one up so that the Case can be updated, either because the user was successfully created or because a user already exists with that username. In each instance, we can set specific values for the Case Status and Description by using Assignment elements and then leverage a single Create Records element for the platform event.

Overview of the flow

Specific section of the flow.

 

Below is a screenshot of the top Assignment element that sets the values we want to pass to the Case if a user is already found with the proposed username. This could mean an inactive user simply needs to be reactivated, so we want to ensure someone reviews this and determines the appropriate course of action.

Setting the existing username message on the Edit Assignment page.

This is the bottom Assignment that sets the values to pass along when the User record is created successfully.

Setting the user created message on the Edit Assignment page.

Using these variables, we create the platform event “record.” This is how we complete the Publish part of the platform event architecture that broadcasts the status of New User creation.

Setting the values for the Create Record Element.

Build the platform event-triggered flow

Now, for Step 3, Update the Case, we need to address the Subscriber side of the platform event. You’re going to create a platform event-triggered flow.

Navigate to Flows in Setup.

Searching for Flows in Setup.

On the Flows page, click New Flow.

The New Flow button in Flows Setup.

Select the Platform Event—Triggered Flow and click Create.

Selecting a Platform Event-Triggered Flow.

Next, you’ll add an Update Records element to the canvas to complete Step 3, Update the Case. Since the platform event includes all of the information you need, choose “Specify conditions to identify records, and set fields individually”. Choose the Case object, and then filter Case records by Id, which is one of the custom fields you built on the platform event. Since the platform event triggered the flow, the fields are accessible through the $Record global variable. This means you identify the initial Case record by filtering Case records where the ID equals $Record.Record to Update ID.

Updating the new user case.

Once you’ve identified the record you want to update, use the other two fields populated on the platform event to update the Description and Status fields, respectively.

Updating Description and Status fields for the Case records.

And that’s a wrap! With the platform event-triggered flow, you’re able to automate the entire process of User creation from the initial case submission through case closure, or escalation in the event of an error, in just about one minute. That’s definitely going to save the team some time, and delight your users as well!

Resources

Want to see more good stuff? Subscribe to our channel!

SUBSCRIBE TODAY
Boost your study routine with flows and email-to-text gateways

Boost Your Salesforce Certification Study Routine with Flows & Email-to-Text Gateways | Automate This!

Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by Awesome Admin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Bradley Condon built the […]

READ MORE
Leverage flows to process inbound emails with documents

Leverage Flows To Process Inbound Emails With Documents | Automate This!

Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by Awesome Admin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Ohad Idan vanquishes shared […]

READ MORE
Improve sales funnel velocity with Flow

Improve Sales Funnel Velocity Using Flow | Automate This!

Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by Awesome Admin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, learn how Becka Dente leverages a simple […]

READ MORE