[vitaminxu]

I have a process with one form submitted by requester, then routed to manager for approval.
Biz user want me to show manager's approval date time in an output document.
Initially I captured the current datetime in a trigger, placed it before routing inside the manager's approval task.
But Biz user check the case history, and compare my date time with the Case History -> task End Date, and found sometimes there's 1s difference.

So I'd like to directly fetch the task's End Date datetime value show in PM's case history. Anyone know how to get it? Thanks.

[amosbatto]

You can look this information up in the APP_CACHE_HISTORY.DEL_FINISH_DATE field in the database in a trigger.

For example, create the following trigger:

Code: Select all

$taskName = "Task 4"; //set to the name of task
@@taskFinishDate = '';
$caseId = @@APPLICATION;
$sql = "SELECT DEL_FINISH_DATE FROM APP_CACHE_VIEW WHERE APP_UID='$caseId' AND 
    APP_TAS_TITLE='$taskName' ORDER BY DEL_DELEGATE_DATE DESC";
$aDels = executeQuery($sql);
if (count($aDels)) {
   @@taskFinishDate = $aDels[1]['DEL_FINISH_DATE'];
} 

Set this trigger to fire "after routing" of the "Task 4" task.
Then put @#taskFinishDate in an Output Document or use it as a variable in a DynaForm field.

[vitaminxu]

Thanks amosbatto! Your code works.
But there' re also exceptions. If one case got reassigned, the code cannot catch the latest timestamp I need.
E.g. Task 2 is to get manager's approval. If task 2 is initially routed to Manager A, but then case supervisor reassign the task to Manager B at timestamp1, and Manager B finish this task 2 at timestamp2. I'm supposed to capture timestamp2, but with below code, it's always timestamp1 being captured. Would you plz help to advise? Thanks!

You can look this information up in the APP_CACHE_HISTORY.DEL_FINISH_DATE field in the database in a trigger.

For example, create the following trigger:

Code: Select all

$taskName = "Task 4"; //set to the name of task
@@taskFinishDate = '';
$caseId = @@APPLICATION;
$sql = "SELECT DEL_FINISH_DATE FROM APP_CACHE_VIEW WHERE APP_UID='$caseId' AND 
    APP_TAS_TITLE='$taskName' ORDER BY DEL_DELEGATE_DATE DESC";
$aDels = executeQuery($sql);
if (count($aDels)) {
   @@taskFinishDate = $aDels[1]['DEL_FINISH_DATE'];
}

Set this trigger to fire "after routing" of the "Task 4" task.
Then put @#taskFinishDate in an Output Document or use it as a variable in a DynaForm field.

[amosbatto]

Try setting your trigger to fire at the start of the next task in the process.
If that doesn't work, then try this:

Code: Select all

$sql = "SELECT DEL_FINISH_DATE FROM APP_CACHE_VIEW WHERE APP_UID='$caseId' AND 
    APP_TAS_TITLE='$taskName' ORDER BY DEL_INDEX DESC";

[vitaminxu]

Thanks amosbatto! The updated sql works perfectly.