Querying Distinct Values in SOQL: Approval Process Work Items

Hello, and welcome to Another Salesforce Blog!  Here I will be posting solutions to problems that I couldn’t find an answer to in hopes of helping those who find themselves stuck when using the Salesforce platform.

User Story

We want to get a list of approval processes that have work items that are dependent on a user that is no longer active, and we want to do it without using Apex.

Background

There is no DISTINCT function in SOQL, so we’re going to have to do some Aggregate Query magic.

Solution

SELECT count(ProcessInstance.ProcessDefinitionId), ProcessInstance.ProcessDefinition.DeveloperName FROM ProcessInstanceWorkItem WHERE Actor.IsActive = false GROUP BY ProcessInstance.ProcessDefinition.DeveloperName

Let’s break down this query piece by piece:

SELECT count(ProcessInstance.ProcessDefinitionId), ProcessInstance.ProcessDefinition.DeveloperName

First, we’re going to get the count of the ProcessDefinitionId, meaning we’re counting the Approval Process Id. We’re also going to grab the DeveloperName of the Approval Process so that we know what we’re looking at.

FROM ProcessInstanceWorkItem WHERE Actor.IsActive = false

We are going to pull this information from the ProcessInstanceWorkItem where the user (Actor being the name of the relationship between the ProcessInstanceWorkItem and the User objects) is not active.

GROUP BY ProcessInstance.ProcessDefinition.DeveloperName

Lastly, we’re going to group our aggregate count() query by the Approval Process DeveloperName.

This will yield a list of Approval Processes that have work items relying on an inactive user.

Resources

ProcessInstance documentation.
ProcessInstanceWorkItem documentation.
Aggregate Functions in SOQL documentation.
COUNT() aggregate function documentation.

Thanks for reading, let me know if you have any comments or questions!

-Evelyn, Another Salesforce Blog

One-Time
Monthly
Yearly

Make a one-time donation

Make a monthly donation

Make a yearly donation

Choose an amount

$5.00
$15.00
$50.00
$5.00
$15.00
$50.00
$5.00
$15.00
$50.00

Or enter a custom amount

$

Help keep Another Salesforce Blog on the internet by donating today!

Your contribution is appreciated.

Your contribution is appreciated.

DonateDonate monthlyDonate yearly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: