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.

[Allen Andreas]

Hi,

You'd want to verify the typeof of the "email" parm that you're passing in to your function. Please add log statement both clarifying what you've got and what typeof it is. Most likely, it's not in correct format, possibly, so that in your GlideRecord is getting completely ignored and it's just finding a random record and using that.

I would consider using:

email.body.caller_id.toString()

Not "body_text", and then check that in your function and ensure it's good to go.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Allen Andreas]

Hi,

As was pointed out below, you'd need to change it to "email" instead of "user_name", if that's the field you want to query against.

Please refer to GlideRecord documentation for further assistance: https://developer.servicenow.com/dev.do#!/reference/api/paris/server/no-namespace/c_GlideRecordScope...

But as I mentioned above, you'd want to double-check what you are passing in to your function and also as I mentioned, the reason why it's finding a random user is because that query line where you're using the email is most likely incorrect, so it's skipping it in the query and just doing a query against the sys_user pulling a random user or first user, etc.

So, it's more than just the "email" needing to be there instead of "user_name", I believe, but yes, that too, would need to be changed. I assumed you had that part correct because you got "user_name" from somewhere? and you knew the field: email?

Please mark reply as Helpful/Correct, if applicable. Thanks!

[vkachineni]

Please adjust the function. comments inline.

function getUserId(email) {
	var user_sys_id = "-1"; //No user found
	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();
	}
	return user_sys_id;
}

[Ahmet1]

I noticed we have getSysId.addQuery('user_name',email) 

Our sys_user table contains the email address of user in field called "email", so we want to search the email in body in this table and return the sys ID matching field email. Do I need to change this line? 

I understand my original script may be incorrect hence why I'm seeking guidance to correct it 

So email comes in where the email body contains " caller_id: user@company.com "

We want ServiceNow to look up the value  " user@company.com " in the sys_user table and return sys ID for record matching this value in "email" field in sys_user.