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.
Looking for Step Number - not step name
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 :
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'
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'
Please let me know the Step Number you are looking for are getting from these queries or I am missing something.