Inbound Email Action to Update User Record Sometimes Fails

[jmiskey]

We have an Inbound Email Action where we forward emails received in a department mailbox to ServiceNow to update a Transfer Date field found on the User Record.  The Inbound Email Action script parses the Employee Number and Transfer Date fields from the email, and is supposed to update the User Record.

I have verified through logging that it is pulling off the correct values for Employee Number and Transfer Date.  So there do not appear to be any issues there.

Here is the script:

//get values from email body
var ebody = email.body_text;

//find employee number
var ee_num = '';
var match = ebody.match(/Employee Number:\s*(\d{6,7})/);
if (match) {
    ee_num = match[1];
}

//get transfer date string
var temp2 = ebody;
var b = temp2.indexOf("Effective Date of Change:");
temp2 = temp2.substr(b + 25, b + 50);
temp2 = temp2.trim();
temp2 = temp2.substr(0, 10);

//convert string to date
var x = temp2.split(" ");  //date looks like "2023 10 18"
var yr = x[0];
var mo = x[1];
var dy = x[2];
var dte = yr + '-' + mo + '-' + dy;
var xfer_date = new GlideDate();
xfer_date.setValue(dte);
gs.log("ADD TRANSFER DATE: xfer_date = " + xfer_date);

//only update record if ee_num is not blank
if(ee_num!=''){
	//update Transfer Date on user record
	var usr = new GlideRecord('sys_user');
	usr.addQuery('employee_number', ee_num);
	usr.addQuery('active','true');
	usr.query();
	gs.log("ADD TRANSFER DATE: record count = " + usr.getRowCount());

	while (usr.next()) {
		gs.log("ADD TRANSFER DATE FOR: " + usr.employee_number);
		usr.u_transfer_date = xfer_date;
		usr.update();
		gs.log("ADD TRANSFER DATE RECORD UPDATED");
	}
}else{
	gs.log("ADD TRANSFER DATE: Cancelled as no Employee Number idenitified");
}

It sometimes works, but not all the time.  When it fails, the error message I get is:

"Did not create or update sys_user using current".

I am not sure why it works for some emails and not others.  I thought maybe at first it was a permissions thing, but I compared user records that worked versus records that don't, and those users have the exact same roles.  So that is not it.

Any idea why I get these errors on some people and not others? 

Any idea on how I might update my script to prevent this from happening?

[Ankur Bawiskar]

@jmiskey 

when it doesn't work does that inbound email action get processed?

any email filters is blocking that inbound email action to trigger?

Did you check that part?

[jmiskey]

Yes, as I said, I have already verified it is parsing the Employee Number and Transfer date values (through the Log statements included in the script).  So we know that the Inbound Email action is actually running.  All those log statements appear in the system logs.

If I open the email in ServiceNow, and look at the Email logs, here is what it shows for that particular Inbound Email Action ("Add Transfer Date" is the actual name of the Inbound Email Action):

[Ankur Bawiskar]

@jmiskey 

it means your script is unable to update that record

did you add few more logs and see if it's breaking at some line of code?

use this and see logs

// Get values from email body
var ebody = email.body_text;
gs.log("ADD TRANSFER DATE: Email body text = " + ebody);

// Find employee number
var ee_num = '';
var match = ebody.match(/Employee Number:\s*(\d{6,7})/);
if (match) {
    ee_num = match[1];
    gs.log("ADD TRANSFER DATE: Employee Number found = " + ee_num);
} else {
    gs.log("ADD TRANSFER DATE: Employee Number not found in email body.");
}

// Get transfer date string
var temp2 = ebody;
var b = temp2.indexOf("Effective Date of Change:");
if (b !== -1) {
    temp2 = temp2.substr(b + 25, 25).trim();
    temp2 = temp2.substr(0, 10);
    gs.log("ADD TRANSFER DATE: Effective Date of Change found = " + temp2);
} else {
    gs.log("ADD TRANSFER DATE: Effective Date of Change not found in email body.");
}

// Convert string to date
var xfer_date = new GlideDate();
try {
    var x = temp2.split(" "); // date looks like "2023 10 18"
    var yr = x[0];
    var mo = x[1];
    var dy = x[2];
    var dte = yr + '-' + mo + '-' + dy;
    xfer_date.setValue(dte);
    gs.log("ADD TRANSFER DATE: Parsed transfer date = " + xfer_date);
} catch (e) {
    gs.log("ADD TRANSFER DATE: Error parsing date - " + e.message);
}

// Only update record if ee_num is not blank
if (ee_num !== '') {
    // Update Transfer Date on user record
    var usr = new GlideRecord('sys_user');
    usr.addQuery('employee_number', ee_num);
    usr.addQuery('active', 'true');
    usr.query();
    gs.log("ADD TRANSFER DATE: Record count for employee number " + ee_num + " = " + usr.getRowCount());

    if (usr.getRowCount() > 0) {
        while (usr.next()) {
            gs.log("ADD TRANSFER DATE FOR: " + usr.employee_number);
            usr.u_transfer_date = xfer_date;
            usr.update();
            gs.log("ADD TRANSFER DATE RECORD UPDATED for user: " + usr.user_name);
        }
    } else {
        gs.log("ADD TRANSFER DATE: No active user found with Employee Number " + ee_num);
    }
} else {
    gs.log("ADD TRANSFER DATE: Cancelled as no Employee Number identified");
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[jmiskey]

OK, this is really bizarre.  I didn't want to make any changes to our active Production environment, so I exported the emails out of PRD and imported them to DEV.  I then tested my original script there, and it all worked! 

The DEV environment has not been cloned over in a few months, so I did the same test in another environment that was cloned just 2 days ago, and it worked there too!  So for some reason, I am unable to recreate the scenario in our sub-environments!  I am hesitant to make changes and test them in our PRD environment.

However note that my original script has lots of log statements already in it.  From one of the failed entries, here are the logs that resulted.  As you can see, it seems to go through each of the log statements successfully.