Querying RITM based on Catalog Item Variables

MWright1 · 4 weeks ago

Hi all,

I am trying to find an RITM based on its variables via server side script. I am not getting anything back. Please help.

This code below returns nothing. Even though I am sure I have something to return.

    var gr = new GlideRecord('sc_item_option_mtom');
    gr.addQuery('sc_item_option.item_option_new.name','rid_source');
    gr.addQuery('sc_item_option.value','C:');

    gr.addQuery('sc_item_option.item_option_new.name','rid_path');
    gr.addQuery('sc_item_option.value','EINSTEIN\123');

    gr.addQuery('sc_item_option.item_option_new.name','alert_description');
    gr.addQuery('sc_item_option.value','My test');

    gr.addQuery('sc_item_option.item_option_new.name','resolution_state');
    gr.addQuery('sc_item_option.value','New');

    gr.addQuery('sc_item_option.item_option_new.name','system_alert_type');
    gr.addQuery('sc_item_option.value','Access');

    gr.query();

    while (gr.next()) {
        gs.addInfoMessage(gr.request_item.number); 
    }

Can you guys see what I am missing here? I have been looking at this code for sometime and can't find anything wrong with it.

Please help. Much appreciated.

Rafael Batistot · 4 weeks ago

Hi @MWright1

Today is your luck day, i've finished a project with this exactly situation.

This code return every variable filled

ar gr = new GlideRecord('sc_req_item');
gr.addQuery('cat_item', '<catalog_id>');
gr.query();
while (gr.next()) {

    getDataFilled(gr.sys_id); // sys_id  RITM
}

function getDataFilled(ritmSysId) {
    var catGr = new GlideRecord('sc_item_option_mtom');
    catGr.addQuery('request_item', ritmSysId);
    catGr.query();
    while (catGr.next()) {

        var optionSysId = catGr.sc_item_option.toString();

        var optionGr = new GlideRecord('sc_item_option');
        optionGr.addQuery('sys_id', optionSysId);
        optionGr.query();
        if (optionGr.next()) {
            gs.print(optionGr.value);
        }
    }
}

If you want some specifics values, not all may you try this

var gr = new GlideRecord('sc_req_item');
gr.addQuery('cat_item', '<catalog_id>'); 
gr.query();

// Names of variables that you need value
var variaveisDesejadas = ['option_1', 'option_2', 'option_3'...];

while (gr.next()) {

    var result= getDataFilled(gr.sys_id);
    gs.log(result.option_1, 'op_1');
    gs.log(result.option_2, 'op_2');
    gs.log(result.option_3, 'op_3');
}

function getDataFilled(ritmSysId) {
   
    var values = {
        option_1: null,
        option_2: null,
        option_3: null
    };

    var catGr = new GlideRecord('sc_item_option_mtom');
    catGr.addQuery('request_item', ritmSysId);
    catGr.query();

    while (catGr.next()) {
        var optionSysId = catGr.sc_item_option.toString();

        var optionGr = new GlideRecord('sc_item_option');
        optionGr.addQuery('sys_id', optionSysId);
        optionGr.query();

        if (optionGr.next()) {
            var variablename= optionGr.item_option_new.name.toString(); // 

            if (variaveisDesejadas.includes(variablename)) {
                valores[variablename] = optionGr.value.toString();
            }
        }
    }

    return values;
}

View solution in original post

MWright1 · 4 weeks ago

Thank you Rafael. Let me try these codes. I probably won't get back to you until Monday.

Mannapuram · 4 weeks ago

@MWright1 You are searching for many conditions in a single query. But Variable Ownership table is a M2M table linking only one combination of , Parent Item (Ex: RITM0013152) with a Dependent Item (set of one question-value pair, Example: rid_source -> C:).

Your query should search for only one question-value pair, like below,

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','rid_source');
gr.addQuery('sc_item_option.value','C:');
gr.query();

while (gr.next()) {
gs.addInfoMessage(gr.request_item.number);
}

But this will give you the list of all the RITM with this combination of question-value (rid_source -> C:). Thus, add additional conditions on the RITM (request_item), like searching for a specific Catalog Item.

var gr = new GlideRecord('sc_item_option_mtom');
gr.addQuery('sc_item_option.item_option_new.name','rid_source');
gr.addQuery('sc_item_option.value','C:');
gr.addQuery('sc_item_option.request_item.cat_item','sys_id_catalog_item');
gr.query();

while (gr.next()) {
gs.addInfoMessage(gr.request_item.number);
}

Let me know in either case. It worked or not. Thanks,

MWright1 · 4 weeks ago

Thank you, Mannapuram. Let me see what I can do. I probably won't get back to you until Monday.

Shruti D · 4 weeks ago

Hello @MWright1 ,

You were using multiple addQuery() statements to find a single record in sc_item_option_mtom where the same field (name or value) equals multiple different things at once, which is not logically possible.

You can use below script to get the expected RITM record:

var requiredOptions = {
    'rid_source': 'C:',
    'rid_path': 'EINSTEIN\\123',
    'alert_description': 'My test',
    'resolution_state': 'New',
    'system_alert_type': 'Access'
};
var reqItemGR = new GlideRecord('sc_req_item');
reqItemGR.query();
while (reqItemGR.next()) {
    var isMatch = true; 

    // Check if this request item has all required item options
    for (var optionName in requiredOptions) {
        var expectedValue = requiredOptions[optionName];

        var optionGR = new GlideRecord('sc_item_option_mtom');
        optionGR.addQuery('request_item', reqItemGR.sys_id);
        optionGR.addQuery('sc_item_option.item_option_new.name', optionName);
        optionGR.addQuery('sc_item_option.value', expectedValue);
        optionGR.query();

        if (!optionGR.next()) {
            isMatch = false; 
            break;
        }
    }

    // If all options matched, show the RITM number
    if (isMatch) {
        gs.addInfoMessage("RITM: " + reqItemGR.number);
    }
}

Please Mark Correct ✔️ if this solves your query and also mark Helpful 👍 if you find my response worthy based on the impact.

Regards,

Shruti

MWright1 · 3 weeks ago

I was able to get the RITM sys_id by running search and looping through the results and querying the variables. Going through the sc_item_option_mtom table is not ideal. Thank you all.