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.