Query Current Step Number for Approval process

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.

Thanks!!

Looking for Step Number - not step name

Answer 1

I don't think that there is field that will tell you exact step number. But if we extend the approach provided by Santanu Boral then we can get calculate the step number.

Data model info:
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.

Answer 2

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:

Please let me know the Step Number you are looking for are getting from these queries or I am missing something.