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.
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.
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
This will yield a list of Approval Processes that have work items relying on an inactive user.
Thanks for reading, let me know if you have any comments or questions!
-Evelyn, Another Salesforce Blog