Query Current Step Number for Approval process

/ apex, Salesforce / By

QUESTION
Is it possible to retrieve the current Step Number for an approval process via a query? If so how? I don’t see this as available field in the ProcessInstanceNode object.

Note: Looking for Step Number – not step name

ANSWER

Let’s understand Data model behind approval process.
ProcessInstance : This table store the data about which record is undergoing which (approval)process.

ProcessInstanceStep : This table store the information about the all steps which user took on particulate Approved, Rejected etc.

Here is the algorithm that i am proposing:

1) Find if any approval process is “Pending” for your record.

SELECT CompletedDate, ElapsedTimeInDays, ElapsedTimeInHours, ElapsedTimeInMinutes, Id, ProcessDefinitionId, Status, SubmittedById, TargetObjectId FROM ProcessInstance WHERE TargetObjectId = '0069000000kSaNE' And Status ='Pending'

2) Then grab the ProcessInstance –> ID and use it in following query

SELECT ActorId,Comments,CreatedById,CreatedDate, ElapsedTimeInDays, ElapsedTimeInHours,   ElapsedTimeInMinutes,Id, OriginalActorId, ProcessInstanceId,  StepNodeId,StepStatus, SystemModstamp FROM ProcessInstanceStep where    ProcessInstanceId='04g90000009uPK0AAM'

This will give all the steps performed for that target object.

Now you can count the number of ‘approved’ records in above query and get the current step number.

PS: If you may need to do addition/subtraction if there are recjection record to get the current step number

The another way could be just add the step number in your Step name so that you can extract that directly from ProcessInstance

So, for ex. if your steps are named as :

PM approval
DM approval
VP Approval

Then it can be renamed to :

PM approval (Step 1)
DM approval (Step 2)
VP Approval (Step 3)

Now you can just grab the name parse the last part in bracket and find out the step number without doing any calculations.

I have tried to investigate at my own way. Created this approval process record.

And I have tried following queries:

SELECT ProcessInstance.Id, ProcessInstance.ProcessDefinitionID, (Select StepStatus,StepNodeId FROM Steps) FROM ProcessInstance WHERE ProcessInstance.TargetObjectId = 'a0190000000uCVa'

It is showing this result:

Based on the ProcessDefinitionId returned from above query I have tried to fetch Step Name as follows:

SELECT Id,Name, ProcessDefinitionId FROM ProcessNode
WHERE ProcessDefinitionId IN (SELECT ProcessDefinitionID FROM ProcessInstance
WHERE TargetObjectId =  'a0190000000uCVa')

It is showing this result:

And finally I have pulled out the history as follows:

SELECT Id, (SELECT Id, StepStatus, Comments FROM StepsAndWorkitems)FROM ProcessInstance WHERE Id =  '04g9000000AdRNKAA3'

It is showing results as follows: 

Related Posts