How to query doesn't contain in glide record.

[jobin1]

Hi All,

How to Query does not contain in glide record. Tried below but not working.

Need to query "if a particular RITM no is not available in sc_req_item"

if (emailsubject.includes("RITM")) {
subject = emailsubject;
indexStart = subject.indexOf('RITM');
number = subject.slice(indexStart, indexStart + 12);
gs.log("custmint191" + number);

var sc_item1 = new GlideRecord("sc_req_item");

//sc_item1.addEncodedQuery('number NOT LIKE number');
//sc_item1.addEncodedQuery('number DOES NOT CONTAIN number');
//sc_item1.addEncodedQuery('numberNOT LIKE' + number);

sc_item1.query();

[Saurav11]

As per your query which you wrote so the number which comes up in the number variable that that should be in one of 3,4,7,110 then only it should come otherwise it should return all the other numbers which does not have the store number field right?

Can you give me an example?

[jobin1]

The ritm I'm trying is not in 3,4,7,110 but still its going in this loop.

I am pasting some more script.

(function executeRule(current, previous /*null when async*/ ) {

//Fetching values from custom_integration table.
var userid = current.getDisplayValue('u_userid');
gs.log("custmint1 " + userid);
var ritmcmpy = current.getDisplayValue('u_ritm_company');
gs.log("custmint2 " + ritmcmpy);
var taskid = current.getDisplayValue('u_taskid');
gs.log("custmint3 " + taskid);
var triggercount = current.getDisplayValue('u_trigger_count');
gs.log("custmint4" + triggercount);
var triggerstatus = current.getDisplayValue('u_trigger_status');
gs.log("custmint41" + triggerstatus);
var ritmnumber = current.getDisplayValue('u_ritm_number');
gs.log("custmint5" + ritmnumber);
var sessionid = current.getDisplayValue('u_sessionid');
gs.log("custmint6" + sessionid);
var actionstatus = current.getDisplayValue('u_action_status');
gs.log("custmint7" + actionstatus);
var emailsender = current.getDisplayValue('u_email_sender');
gs.log("custmint9" + emailsender);
var emailcc = current.getDisplayValue('u_email_cc');
gs.log("custmint10" + emailcc);
var emailbcc = current.getDisplayValue('u_email_bcc');
gs.log("custmint11" + emailbcc);

var emailinbound = current.getDisplayValue('u_email_inbound');
gs.log("custmint13" + emailinbound);

var emailoutputresponse = current.getDisplayValue('u_email_outputresponse');
gs.log("custmint14" + emailoutputresponse);
var emailbody = current.u_email_body;

gs.log("custmint15" + emailbody);

var str4 = GlideSPScriptable().stripHTML(emailbody);
var emailsubject = current.getDisplayValue('u_email_subject');
gs.log("custmint12" + emailsubject);
//subject changes for /open/wip/pending tickets/only new email client entry/ starts
if (emailsubject.includes("RITM")) {
var subject = emailsubject;
var indexStart = subject.indexOf('RITM');
var number = subject.slice(indexStart, indexStart + 12);
gs.log("custmint081" + number);

var grritm = new GlideRecord("sc_req_item");
grritm.addQuery('number', number);
grritm.addEncodedQuery('stateIN1,2,101,102,103,6');//the wip/open/pending ticket should go only in this loop
grritm.query();
if (grritm.next()) {
var grritmval = grritm.getValue('sys_id');
gs.log("custmint82" + grritmval);
grritm.u_mailbox_address = userid;
grritm.update();
var gr4 = grritm.getValue('number');
gs.log("custmint8" + gr4);
gr4 = grritm.sys_id.toString();
gs.log("custmint19" + gr4);
//mailbox address is passing to u_from
var emailId4 = grritm.u_mailbox_address;
gs.log("custmint21" + emailId4);
var emailadddress3 = new GlideRecord('u_email_reply_address');
emailadddress3.addQuery('u_reply_to', emailId4);
emailadddress3.query();
if (emailadddress3.next())
var ecfrom5 = emailadddress3.sys_id.toString();
gs.log("custmint22" + ecfrom5);
//Updated for u_from field in email client end

var gr5 = new GlideRecord('u_email_client');
gr5.initialize();
gr5.u_subject = emailsubject;
gr5.u_to_whom = userid;
gr5.u_cc = emailcc;
gr5.u_bcc = emailbcc;
gr5.u_from_address = emailsender;
gr5.u_from = ecfrom5;
gr5.u_status = "Received";
gr5.u_item = gr4;
//r5.u_html_message = emailbody;
if (emailinbound.includes("html_content:false")) {
gr5.u_message = emailbody;
} else {
gr5.u_html_message = emailbody;
}
gr5.u_sender = emailsender;
gr5.u_email_direction = "Received";

gr5.insert();
var ecsysid = gr5.getValue('sys_id');
gs.log("custmint61" + ecsysid);

var gr6 = grritm.getValue('number');
gs.log("custmint30 " + gr6);
var gr = new GlideRecord('u_custom_integration');
gr.orderByDesc("sys_created_on");
gr.setLimit(1);
gr.query();
if (gr.next()) {

var ritmNumber2 = grritm.getValue('number');
gr.u_ritm_number = ritmNumber2;
gr.u_trigger_status = "Processed";

gr.u_email_outputresponse = "email_body_file_sys_id::" + ecsysid + "\n" + "operations_XMAP:true" + "\n" + "sys_id:" + gr4 + "\n" + "urn:" + actionstatus + "\n" + "ritm_num:" + gr6 + "\n" + "email_body_file_table:u_email_client" + "\n" + "table:sc_req_item";

gr.update();

}
}
}

//subject changes for /open/wip/pending tickets/only new email client entry/ ends
//subject changes for Cancelled and closed ticket starts/RITM and email client entry/starts
if (emailsubject.includes("RITM")) {
subject = emailsubject;
indexStart = subject.indexOf('RITM');
number = subject.slice(indexStart, indexStart + 12);
gs.log("custmint191" + number);

var sc_item1 = new GlideRecord("sc_req_item");
//sc_item1.addQuery('number', number);
//sc_item1.addQuery('stateIN3,4,7,110');


//sc_item1.addEncodedQuery('numberLIKE'+number+'^stateIN3,4,7,110^NQnumberNOT LIKE'+number);
//sc_item1.addEncodedQuery('numberLIKE'+number+'^stateIN3,4,7,110');
sc_item1.addEncodedQuery('numberLIKE'+number+'^stateIN3,4,7,110^ORnumberNOT LIKE'+number);////the wip/open/pending ticket also comming in this loop as well thats the issue

//sc_item1.addEncodedQuery('numberNOT LIKE'+number);
//sc_item1.addQuery('number','DOES NOT CONTAIN',number);
//var query = "number="+number+"^ORnumberNOT LIKE"+number+"^stateIN3,4,7,110";
//sc_item1.addEncodedQuery(query );
//sc_item1.addEncodedQuery('number, number ^stateIN3,4,7,110^ORnumber NOT LIKE number');//new ritm for pend
//sc_item1.addEncodedQuery('number, number ^stateIN3,4,7,110^ORnumber DOES NOT CONTAIN number');//new ritm for pend
//sc_item1.addEncodedQuery('number=' + number + '^stateIN3,4,7,110^ORnumberNOT LIKE' + number);//invalid ritm not working
//sc_item1.addEncodedQuery('number= + number + ^stateIN3,4,7,110^ORnumberNOT LIKE + number');//issue for cancelled and closed,invalid ritm
sc_item1.query();
if (sc_item1.next()) {
var sc_itemval = grritm.getValue('sys_id');
gs.log("custmint182" + sc_itemval);

[Saurav11]

So just to be clear you are saying for example the RITM0010035 is the value returned in the variable and it is not in state 3,4,7,110 still is is going inside?

[jobin1]

Yes You're correct ,That's the problem

[Community Alums]

Hello @jobin1 

Could you please try 

if (emailsubject.includes("RITM")) {
var subject = emailsubject;
var indexStart = subject.indexOf('RITM');
var num = subject.slice(indexStart, indexStart + 12);
gs.log("custmint191" + number);

var sc_item1 = new GlideRecord("sc_req_item");
sc_item1.addQuery('number', num);
sc_item1.setLimit(1); // Very important for performance since we only care about one record
sc_item1.query();
if(sc_item1.hasNext()) {
gs.info("record is present");
  return true;
} else {
gs.info("record is not present");
return false;
}

Please Mark ✅ Correct & 👍 Helpful, if applicable.