Use system property in gliderecord endoced query

avinashdubey103
Tera Guru

‎01-16-2024 10:53 PM

I have created a  system property that stores sys id. comma separated 

I have requirement to exclude these in gliderecord .
the script is not working 
Script :

var listOfVariables = gs.getProperty('variables_exclude');

                var variablesArray = listOfVariables.split(',');

                var itemVars = new GlideRecord('sc_item_option_mtom');

                if (table == 'sc_req_item') {

                    itemVars.addQuery('request_item', current.sys_id);

                }
                if (table == 'sc_task') {

                    itemVars.addQuery('request_item', current.request_item.sys_id);

                }

                if (table == 'sysapproval_approver') {

                    itemVars.addQuery('request_item', current.sysapproval.sys_id);

                }

                //itemVars.addNotNullQuery('sc_item_option.value');

                // Construct "NOT IN" logic within the encoded query
                var notInQuery = 'sc_item_optionIN' + variablesArray.map(function(value) {

                    return '!' + value;

                }).join(',');


                itemVars.addEncodedQuery(notInQuery);
                itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 11);

                itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 19);

                itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 20);

                itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 24);

                itemVars.orderBy('sc_item_option.order');

                itemVars.query();
0 Helpfuls
  • 1,052 Views
2 REPLIES 2

Harish Bainsla
Kilo Patron
Kilo Patron

‎01-16-2024 11:01 PM

Hi try this

var listOfVariables = gs.getProperty('variables_exclude');
var variablesArray = listOfVariables.split(',');

var itemVars = new GlideRecord('sc_item_option_mtom');

if (table == 'sc_req_item') {
itemVars.addQuery('request_item', current.sys_id);
}
if (table == 'sc_task') {
itemVars.addQuery('request_item', current.request_item.sys_id);
}
if (table == 'sysapproval_approver') {
itemVars.addQuery('request_item', current.sysapproval.sys_id);
}

// Construct "NOT IN" logic within the encoded query
var notInQuery = 'sc_item_optionIN' + variablesArray.map(function(value) {
return '!' + value;
}).join(',');

itemVars.addEncodedQuery(notInQuery);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 11);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 19);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 20);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 24);

itemVars.orderBy('sc_item_option.order');
itemVars.query();

0 Helpfuls

Iraj Shaikh
Mega Sage
Mega Sage

‎01-16-2024 11:03 PM

Hi @avinashdubey103 

Your script is attempting to construct an encoded query that excludes these sys_ids. However, there's a mistake in the way you're constructing the "NOT IN" query.

The encoded query for "NOT IN" should use the `NOT IN` operator, but your script is using `IN` followed by negations (`!`). Here's how you can fix the script:

 

var listOfVariables = gs.getProperty('variables_exclude');
var variablesArray = listOfVariables.split(',');

var itemVars = new GlideRecord('sc_item_option_mtom');

if (table == 'sc_req_item') {
    itemVars.addQuery('request_item', current.sys_id);
}
if (table == 'sc_task') {
    itemVars.addQuery('request_item', current.request_item.sys_id);
}
if (table == 'sysapproval_approver') {
    itemVars.addQuery('request_item', current.sysapproval.sys_id);
}

// Construct "NOT IN" logic within the encoded query
var notInQuery = 'sc_item_option.sys_idNOT IN' + variablesArray.join(',');

itemVars.addEncodedQuery(notInQuery);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 11);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 19);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 20);
itemVars.addQuery('sc_item_option.item_option_new.type', '!=', 24);

itemVars.orderBy('sc_item_option.order');
itemVars.query();

 


Here's what I changed:

1. I replaced 'sc_item_optionIN' with 'sc_item_option.sys_idNOT IN' to correctly use the "NOT IN" operator in the encoded query.
2. I removed the map function that was adding `!` to each value, as it's not needed for the "NOT IN" operator.
3. I directly joined the `variablesArray` with commas, as that's the correct format for the "NOT IN" operator in an encoded query.

Make sure that the sys_ids in the `variables_exclude` system property are valid and that they correspond to the `sys_id` field of the `sc_item_option` table. Also, ensure that the `table` variable is correctly set to one of the expected values ('sc_req_item', 'sc_task', or 'sysapproval_approver') before running this script.

 

Please mark this response as correct or helpful if it assisted you with your question.

0 Helpfuls