
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 Grizzle
Another Salesforce Blog

Make a one-time donation
Make a monthly donation
Make a yearly donation
Choose an amount
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