Querying RITM based on Catalog Item Variables

[MWright1]

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.

[MWright1]

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

[Mannapuram]

@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]

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

[Shruti D]

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]

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.