4

It seems to be a quirk of ordering SOQL queries by CreatedDate that, if the records were created in the same second, the order returned by the SOQL query doesn't reflect the order of execution, as demonstrated here.

I'm running into this problem when trying to query the approval process history for a custom object, in that the submission step is appearing before the next pending steps.

List<ProcessInstance> allApprovalProcesses = new List<ProcessInstance>(
            [SELECT Id, TargetObjectId, Status, CompletedDate, CreatedDate, ProcessDefinitionId, 
                (SELECT Id, StepStatus, Originalactor.Name, Actor.Name, CreatedDate 
                    FROM StepsAndWorkitems 
                    ORDER BY Id, CreatedDate DESC), 
                (SELECT Id, Actor.Name 
                    FROM Workitems), 
                (SELECT Id, StepStatus, Actor.Name, Originalactor.Name, CreatedDate 
                    FROM Steps) 
                FROM ProcessInstance 
                WHERE TargetObjectId IN :proposalIDs 
                ORDER BY TargetObjectId, CreatedDate DESC]);

Even if I order by both CreatedDate and Id descending, as suggested in the above link, the 'Started' (i.e. 'Submitted') step appears before the 'Pending' steps.

 05:45:54.0 (38218906)|USER_DEBUG|[359]|DEBUG|*** 
(ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04h11000001OSmAAAW, StepStatus=Started, OriginalActorId=00511000004iKuJAAU, ActorId=00511000004iKuJAAU, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up4AAE, StepStatus=Pending, OriginalActorId=00GG0000005hz2FMAQ, ActorId=00GG0000005hz2FMAQ, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up5AAE, StepStatus=Pending, OriginalActorId=00GG0000005hz2EMAQ, ActorId=00GG0000005hz2EMAQ, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up6AAE, StepStatus=Pending, OriginalActorId=00511000004iKuJAAU, ActorId=00511000004iKuJAAU, CreatedDate=2016-11-02 10:58:11})

Anyone have any ideas how I can force the SOQL ordering as per the actual approval history below?

Example of approval process history

Thanks!

  • Good question. The reason sorting by Id doesn't work is that the 'Started' record appears to be stored in a different object (the first 3 digits of the Id differ from the other records). – Guy Clairbois Nov 3 '16 at 12:16
  • Yeah, it's annoying. I've come up with a workaround now that I'll share in an answer but I'm hoping someone can help me out with something a bit cleaner. – Aislinn Hayes Nov 3 '16 at 12:25
4

I've been able to make it work with the following query:

private Map<Id,ProcessInstance> getProcessHistory(Id objectId){
       return new Map<Id,ProcessInstance>([SELECT
                                                Id
                                                , (SELECT 
                                                        ID
                                                        , ActorId
                                                        , Actor.Name
                                                        , Comments
                                                        , CreatedById
                                                        , CreatedDate
                                                        , IsDeleted
                                                        , IsPending
                                                        , OriginalActorId
                                                        , OriginalActor.Name
                                                        , ProcessInstanceId
                                                        , ProcessNodeId
                                                        , ProcessNode.Name 
                                                        , RemindersSent
                                                        , StepStatus
                                                        , TargetObjectId
                                                    FROM StepsAndWorkitems
                                                    ORDER BY CreatedDate DESC, Id DESC)
                                            FROM ProcessInstance 
                                            WHERE TargetObjectId =:objectId
                                            ORDER BY CreatedDate DESC, Id DESC]);
    }

The catch is to order first by the Created Date and THEN by the Id.

|improve this answer|||||
0

I've created a workaround in the method below - not particularly clean so I'm hoping there are other suggestions!

/* 
*  Function to reorder of the approval process instance stepandworkitems if the approval process is 'Pending' and CreatedDate of steps are equal
*  This is necessary because SOQL query to sort by CreatedDate DESC does not predictably follow the execution order of the approval process
*  if the CreatedDates are the same for each step (CreatedDate is only accurate to 1 second).
*/
@TestVisible private static List<ProcessInstanceHistory> reorderApprovalProcess(ProcessInstance thisApprovalProcess){
    List<ProcessInstanceHistory> reorderedApprovalProcessSteps = thisApprovalProcess.StepsAndWorkItems;
    if (thisApprovalProcess.Status=='Pending'){
        // Get list of approval steps for this process
        List<ProcessInstanceHistory> approvalStepList = thisApprovalProcess.StepsAndWorkitems;
        // Check if first step is 'Started' AND size is greater than 1 AND next step has same CreatedDate AND next step is 'Pending'
        if (approvalStepList.size()>1){
            if( (approvalStepList[0].StepStatus=='Started') && (approvalStepList[1].StepStatus=='Pending') && (approvalStepList[0].CreatedDate==approvalStepList[0].CreatedDate)){
                // Reverse list
                List<ProcessInstanceHistory> reversed = new List<ProcessInstanceHistory>();
                for(Integer i = approvalStepList.size() - 1; i >= 0; i--){
                    reversed.add(approvalStepList[i]);
                }
                reorderedApprovalProcessSteps = reversed;
            }
        }
    }
    return reorderedApprovalProcessSteps;
}
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.