GlideQuery - Querying ritm variables

[Ankita9793]

Hi all,

I need help with below scheduled job script, where i'm trying to query ritm variables. I understand, GlideQuery does not support querying on ritm variables so join has to be used. I have updated my code with join but it does not work, Please suggest. 

Actual script : 

    new GlideQuery('sc_req_item')

        .where('active', true)

        .where('cat_item.name', 'GIAM Revoke Request')

        .where('ritm.variables.recert_id', 'HBEU-GB-A-BYODGFE-01') //issue with this statement 

        .where('approval', 'approved')

        .where('state', 'IN', [1, 2])

        .select(['u_beneficiary.u_country', 'number'])

        .forEach(function (reqItem) {

        var brambleSource = new GlideQuery('u_mobile_country_to_request_type')

        .where('u_bramble_source', 'IN', ['EMEA', 'APAC', 'TURKEY', 'Germany'])

        .where('u_country_code.u_lookup_country', reqItem.u_beneficiary.u_country)

        .selectOne('u_bramble_source')

        .orElse({

            u_bramble_source: null

        });

Updated script : 

var obj = {}

new GlideQuery('sc_req_item')

    .where('active', true)

    .where('cat_item.name', 'GIAM Revoke Request')

    .join('sc_item_option_mtom', 'sys_id', 'request_item') // Added join query

    .join('sc_item_option', 'sc_item_option', 'sys_id') // Added join query

    .where('sc_item_option.item_option_new.name', '=', 'recert_id')

    .where('sc_item_option.value', '=', 'HBEU-GB-A-BYODGFE-01')

    //.where('variables.recert_id', 'HBEU-GB-A-BYODGFE-01')

    .where('approval', 'approved')

    .where('state', 'IN', [1, 2])

    .select(['u_beneficiary.u_country', 'number'])

    .forEach(function(reqItem) {

        gs.info("tetst" + reqItem.number);

        var brambleSource = new GlideQuery('u_mobile_country_to_request_type')

            .where('u_bramble_source', 'IN', ['EMEA', 'APAC', 'TURKEY', 'Germany'])

            .where('u_country_code.u_lookup_country', reqItem.u_beneficiary.u_country)

            .selectOne('u_bramble_source')

            .orElse({

                u_bramble_source: null

            });

TIA!!

[Ankur Bawiskar]

@Ankita9793 

try this

var obj = {};
new GlideQuery('sc_req_item')
    .where('active', true)
    .where('cat_item.name', 'GIAM Revoke Request')
    .join('sc_item_option_mtom', 'sys_id', 'request_item')
    .join('sc_item_option', 'sys_id', 'sc_item_option') // Join sc_item_option_mtom.sc_item_option to sc_item_option.sys_id .where('sc_item_option.item_option_new.name', 'recert_id')
    .where('sc_item_option.value', 'HBEU-GB-A-BYODGFE-01')
    .where('approval', 'approved')
    .where('state', 'IN', [1, 2])
    .select(['u_beneficiary.u_country', 'number'])
    .forEach(function(reqItem) {
        gs.info("Number: " + reqItem.number);
        var brambleSource = new GlideQuery('u_mobile_country_to_request_type')
            .where('u_bramble_source', 'IN', ['EMEA', 'APAC', 'TURKEY', 'Germany'])
            .where('u_country_code.u_lookup_country', reqItem.u_beneficiary.u_country)
            .selectOne('u_bramble_source')
            .orElse({
                u_bramble_source: null
            });
    });

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@Ankita9793 

Hope you are doing good.

Did my reply answer your question?

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[PrathmeshD21790]

new GlideQuery('sc_req_item')
.where('active', true)
.where('cat_item.name', 'GIAM Revoke Request')
.join('sc_item_option_mtom', 'sys_id', 'request_item', 'mtom')
.join('sc_item_option', 'mtom.sc_item_option', 'sys_id', 'opt')
.join('item_option_new', 'opt.item_option_new', 'sys_id', 'optdef')
.where('optdef.name', '=', 'recert_id')
.where('opt.value', '=', 'HBEU-GB-A-BYODGFE-01')
.where('approval', 'approved')
.where('state', 'IN', [1, 2])
.select(['u_beneficiary.u_country', 'number'])
.forEach(function (reqItem) {
gs.info("Found RITM: " + reqItem.number);
var brambleSource = new GlideQuery('u_mobile_country_to_request_type')
.where('u_bramble_source', 'IN', ['EMEA', 'APAC', 'TURKEY', 'Germany'])
.where('u_country_code.u_lookup_country', reqItem.u_beneficiary.u_country)
.selectOne('u_bramble_source')
.orElse({ u_bramble_source: null });

// You can now use brambleSource.u_bramble_source as needed
});

If my response helped, please mark it as correct ✅ and close the thread 🔒 — this helps others find the solution faster! 🙏

Regards,
Prathamesh

[nawalkishos]

Hi @Ankita9793 ,

Please review below code- 

new GlideQuery('sc_req_item')
    .where('active', true)
    .where('cat_item.name', 'GIAM Revoke Request')

    // Join to MTOM table
    .join('sc_item_option_mtom', 'sys_id', 'request_item')

    // Join to variable values table
    .join('sc_item_option', 'sc_item_option', 'sys_id')

    // Join to variable definition table (item_option_new)
    .join('item_option_new', 'item_option_new', 'sys_id')

    // Match variable name
    .where('item_option_new.name', '=', 'recert_id')

    // Match variable value
    .where('sc_item_option.value', '=', 'HBEU-GB-A-BYODGFE-01')

    .where('approval', 'approved')
    .where('state', 'IN', [1, 2])

    .select(['u_beneficiary.u_country', 'number'])
    .forEach(function(reqItem) {

        gs.info("Processing RITM " + reqItem.number);

        var brambleSource = new GlideQuery('u_mobile_country_to_request_type')
            .where('u_bramble_source', 'IN', ['EMEA', 'APAC', 'TURKEY', 'Germany'])
            .where('u_country_code.u_lookup_country', reqItem.u_beneficiary.u_country)
            .selectOne('u_bramble_source')
            .orElse({ u_bramble_source: null });

    });

If you found my response helpful, please mark it as helpful and accept it as the solution.

Thank you
Nawal Singh