How to delete Cancelled Survey Instances?

[Bogdan18]

Hi everyone,

I have a scheduled job that looks for any survey instances older than 7 days and cancel them if they are in the status Ready to take. The Scheduled job script can be seen below.

autoCancelassessments();

function autoCancelassessments() {

  var value = gs.getProperty('glide.assessment.autoclose.time'); // create a system property in sys_properties table., Type = integer and value = 7 in                                                

  var valueinInt = parseInt(value);

  if (valueinInt > 0) {

  var gr = new GlideRecord('asmt_assessment_instance'); // query the table

  var other = gr.addQuery('metric_type', '277b148b4f5106007825b3318110c796');

  other.addOrCondition('metric_type', '8c3a2c44d7211100158ba6859e6103fe');

  other.addOrCondition('metric_type', '87186844d7211100158ba6859e610378');

  other.addOrCondition('metric_type', '502a2c44d7211100158ba6859e6103a3');

  other.addOrCondition('metric_type', '6826bdbf4fa54a40acbe01b28110c762');//edit your assessment names  

  gr.addQuery('state', 'ready');

  gr.addQuery('sys_created_on', '<', gs.daysAgoStart(7));

  gr.query();

  while(gr.next()) {

  gr.state = 'canceled'; //value of cancelled state.

  gr.active = false;

  gr.update();

  }

  }

}

In addition to cancel the survey instance I would also like to delete the once Cancelled Survey Instance. I found some helpful script in the OOTB scheduled job "Remove Old Assessment Data" but this is deleting all survey instances not just the cancelled ones. The Remove Old Assessment Data schedule job script can be seen below. I am now wondering how to combine there 2 scripts and have a single one which cancel the survey instances older than 7 days and then immediately deletes the survey instance as well. Any ideas?

var gdt = new GlideDateTime();

gdt.addYears(-1);

var gr = new GlideRecord("asmt_assessment_instance_question");

gr.addQuery("instance.assessment_group.sys_created_on", "<", gdt);

gr.query();

while (gr.next())

    gr.deleteRecord();

var gr = new GlideRecord("asmt_assessment_instance");

gr.addQuery("assessment_group.sys_created_on", "<", gdt);

gr.query();

while (gr.next())

    gr.deleteRecord();

var gr = new GlideRecord("asmt_metric_result");

gr.addQuery("assessment_group.sys_created_on", "<", gdt);

gr.query();

while (gr.next())

    gr.deleteRecord();

Thanks,

Bogdan

[srinivasthelu]

Hi Bogadan,

Just a doubt. Why do you want to cancel it first ? anyway you are going to delete immediately . if you wanted to delete it directly

  var gr = new GlideRecord('asmt_assessment_instance'); // query the table

  var other = gr.addQuery('metric_type', '277b148b4f5106007825b3318110c796');

  other.addOrCondition('metric_type', '8c3a2c44d7211100158ba6859e6103fe');

  other.addOrCondition('metric_type', '87186844d7211100158ba6859e610378');

  other.addOrCondition('metric_type', '502a2c44d7211100158ba6859e6103a3');

  other.addOrCondition('metric_type', '6826bdbf4fa54a40acbe01b28110c762');//edit your assessment names

  gr.addQuery('state', 'ready');

  gr.addQuery('sys_created_on', '<', gs.daysAgoStart(7));

  gr.query();

  while(gr.next()) {

gr.deleteRecord()   // I have changed it to delete record.

  }

  }

}

Also, You can add conditions to delete only cancelled records.

var gr = new GlideRecord("asmt_assessment_instance_question");

gr.addQuery("instance.assessment_group.sys_created_on", "<", gdt);

gr.addQuery('state', 'canceled');   // I have added this condition

gr.query();

while (gr.next())

    gr.deleteRecord();

}

[Bogdan18]

Hi Srinivas,

Thanks for replying, the delete option will not work in the first schedule job as you first need to delete the asmt_assessment_instance_question part and only after that the instance can be deleted, not before. The deletion has to be in the order presented in the second schedule job. I want the schedule job to first cancel any survey instance older than 7 days and then in the second part look for only the cancelled ones and delete only these cancelled record. The Completed survey instances older than 7 days I want to keep as they are necessary for reporting purposes.

Thanks,

Bogdan

[Mike Allen]

I would go to your list view and build the query there:

And use

gr.addEncodedQuery('metric_type.evaluation_method=survey^state=canceled');

gr.deleteMultiple();

GlideRecord - ServiceNow Wiki

Also, up here, you can use IN

gr.addQuery('metric_type', 'IN',   '277b148b4f5106007825b3318110c796', '8c3a2c44d7211100158ba6859e6103fe', '87186844d7211100158ba6859e610378', '502a2c44d7211100158ba6859e6103a3', '6826bdbf4fa54a40acbe01b28110c762');

[Bogdan18]

Hi Mike,

Thank you for replying. the thing is that the deletion of survey instances must be done as it is presented in the second schedule job and in that specific order otherwise the deletion will not work. Based on your guys feedback I changed the second schedule job to what it can be seen below and it works in part as the schedule deletes only the Cancelled survey instances but it also deletes the questions for athe Completed ones. I added the bold part below but it still doesn't work, I guess that i must find a way to look for the state of the instance before trying to delete the questions, only if the instance has the status Cancelled then proceed to delete the questions, then the survey instance and then the metric result.

var gdt = new GlideDateTime();

1. gdt.addDaysLocalTime(-8);

var gr = new GlideRecord("asmt_assessment_instance_question");

1. gr.addQuery("instance.assessment_group.sys_created_on", "<", gdt);
2. gr.addQuery('asmt_assessment_instance.state', 'canceled');
3. gr.query();

while (gr.next())

gr.deleteRecord();

var gr = new GlideRecord("asmt_assessment_instance");

1. gr.addQuery("assessment_group.sys_created_on", "<", gdt);
2. gr.addQuery('state', 'canceled');
3. gr.query();

while (gr.next())

gr.deleteRecord();

var gr = new GlideRecord("asmt_metric_result");

1. gr.addQuery("assessment_group.sys_created_on", "<", gdt);
2. gr.addQuery('state', 'canceled');
3. gr.query();

while (gr.next())

gr.deleteRecord();

Thanks,

Bogdan