Reflecting CHG Actual End Dates with the latest closed CTask end date

[GB9414]

Not sure if this is even possible but does anyone have a script or suggestion that will allow for the latest closed CTask work end date to also reflect as the actual end date for the CHG request. 

The following script is currently active and it works if I only have one closed CTask within the CHG request, however if a user had a few more tasks (specifically more that 2), then it would only reflect up to the second closed task.

Script as follows:

(function executeRule(current, previous /*null when async*/)

      

      //Query change tasks related to the current change request

      var task = new GlideRecord('change_task');

      task.addQuery('parent', current.sys_id);

      task.query();

      var latest_end_date;

      

      //Loop through all related tasks

      while (task.next()) {

            //If the task is closed

            if (task.state == '3') {

                  // If this is the first task or this task ended later than the previous latest

                  if (!latest_end_date || task.work_end > latest_end_date) {

                        latest_end_date = task.work_end;

                  }

            }

      }

      //If there was at least one closed task, update the change request's actual end date

      if (latest_end_date) {

            current.work_end = latest_end_date;

            current.update();

      }

})(current, previous);

I have try other variations as well as ensuring accurate field configurations but nothing seems to work. Even AI suggestions are proving futile. 

Any promising leads would be extremely appreciated!

Thank you!

[GB9414]

Brad,

So the BR is live, I have a CHG request in DEV that has 5 CTasks associated with it. When I test the BR, the actual end date of the CHG reflects the 4th closed CTask. How do get the BR to reflect the 5th closed CTask. I have tried the Business Rule with 

current.work_end = tsk.work_end;

per your suggestion and I have also tried

current.actual_end = tsk.work_end;

Everything else from your suggestions remained the same in my BR, I just can't seem to crack this missing piece. The run actions and conditions are also as suggested.  Any other thoughts for this?
Thanks!

[Brad Bowman]

I suspect there's a data or test case issue, which also prevented your original attempt from working.

Are you testing this by manually changing the State on the Change Request to Closed, and all of the change tasks are already closed - so you can re-test using the same Change Requests and tasks by manually changing the State to something else, then changing it again to Closed, or do you have something in place where once the last change task is closed the State on the Change Request automatically changes?

To confirm the data, add a gs.info log inside the if block to confirm the record returned by the query and the value of tsk.work_end to make sure we're not chasing down the wrong issue.  If the 4th instead of 5th record is actually the one being returned by the GlideRecord, run a fix script something like this to check the data:

var tsk = new GlideRecord('change_task');
tsk.addQuery('change_request', 'b0dbda5347c12200e0ef563dbb9a718f'); //hard-code test case Change Request sys_id
tsk.addQuery('state', 3);
tsk.orderByDesc('work_end');
tsk.query();
while (tsk.next()) {
	gs.print('change task # ' + tsk.number + ' work_end = ' + tsk.work_end);
}

You should see something that makes sense, like this from my sample data:

Then when you change the 'while' to an 'if', you still get the correct record:

If the log in the BR shows the 5th, correct record is actually being returned by the GlideRecord, then another Business Rule or something is affecting the Change Request work_end value from what the script is setting it to. 

[Ethan Davies]

Hey - yes, this is certainly possible! So you're not a million miles away, you seem like you want a lead rather than a direct answer so what I would suggest is looking into ordering the results that get returned from the GlideRecord query. For example, if we order the GlideRecord query to return the Change Tasks that were closed most recently, we can just select the first one we find in our query and use that one to update the Actual End date value on the Change.

There is another article here that will help you understand that concept and you should be able to apply it to your code. Pay special attention to the following code from that article: https://www.servicenow.com/community/developer-forum/script-to-find-the-latest-record-and-update-a-r...

sts.setLimit(1);
sts.orderByDesc('as_on');

The above code will order the GlideRecord results that are returned from your query by some field (i.e. in your case it will likely be a value that specifies when the Task was closed, i.e. the 'closed_at' field), and also limit the results so that we only process the first one we find. By combining .setLimit() and .orderByDesc we essentially get the most recent record.

Since you would only be returning 1 record, you would then change from:

while (task.next()) {
// Do something
}

to

if (task.next()) {
// Do something
}

Then if you find a record, your code to update the Change Request with the date from the Change Task can be executed.

Hope this helps and let me know if you need any further pointers!

[harshav]

Your requirement is not clear, you need update the Change only when all the tasks are closed or any task is closed. If any task is closed then it doesn't make sense to update the change with actual end Date. 

[Amit Gujarathi]

HI @GB9414 ,
I trust you are doing great.
Please find the below updated code for the same.

(function executeRule(current, previous /*null when async*/) {
    var task = new GlideRecord('change_task');
    task.addQuery('parent', current.sys_id);
    task.addQuery('state', '3'); // Closed tasks only
    task.orderByDesc('work_end'); // Order by work_end in descending order
    task.setLimit(1); // Limit to the most recent closed task
    task.query();

    if (task.next()) {
        // If there's at least one closed task
        current.work_end = task.work_end; // Set CHG request's actual end date
        current.update();
    }
})(current, previous);