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();

[jobin1]

Hi

I need to handle it in glide record query itself, not in if the condition.

[kamlesh kjmar]

Hi @jobin1 ,

Correct me if I have understood your issue wrong, I beleive what you are looking for is :

Check if there is already any RITM number available with same name as stored in your number  field which you have extracted from subject line. If this is the scenario then you can use simple script provided below:

if (emailsubject.includes("RITM")) {
subject = emailsubject;
indexStart = subject.indexOf('RITM');
number = subject.slice(indexStart, indexStart + 12); // I am assuming, here you are getting ritm number RITMXXXXX
gs.log("custmint191" + number);

var sc_item1 = new GlideRecord("sc_req_item");
if(sc_item1 .get("number",number)
{
// Write your logic how to handle this scenario when RITM with same number Exist
}
else
{
// Write your logic how to handle this scenario when RITM with same number DOES NOT Exist
}

}

Please let me know if this do not works

I Hope this helps.

Please mark this helpful if this helps and Accept the solution if this solves your issue.

Regards,

Kamlesh

[jobin1]

Hi

I need to perform few tasks if the below query is satisfied.

so all we need to validate in one query

something like this->sc_item1.addEncodedQuery('number, number ^stateIN3,4,7,110^ORnumber NOT LIKE number'); 

entire 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');
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.addEncodedQuery('number, number ^stateIN3,4,7,110^ORnumber NOT LIKE number');
// sc_item1.addEncodedQuery('number, number ^stateIN3,4,7,110^ORnumber DOES NOT CONTAIN number');
//sc_item1.addEncodedQuery('number=' + number + '^stateIN3,4,7,110^ORnumberNOT LIKE' + number);
//sc_item1.addEncodedQuery('number= + number + ^stateIN3,4,7,110^ORnumberNOT LIKE + number');
sc_item1.query();
if (sc_item1.next()) {
var sc_itemval = grritm.getValue('sys_id');
gs.log("custmint182" + sc_itemval);
var user_id, country, company, phone, company_sysid;

var cartId = GlideGuid.generate(null);
var cart = new Cart(cartId);
var item;

//Validation of Catalog Item-Starts here
if (sessionid == "Standard HR Query") {
item = cart.addItem('a9bb0be5379b524021865ca543990ebf');
cart.setVariable(item, 'stan_hr_issue_category', 'HR Query');
} else if (sessionid == "Standard Invoice Query") {
item = cart.addItem('b1deb33a0f989240e65a4b9ce1050e51');
cart.setVariable(item, 'stan_issue_category', 'Invoice Follow-up');
} else if (sessionid == "Standard Query") {
item = cart.addItem('edf0890b37d8fe0040ba70f543990e85');
cart.setVariable(item, 'stan_hr_issue_category', 'General Query');
} else if (sessionid == "Standard Learning Query") {
item = cart.addItem('86fb9e6e37a1e68040ba70f543990e74');
cart.setVariable(item, 'stan_hr_issue_category', 'HR Query');
}
user_id, country, company, phone, company_sysid;

cartId = GlideGuid.generate(null);
cart = new Cart(cartId);
item;

//Validation of Catalog Item-Starts here
if (sessionid == "Standard HR Query") {
item = cart.addItem('a9bb0be5379b524021865ca543990ebf');
cart.setVariable(item, 'stan_hr_issue_category', 'HR Query');
} else if (sessionid == "Standard Invoice Query") {
item = cart.addItem('b1deb33a0f989240e65a4b9ce1050e51');
cart.setVariable(item, 'stan_issue_category', 'Invoice Follow-up');
} else if (sessionid == "Standard Query") {
item = cart.addItem('edf0890b37d8fe0040ba70f543990e85');
cart.setVariable(item, 'stan_hr_issue_category', 'General Query');
} else if (sessionid == "Standard Learning Query") {
item = cart.addItem('86fb9e6e37a1e68040ba70f543990e74');
cart.setVariable(item, 'stan_hr_issue_category', 'HR Query');
}
//Validation of Requested for-Starts here
var domain, domain1;
var com = new GlideRecord("core_company");
com.addQuery('name', ritmcmpy);
com.query();
if (com.next()) {
domain = com.sys_domain;
domain1 = domain.getDisplayValue();
company_sysid = com.sys_id;
}
user_id;
var user = new GlideRecord("sys_user");
user.addQuery('sys_domain', domain);
user.addQuery('email', emailsender);
user.query();
if (user.next()) {
user_id = user.getValue('sys_id');

} else {
var default_user = new GlideRecord("sys_user");
default_user.addQuery('sys_domain', domain);
default_user.addQuery('user_name', 'CONTAINS', 'nobody');
default_user.query();
if (default_user.next()) {
user_id = default_user.getValue('sys_id');

}

}
var cartGR = cart.getCart();
gs.log("custmint50" + cartGR);
cartGR.requested_for = user_id;
var reqfor = cartGR.requested_for.getDisplayValue();
cartGR.update();
cart.setVariable(item, 'req_for', user_id);
cart.setVariable(item, 'u_country', country);
cart.setVariable(item, 'contact', phone);
cart.setVariable(item, 'company', company);
cart.setVariable(item, 'shrt_des', emailsubject);
cart.setVariable(item, 'des', str4);
var rc = cart.placeOrder();
number = rc.number;
gs.log("custmint51" + number);
var sysID = rc.sys_id;
gs.log("custmint52" + sysID);
var num = number.toString();
gs.log("custmint53" + num);
var num2 = '';
var sc_item = new GlideRecord('sc_req_item');
sc_item.addQuery('request', sysID);
sc_item.query();
if (sc_item.next()) {
num2 = sc_item.number.toString();
gs.log("custmint54" + num2);
sc_item.u_requestor = sc_item.opened_by;
sc_item.u_mailbox_address = userid;
sc_item.contact_type = 'email';
sc_item.urgency = '3 - Low';

sc_item.short_description = emailsubject;

str4 = GlideSPScriptable().stripHTML(emailbody);

sc_item.description = str4;
sc_item.u_hr_request_type = 'General Query';

var gr2 = new GlideRecord('core_company');
gr2.addQuery('name', ritmcmpy);
gr2.query();
if (gr2.next())
var cmpyal = gr2.sys_id.toString();
sc_item.company = cmpyal;
gs.log("custmint17" + cmpyal);
//assignment group validation with respect to company config.
var grint = new GlideRecord('u_company_config_intg');
grint.addQuery('u_company.name', ritmcmpy);
grint.addQuery('u_active=True');
grint.query();
if (grint.next()) {

var value = grint.getValue('u_language_translation_exclude_list');
gs.log("custmint42" + value);
var arr = value.split(',');
gs.log("custmint51" + arr);
var emailArr = [];
for (var i in arr) {
var arr1 = arr[i].split(':');
var obj = {};
obj["email"] = arr1[0].toString();
obj["otherValue"] = arr1[1].toString();
emailArr.push(obj);
}
var myFinalValue = '';
var inputvalue = userid;
for (var j = 0; j < emailArr.length; j++) {

if (inputvalue == emailArr[j].email) {
myFinalValue = emailArr[j].otherValue;

gs.log("custmint52 " + myFinalValue);
}
}

var gr1 = new GlideRecord('sys_user_group');
gr1.addQuery('name', myFinalValue);
gr1.query();
if (gr1.next()) {
var gpsyal = gr1.sys_id.toString();
sc_item.assignment_group = gpsyal;

}
} else {
sc_item.assignment_group = " ";
}
sc_item.update();
}
gr4 = sc_item.getValue('number');
gs.log("custmint8" + gr4);
gr4 = sc_item.sys_id.toString();
gs.log("custmint19" + gr4);

//mailbox address is passing to u_from
emailId4 = sc_item.u_mailbox_address;
gs.log("custmint21" + emailId4);
emailadddress3 = new GlideRecord('u_email_reply_address');
emailadddress3.addQuery('u_reply_to', emailId4);
emailadddress3.query();
if (emailadddress3.next())
ecfrom5 = emailadddress3.sys_id.toString();
gs.log("custmint22" + ecfrom5);
//Updated for u_from field in email client end

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();
ecsysid = gr5.getValue('sys_id');
gs.log("custmint61" + ecsysid);

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

ritmNumber2 = sc_item.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 Cancelled and closed ticket /RITM and email client entry/ends
else {

//Without Ritm in subject /Normal scenario starts

user_id, country, company, phone, company_sysid;

cartId = GlideGuid.generate(null);
cart = new Cart(cartId);
item;

//Validation of Catalog Item-Starts here
if (sessionid == "Standard HR Query") {
item = cart.addItem('a9bb0be5379b524021865ca543990ebf');
cart.setVariable(item, 'stan_hr_issue_category', 'HR Query');
} else if (sessionid == "Standard Invoice Query") {
item = cart.addItem('b1deb33a0f989240e65a4b9ce1050e51');
cart.setVariable(item, 'stan_issue_category', 'Invoice Follow-up');
} else if (sessionid == "Standard Query") {
item = cart.addItem('edf0890b37d8fe0040ba70f543990e85');
cart.setVariable(item, 'stan_hr_issue_category', 'General Query');
} else if (sessionid == "Standard Learning Query") {
item = cart.addItem('86fb9e6e37a1e68040ba70f543990e74');
cart.setVariable(item, 'stan_hr_issue_category', 'HR Query');
}
//Validation of Requested for-Starts here
domain, domain1;
com = new GlideRecord("core_company");
com.addQuery('name', ritmcmpy);
com.query();
if (com.next()) {
domain = com.sys_domain;
domain1 = domain.getDisplayValue();
company_sysid = com.sys_id;
}
user_id;
user = new GlideRecord("sys_user");
user.addQuery('sys_domain', domain);
user.addQuery('email', emailsender);
user.query();
if (user.next()) {
user_id = user.getValue('sys_id');

} else {
default_user = new GlideRecord("sys_user");
default_user.addQuery('sys_domain', domain);
default_user.addQuery('user_name', 'CONTAINS', 'nobody');
default_user.query();
if (default_user.next()) {
user_id = default_user.getValue('sys_id');

}

}
cartGR = cart.getCart();
gs.log("custmint50" + cartGR);
cartGR.requested_for = user_id;
reqfor = cartGR.requested_for.getDisplayValue();
cartGR.update();
cart.setVariable(item, 'req_for', user_id);
cart.setVariable(item, 'u_country', country);
cart.setVariable(item, 'contact', phone);
cart.setVariable(item, 'company', company);
cart.setVariable(item, 'shrt_des', emailsubject);
cart.setVariable(item, 'des', str4);
rc = cart.placeOrder();
number = rc.number;
gs.log("custmint51" + number);
sysID = rc.sys_id;
gs.log("custmint52" + sysID);
num = number.toString();
gs.log("custmint53" + num);
num2 = '';
sc_item = new GlideRecord('sc_req_item');
sc_item.addQuery('request', sysID);
sc_item.query();
if (sc_item.next()) {
num2 = sc_item.number.toString();
gs.log("custmint54" + num2);
sc_item.u_requestor = sc_item.opened_by;
sc_item.u_mailbox_address = userid;
sc_item.contact_type = 'email';
sc_item.urgency = '3 - Low';

sc_item.short_description = emailsubject;

str4 = GlideSPScriptable().stripHTML(emailbody);

sc_item.description = str4;
sc_item.u_hr_request_type = 'General Query';

gr2 = new GlideRecord('core_company');
gr2.addQuery('name', ritmcmpy);
gr2.query();
if (gr2.next())
cmpyal = gr2.sys_id.toString();
sc_item.company = cmpyal;
gs.log("custmint17" + cmpyal);

//assignment group validation with respect to company config.
grint = new GlideRecord('u_company_config_intg');
grint.addQuery('u_company.name', ritmcmpy);
grint.addQuery('u_active=True');
grint.query();
if (grint.next()) {

value = grint.getValue('u_language_translation_exclude_list');
gs.log("custmint42" + value);
arr = value.split(',');
gs.log("custmint51" + arr);
emailArr = [];
for (var i in arr) {
arr1 = arr[i].split(':');
obj = {};
obj["email"] = arr1[0].toString();
obj["otherValue"] = arr1[1].toString();
emailArr.push(obj);
}
myFinalValue = '';
inputvalue = userid;
for (var j = 0; j < emailArr.length; j++) {

if (inputvalue == emailArr[j].email) {
myFinalValue = emailArr[j].otherValue;

gs.log("custmint52 " + myFinalValue);
}
}

gr1 = new GlideRecord('sys_user_group');
gr1.addQuery('name', myFinalValue);
gr1.query();
if (gr1.next()) {
gpsyal = gr1.sys_id.toString();
sc_item.assignment_group = gpsyal;

}
} else {
sc_item.assignment_group = " ";
}
sc_item.update();
}
gr4 = sc_item.getValue('number');
gs.log("custmint8" + gr4);
gr4 = sc_item.sys_id.toString();
gs.log("custmint19" + gr4);

//mailbox address is passing to u_from
emailId4 = sc_item.u_mailbox_address;
gs.log("custmint21" + emailId4);
emailadddress3 = new GlideRecord('u_email_reply_address');
emailadddress3.addQuery('u_reply_to', emailId4);
emailadddress3.query();
if (emailadddress3.next())
ecfrom5 = emailadddress3.sys_id.toString();
gs.log("custmint22" + ecfrom5);
//Updated for u_from field in email client end

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();
ecsysid = gr5.getValue('sys_id');
gs.log("custmint61" + ecsysid);

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

ritmNumber2 = sc_item.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();

}

}
//Without Ritm in subject /Normal scenario ends
})(current, previous);

[kamlesh kjmar]

Hi @jobin1 ,

I would suggest first build your query as a string and then use it in your encoded query. Please refer to the below way. In the below script I have created an encoded query based on your script which says query the RITM table and look for records which either do not contains number which you have extracted from email or which is not exactly same as number in the subject.

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");
var query = "number!="+number+"^ORnumberNOT LIKE"+number
sc_item1.addEncodedQuery(query );
sc_item1.query();

I Hope this helps.

Please mark this helpful if this helps and Accept the solution if this solves your issue.

Regards,

Kamlesh

[jobin1]

Hi 

I need to validate the state as well in the query. Tried the below but not working.

glide RITM table and look for records that either  contains number which have extracted from the email and state should be one of 3,4,7,110 or which do not contains  number 

These 3 conditions need to consider  while querying 

var query = "number ="+number+"^stateIN3,4,7,110^ORnumber !="+number;