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.

[Ahmet1]

Hi,

Many thanks for your assistance! I fixed this by changing my 

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

to

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

Along with the function getUserID code you supplied it worked! Matched correct caller ID.

So this code is to fetch the sys ID for email matching in sys_user table, how can I apply this same logic to search cmn_location table to match Location: London in email body to cmn_location field "Name" and get the sys ID?

This is my final code that works now for caller_id:

createRequest();
function createRequest() {
var cart = new Cart();
// add in cart, substitute your catalog item sys_id
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.caller_id));
	cart.setVariable(item, 'full_name', email.body.full_name);
	cart.setVariable(item, 'u_phone', email.body.u_phone);
	cart.setVariable(item, 'job_title', email.body.job_title);
	cart.setVariable(item, 'post_reference', email.body.post_reference);
	cart.setVariable(item, 'planned_start_date', email.body.planned_start_date);
	cart.setVariable(item, 'location', email.body.location);


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('email',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;
}

I want to do the same for "location" , search the value from location: in email which is "Location: London" search this in cmn_location table and match it to field "name" in this table and if matched, return the sys id for location. This will help allot thanks!

[vkachineni]

Check if the Location in the email matches location table field "name".

Or it could be "city" field and return the sys_id

[Ahmet1]

I figured it out by amending some in script to reflect location etc. its working! Thanks so much