How to check email address from a string field and fetch from sys_user table and populate name

[Amol Pawar]

Hi Experts,

I want to populate the user's name in a field named 'Responsible Person' which we have to check from the 'Email Address' field on a record producer's multi row variable set.

In above image, Email Id is a string field. We have to check that email id from sys_user table and if it's there, we have to populate that user's name in the next field which is 'Responsible person'.

Let me know the approach and possible solution on it.

Thanks in advance,

Amol Pawar

[Brad Bowman]

You need to write an onChange Catalog Client Script that applies to the MRVS, not the Catalog Item, when the email variable changes.  This script will use a GlideAjax call to pass the newValue to a Script Include which will lookup the user record based on the email and return the name which the client script will then populate in the other variable.  

Here is an excellent guide on the process

https://www.servicenow.com/community/developer-articles/glideajax-example-cheat-sheet-updated/ta-p/2... 

Give it a shot and post your scripts using the insert code </> icon if you get stuck.

[Amol Pawar]

Hi @Brad Bowman ,

I appreciate your response. I tried this approach but this is not working for me as expected. I'm adding my code here, please review it and let me know where I'm missing:

Catalog Client Script:

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading || newValue == '') {

        return;

    }

    //Type appropriate comment here, and begin script below

    var ga = new GlideAjax('GetUserNameFromEmail');

    ga.addParam('sysparm_name', 'getUserName');

    ga.addParam('sysparm_email', newValue);

    ga.getXMLAnswer(function(response) {

        var userName = response;

        if (userName) {

            g_form.setValue('responsible_person', userName);

        } else {

            g_form.setValue('responsible_person', '');

        }

    });

}

Script Include:

var GetUserNameFromEmail = Class.create();

GetUserNameFromEmail.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

    initialize: function() {},

    getUserName: function(email) {

        var user = new GlideRecord('sys_user');

        user.addQuery('email', email);

        user.query();

        if (user.next()) {

            return user.getDisplayValue('name');

        }

        return '';

    },

    type: 'GetUserNameFromEmail'

});

I'm typing the correct email id in the field but it's not populating the name in the Responsible person field.

My email field name is 'email' is same as user table column name email, is it conflicting somehow?

Let me know your thoughts on this?

Thank you,

Amol

[Medi C · Accepted solution]

@Amol Pawar 

Please adjust your function to:

getUserName: function() {
        var email = this.getParameter('sysparm_email');
        var user = new GlideRecord('sys_user');
        user.addQuery('email', email);
        user.query();
        if (user.next()) {
            return user.getDisplayValue('name');
        }
        return '';
    },

[Amol Pawar]

Hi @Medi C,

Thank you so much for your reply.

Thank you @Ankur Bawiskar, for your advise to not use initialize method.

with these minor changes, this is working fine.

Thank you @SyedMahemoH.

Regards,

Amol