+ Start a Discussion
raj kiranraj kiran 

SOQL on approval -process instance ,process instance step, process definition

Hi All,

My objective was to write and workflow/trigger to fire an email notification when the approval time between the steps in approval process takes more than some specific time. So, i was trying to fetch the timestamp from each step such that if the time stamp is greater than of required period then an email will be fired according step related user/queue.

To fetch the time stamp i wrote an SOQL query between the processdefinition,processinstance,processinstance step. which is

SELECT ID,(SELECT Id, (SELECT ElapsedTimeInMinutes, FROM ProcessInstanceStep) FROM ProcessInstance ) FROM ProcessDefinition WHERE Id = ‘some value’

Here for every record creation in an approval process there will be a new process instance id created keeping process definition kind of master id as reference . And for every process instance id, there will be associated step id created for approavl steps for a record in approval.

Now the question is that i an unable to understand the realtion between these 3 tables to frame my query as my query fails when it reads the subquery select.

Please let me know how can i over come this problem.

raj kiranraj kiran
Just found an similar query which i was looking for,

SELECT Id, (SELECT Id, StepStatus  FROM Steps)
 FROM ProcessInstance

However this steps could not fetch me the timestamp which is available in Processinstancestep.