Parsing email with Regular Expressions, then running GlideRecord Query

Jeremy Robb · ‎02-14-2017

I'm having a devil of a time trying to run a GlideRecord query from the results of a regular expression search.

I'm running a Business Rule to check for incoming email with a specific title (Inbound Email action didn't seem to want to work). The email contains the first name, last name, score number, and number of submissions all in an HTML-formatted body. Once the email is in the sys_email table, I run the script below.

Pulling the results isn't a problem using regular expressions, and I've been successful in pushing the results to gs.addInfoMessage so I know it's grabbing what I need. But, the bit below that's bolded and italicized, I just can't seem to get to work! I've tried forcing the results of the RegEx match to a string, tried identifying the item in the position of the array, nothing seems to work. If I don't try to tie it to a referenced user name but make the field a string-type field, it has no problems populating.

So my question is.. How do I run a GlideRecord query from the results of a RegEx Match?

Thanks for your help!

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

// Query the received email, pull the results for each line, and place results in array

var testData = current.body;

// RexEx to pull the last name

var patt1 = /[A-Za-z.\s-]+,/g;

//RegEx to pull the First Name

var patt2 = /\s[A-Za-z.\s]+\s-/g;

//RegEx to pull the score

var patt3 = /-\s[0-9]+.[0-9]+/g;

//RegEx to pull the number of respondents

var patt4 = /=[0-9]+/g;

var patt5 = /[A-Za-z.\s]+/g;

var patt6 = /[0-9]+.[0-9]+/g;

var patt7 = /[0-9]+/g;

var nameFirst = testData.match(patt2);

gs.addInfoMessage("Name First array is " + nameFirst);

var nameLast = testData.match(patt1);

//Pull score

var score = testData.match(patt3);

gs.addInfoMessage("Score array is " + score);

//pull number of submissions

var num = testData.match(patt4);

gs.addInfoMessage("Number of submissions array is " + num);

//Pull full name of user

for (i=0; i< nameFirst.length; i++) {

gs.addInfoMessage("First name of user " + (i+1) + " is " + nameFirst[i] + " and Last Name is " + nameLast[i]);

var firstNameRaw = nameFirst[i].match(patt5);

var firstName = firstNameRaw.toString();

var lastNameRaw = nameLast[i].match(patt5);

var lastName = lastNameRaw.toString();

var name = firstName + lastName;

var resultsRaw = score[i].match(patt6).toString();

var submissionsRaw = num[i].match(patt7).toString();

var results = parseFloat(resultsRaw);

var number = parseFloat(submissionsRaw);

gs.addInfoMessage("Name: " + name + ", First Name is " + firstName + ", Last Name is " + lastName + ", Score is " +

results + ", and number of submissions is " +

number + ".");

// var findName = new GlideRecord('sys_user');

// //findName.addQuery('first_name', firstName);

// //findName.addQuery('last_name', lastName);

// findName.addQuery('name', name);

// findName.query();

// if (findName.next()){

// var sys_id = findName.getValue('sys_id');

var addScore = new GlideRecord('u_quarterly_score');

addScore.query();

addScore.setValue('name', name);

addScore.setValue('score', results);

addScore.setValue('number_of_submissions', number);

addScore.update();

// } else {

// gs.addInfoMessage("Couldn't find a matching name");

// }

}

})(current, previous);]]>

JonathanJacob · ‎02-14-2017

Hi Jeremy, I have a couple of suggestions: First, you may want to try to grab the values using the built in key:value pair parsing, I have used this before with emails that contain html formatted text, this will simplify your code greatly. see http://wiki.servicenow.com/index.php?title=Inbound_Email_Actions#Setting_Field_Values_from_the_Email... for more info on this. In addition, you may want to try logging the typeof your values, this will help in case it is not getting cast to a string. Lastly, you can try using gr.field.setDisplayValue(something) - this will let look up the name for you, without needing a gliderecord, but I would suggest using gliderecord.