How to get last closed task of ritm

lucky24
Tera Contributor

‎09-11-2023 06:51 AM

Hi Team.

 

In some catalog item task is closed but in RITM closed date not populated so we have to update through background script.

but there is multiple task with for some  RITM (one catalog item) catalog item so I want that task which closed in last so I can update closed date according task.

 

I have write below script but this is for single task which attached to single RITM .

 

var ritmClosed  = new GlideRecord("sc_req_item");
ritmClosed.addEncodedQuery("closed_atISEMPTY^stateNOT IN1,2,51,52^cat_item=311967cc1b323fdfgb4ac99ffbd4bcb6f");
ritmClosed.query();
while(ritmClosed.next()){
var scTask = new GlideRecord("sc_task");
scTask.addQuery("request_item",ritmClosed.sys_id);
//ritmclosed.orderByDesc();
scTask.query();
if(scTask.next()){
ritmClosed.closed_at = scTask.closed_at;
gs.print(scTask.number);
}
gs.print(ritmClosed.number);
ritmClosed.update(); 
}
 
Please help me there is around 560 RITM and with each RITM there is multiple task attached. 
Thanks
0 Helpfuls
  • 735 Views
3 REPLIES 3

Mark Balloch
Giga Contributor

‎09-11-2023 07:28 AM

To update the closed_at field in the Requested Item (RITM) records with the most recent closed_at value from their associated tasks, you'll need to modify your script to handle multiple tasks per RITM. You can achieve this by tracking the most recent closed_at value for each RITM and then updating the RITM record accordingly. Here's a modified script to accomplish this:

 

var ritmGR = new GlideRecord("sc_req_item");
ritmGR.addEncodedQuery("closed_atISEMPTY^stateNOT IN1,2,51,52^cat_item=311967cc1b323fdfgb4ac99ffbd4bcb6f");
ritmGR.query();

while (ritmGR.next()) {
var mostRecentClosedAt = null; // Track the most recent closed_at value
var scTaskGR = new GlideRecord("sc_task");
scTaskGR.addQuery("request_item", ritmGR.sys_id);
scTaskGR.orderByDesc("closed_at"); // Order by closed_at in descending order to get the most recent task first
scTaskGR.query();

if (scTaskGR.next()) {
mostRecentClosedAt = scTaskGR.closed_at;
gs.print("Most recent task number: " + scTaskGR.number);
}

// Update the RITM with the most recent closed_at value
ritmGR.closed_at = mostRecentClosedAt;
ritmGR.update();
gs.print("Updated RITM number: " + ritmGR.number);
}


This script will iterate through all the RITM records that meet your specified criteria, query the associated tasks, and update the closed_at field in each RITM with the most recent closed_at value from its tasks.

Please make sure to test this script in a non-production environment first to ensure it works as expected and doesn't cause any unintended consequences. Additionally, consider creating a backup or snapshot of your data before making bulk updates like this.

0 Helpfuls

Venkatesh_N
Tera Guru

‎09-11-2023 07:40 AM

Hi @lucky24 ,
In the middle line which you commented, ,write it like this :
ritmclosed.orderByDesc('sys_updated_on');

I hope this helps. Mark my answer correct & Like Helpful, if Applicable.

Thanks!
Venkatesh Nekkanti

 

0 Helpfuls

SwarnadeepNandy
Mega Sage

‎09-11-2023 08:20 AM

Hello @lucky24,

I have modified your script.

//Define a function that takes a RITM sys_id as an input and returns the sys_id of the last closed task
function getLastClosedTask(ritmId) {
  //Create a new GlideRecord object for the sc_task table
  var gr = new GlideRecord('sc_task');
  //Add a query condition to filter by the RITM sys_id
  gr.addQuery('request_item', ritmId);
  //Order the records by the closed_at field in descending order
  gr.orderByDesc('closed_at');
  //Query the table
  gr.query();
  //Check if there is at least one matching record
  if (gr.next()) {
    //Return the sys_id of the first record, which is the last closed task
    return gr.getValue('sys_id');
  } else {
    //Return null if no matching record is found
    return null;
  }
}

//Loop through each RITM that has no closed_at value and is not in an active state
var ritmClosed = new GlideRecord('sc_req_item');
ritmClosed.addEncodedQuery('closed_atISEMPTY^stateNOT IN1,2,51,52^cat_item=311967cc1b323fdfgb4ac99ffbd4bcb6f');
ritmClosed.query();
while (ritmClosed.next()) {
  //Get the sys_id of the last closed task for the current RITM
  var lastTaskId = getLastClosedTask(ritmClosed.getValue('sys_id'));
  //Check if the last task id is not null
  if (lastTaskId) {
    //Create a new GlideRecord object for the sc_task table
    var scTask = new GlideRecord('sc_task');
    //Get the record by the sys_id
    scTask.get(lastTaskId);
    //Update the closed_at value of the RITM with the closed_at value of the last task
    ritmClosed.closed_at = scTask.closed_at;
    gs.print(scTask.number);
  }
  gs.print(ritmClosed.number);
  ritmClosed.update();
}

Hope that helps.

 

Kind Regards,

Swarnadeep Nandy

0 Helpfuls