Inbound email action to populate reference variable in Request Item using gliderecord

[Ahmet1]

Hello,

Our line managers fill out a Power BI Form for new starters which generates an email to various systems including ServiceNow to inform of new starter.

We have an inbound action script setup to create a New starter request on ServiceNow populating variables from the email body.

The script populates everything fine except for reference variables, we have tried the following solution for getting sys ID using Gliderecord but its returning incorrect sys ID (not the sys ID matching the line manager in body).

Current script in use:

createRequest();
function createRequest() {
var cart = new Cart();
var item = cart.addItem('515b9d0437a3f60047700ba754990e3d');
if (email.body.full_name != undefined && email.body.u_phone != undefined && email.body.job_title != undefined)

cart.setVariable(item, 'caller_id', getUserId(email.body_text.caller_id.toString()));
	cart.setVariable(item, 'hr_dept_choice', email.body.hr_dept_choice);
	cart.setVariable(item, 'appointment_type', email.body.appointment_type);
	cart.setVariable(item, 'planned_start_date', email.body.planned_start_date);

var rc = cart.placeOrder();
}

function getUserId(email) {
var getSysId = new GlideRecord('sys_user');
getSysId.addQuery('user_name',email); 
getSysId.setLimit(1);
getSysId.query();
if(getSysId.next()){
return getSysId.sys_id;
}}

Email body that creates the request:

caller_id: Testuser@company.com
hr_dept_choice: lbb
appointment_type: permanent 
planned_start_date: 2022-03-16

If we have a user record in sys_user where the "email" field matches testuser@company.com , the script is returning the sys ID for another record in sys_user that has a different email not matching the email body, i.e. testuser321@company.com which is not what we want. The script is not returning accurate sys ID. 

All other variables in request item are correctly populated from this email however the caller_id variable is not populated correctly its not matching what is in email.

I would greatly appreciate any help and would assume this would be useful for many use cases and also others to find helpful.

[vkachineni]

If the data you are looking up is in the 'email' field then

the line should be

getSysId.addQuery('email',email);

[Ahmet1]

Hi vkachineni,

Thanks for the reply, unfortunately its returning sys ID for a record in sys_user with blank email address (incorrect record/not matching what is in email body). 

[vkachineni · Accepted solution]

//Add some logs and see what is coming in

function getUserId(email) {
	var user_sys_id = "-1"; //No user found
	gs.log("Incoming email lookup:" + email, "@@@Testing");
	email = email.trim();//remove any leading spaces.
	var getSysId = new GlideRecord('sys_user');
	getSysId.addQuery('user_name',email); //check if you are looking up in the correct field with email.
	getSysId.setLimit(1);
	getSysId.query();
	if(getSysId.next()){
		user_sys_id = getSysId.sys_id.toString();
		gs.log("Found user with email:" + getSysId.email + " name=" + getSysId.name, "@@@Testing");
	}
	return user_sys_id;
}

[Ahmet1]

Ok, thanks.

So here's the log for incoming email , it appears to be "undefined"

Therefore its finding record in sys_id with blank email address... 

Incoming email contents:

caller_id: user@company.com

planned_start_date: 2021-09-08

hr_dept_choice: lbb

appointment_type: permanent

[vkachineni]

As Alan suggested try sending in

email.body.caller_id.toString()

cart.setVariable(item, 'caller_id', getUserId(email.body.caller_id.toString()));

If that doesn't work try parsing out the caller id:

//Update this line to get the email
cart.setVariable(item, 'caller_id', getUserId(parseBody("caller_id:")));

function getUserId(email) {
	var user_sys_id = "-1"; //No user found
	gs.log("Incoming email lookup:" + email, "@@@Testing");
	email = email.trim();//remove any leading spaces.
	var getSysId = new GlideRecord('sys_user');
	getSysId.addQuery('user_name',email); //check if you are looking up in the correct field with email.
	getSysId.setLimit(1);
	getSysId.query();
	if(getSysId.next()){
		user_sys_id = getSysId.sys_id.toString();
		gs.log("Found user with email:" + getSysId.email + " name=" + getSysId.name, "@@@Testing");
	}
	return user_sys_id;
}

//Add the below two utility functions to get the value from email body text
function parseBody(parseString)
{
	var parsedGroup = "";
	var bodySplit = email.body_text.split("\n");
	for(var i = 0; i < bodySplit.length; i++){
		if(bodySplit[i].indexOf(parseString) != -1){
			parsedGroup = grabContent(bodySplit[i], parseString);
			return parsedGroup;
		}
	}
}

function grabContent(str, start) 
{   
	var startLen = start.length;   
	var s = str.indexOf(start);    
	var scrape = str.substring(s+startLen);   
	return scrape.trim();
}