Issue querying Assessments

[Cirrus]

Good afternoon,

When an assessment is still in a ready to take state, we would like to send out a reminder to the user if the due date is within 4 days of the current date. Before I even get into script include and the like to work out how to pass the user and assessment number into an email, I thought I would just try and query the table to ensure we can at least identify the affected assessments. I am running the following background script, but so far I havent managed to return anything, despite having at least 6 records that currently meet the search criteria. Interestingly, the value for metric_type appears to be a sys_id, but it doesnt work with that either. Can anyone advise where we are going wrong please.

var now = new GlideDateTime();
now.addDaysUTC(4);
var gr = new GlideRecord('asmt_assessment_instance');
gr.addQuery('state','ready');
gr.addQuery('metric_type','demand');
gr.query();
while (gr.Next()) {
var due = new GlideDateTime(gr.due_date);
if (due.after(now)) {
gs.info('Assessment ' + asmt_assessment_instance.number + ' has not been completed');
}
}

[Nia McCash]

Instead of fiddling with GlideDateTime() I would try to use the query I can build via the UI to get the encoded query:

Which I can then use in my script:

var gr = new GlideRecord('asmt_assessment_instance');
gr.addEncodedQuery("state=ready^due_dateRELATIVELT@dayofweek@ahead@4");
gr.addQuery('metric_type','demand');
gr.query();

[Cirrus]

Thanks Nia. The problem seems to be that it doesnt accept metric_type 'demand' or metric_type @0f6ther5..... (sys id for the metric type name, which appears to be set on the asmt_metric_type table). Any ideas?

[Nia McCash · Accepted solution]

You can use the same method and build the metric_type condition into the filter you build in the list:

Then copy the query that is generated. This is what I get for the encoded query on my instance:

state=ready^due_dateRELATIVELT@dayofweek@ahead@4^metric_type=0556fa9a8f12110040f82ab2f0f923f8

[Cirrus]

Thanks Nia. Very helpful. I concur with your syntax within the table query, but whats the best way of testing this in a background script, as when I run it as the following I just get "Script execution history and recovery available here" but there are no records to validate the output. With my script below there should be 3 records. Do I need to add a gs.print statement? Sorry, I'm not a coder

var gr = new GlideRecord('asmt_assessment_instance');

gr.addEncodedQuery("metric_type.evaluation_method=assessment^state=ready^metric_type=0556fa9a8f12110040f82ab2f0f923f8^due_dateRELATIVELT@dayofweek@ahead@10");

gr.query();