SC_TASK Variable Relationship Script

[DylanBlumenberg]

Hey everyone, I'm trying to create a relationship on the sc_task table and I'm running into some issues. The goal is to take the value of the employee_number catalog item variable from the current record and look up any other sc_task records that contain that same value on the employee_number variable. Below is what I have so far but it's returning every sc_task record in the system. Does anyone know where I went wrong?

Things worth noting: 

• 'Applies to table' and 'Queries from table' are both sc_task.
• There is a normal variable named employee_number and a variable set variable named employee_number. This script should look for values from both variables. 
• In the log, gs.info is returning the correct employee_number variable.

(function refineQuery(current, parent) {

    var employeeNumber = parent.request_item.variables.employee_number;
    gs.info('Related List Debug. Value found is: ' + employeeNumber);

    current.addQuery('request_item.variables.employee_number', employeeNumber);

})(current, parent);

[John Gilmore]

I would start by checking the data types before your addQuery statement.

gs.info(typeof employeeNumber);
gs.info(typeof current.request_item.variables.employee_number);

If those don't have the same data type then your query parameter is being ignored.

[phgdet]

Hi @DylanBlumenberg, you cannot directly query the variables of the Service Catalog Request Item table [sc_req_item]. You might need to query table sc_item_option_mtom, it will return many-to-many relationships between variable value and the sc_req_item, then you can filter to get the array of sysIds. Use current.addQuery('sys_id', sys_ids); eventually to have the correct related list.

[Ankur Bawiskar · Accepted solution]

@DylanBlumenberg 

variables are linked with RITM and not with sc_task

Something like this should work, assuming you want to see related sc_tasks with same variable value

(function refineQuery(current, parent) {

    var employeeNumber = parent.request_item.variables.employee_number;
    var variableSysId = ''; // give here the sysId of that variable from item_option_new table
    var ritmArr = [];
    var gr = new GlideRecord("sc_req_item");
    gr.addQuery("variables." + variableSysId, employeeNumber);
    gr.query();
    while (gr.next()) {
        ritmArr.push(gr.getUniqueValue());
    }
    current.addQuery('request_item.sys_id', 'IN', ritmArr.toString());

})(current, parent);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[DylanBlumenberg]

Thanks @Ankur Bawiskar, that worked great!

I made some additional changes to the script to allow searching for an array of variables as well as filtering it to only run on certain requests (which I happened to find from another one of your replies). With the addition of the variableSysIDs array, the related list loads noticeably slower, so I might take that out but wanted to share the script either way. 

(function refineQuery(current, parent) {
if (parent.request_item.cat_item == '0171a9cadb2cd0101739327e9d9619d7' || parent.request.u_order_guide == '2818c6431b7be9104143ebd5604bcb6e'){

    var employeeNumber = parent.request_item.variables.employee_number;
    var variableSysIds = ['245957951ba0b1104143ebd5604bcbac', 'e68171c2dbacd0101739327e9d9619a8']; // Add your sys_ids here
    var ritmArr = [];
    var gr = new GlideRecord("sc_req_item");

    var orQuery = gr.addQuery("variables." + variableSysIds[0], employeeNumber);
    for (var i = 1; i < variableSysIds.length; i++) {
        orQuery.addOrCondition("variables." + variableSysIds[i], employeeNumber);
    }

    gr.query();
    while (gr.next()) {
        ritmArr.push(gr.getUniqueValue());
    }
    current.addQuery('request_item.sys_id', 'IN', ritmArr.join(','));

} else {
	current.addQuery('sys_id', '-1');
}
})(current, parent);