Returning a Primary Key from a Table using GlideRecords()

[Joe Webster]

Hey all, I had a question about querying a table in my ServiceNow instance. I'm currently trying to write a Virtual Agent tool which creates Requests within the Request Table, based on each User's unique identifier (username). The username is not the primary key for the table which houses the Users, but their username can be used as a Reference to the table to link them to Requests/Incidents/etc. 

My script currently gets the username from user input, then checks to see if it needs to be trimmed (if the user's username contains part of their email, which the username is based off of). Then, it creates a new GlideRecord based on the given Users table. This is the part where I am a bit lost. I need to return a Reference to the row containing the given username within the Users table so I can create Requests or other items in other tables successfully.

This is my current code: 

    var input_user_name = vaInputs.get_verified_username;//should be a string from text input
    var i = 0;
    var index_of_at = 0;
    var final_user_name = "";

    // we don't need to query with the @email.com portion of an email in case the user puts it in.
    if (input_user_name.includes('@')) {
        // loop to find the placement of @
        for (i = 0; i < input_user_name.length; i++) {
            if (input_user_name[i] == '@') {
                index_of_at = i;
            }
        }

        // substring of everything up to but not including @.
        final_user_name = input_user_name.substring(0, index_of_at);
    }
    else {
        final_user_name = input_user_name;
    }

    var encoded_query = "username=" + final_user_name;
    var gr = new GlideRecord(table);
    gr.addEncodedQuery(encoded_query);
    gr.query();
    if (gr.next()) {
        // here i need to return a reference to the user on the User table based off
        // their final_user_name.
        return gr.getValue(encoded_query);
    }

[Chris York]

Are you prompting the user for their username as a text input in the Virtual Agent Topic? If so, why not just use a Reference Field and have the user lookup the correct sys_user record that way. You would already know the unique sys_id if a reference field is used.

I also don't see in your code where you are setting the 'table' variable. Should be sys_user

With that said, if you left everything the same, you would want to return the sys_id column from the sys_user table. (currently you have it returning the encoded_query variable which is incorrect)

[Joe Webster]

Hey thanks for the quick response! 

I just changed it to return the sys_id and it pulled the wrong user name in for the ticket. I'm wondering if I don't need to use gr.next() in this scenario.

I'll look into using a Reference Field for username lookup and get the sys_id that way.

[Chris York]

gr.next() needs to be there to grab the 1st result in the result set.

[siva_ · Accepted solution]

Hello Joe, 

I am not sure how you are approaching but this would be my approach 

Codes used in the blocks : 

Choice Value expression :

(function execute(table) {

/* Write a glide record query, and return the glide record.

For example:*/

var input_user_name = vaInputs.user_name;//should be a string from text input

var i = 0;

var index_of_at = 0;

var final_user_name = "";

var options = [];

// we don't need to query with the @email.com portion of an email in case the user puts it in.

if (input_user_name.includes('@')) {

// loop to find the placement of @

for (i = 0; i < input_user_name.length; i++) {

if (input_user_name[i] == '@') {

index_of_at = i;

}

}

// substring of everything up to but not including @.

final_user_name = input_user_name.substring(0, index_of_at);

}

else {

final_user_name = input_user_name;

}

var encoded_query = "user_nameLIKE"+input_user_name;

var gr = new GlideRecord(table);

gr.addEncodedQuery(encoded_query);

gr.query();

while (gr.next()) {

// here i need to return a reference to the user on the User table based off

// their final_user_name.

options.push({ 'value': gr.getUniqueValue(), 'label': gr.getValue('user_name')});

}

return options;

})(table)

Link Output block script  

(function execute() {

var url = "https://devxxxxx.service-now.com/sys_user.do?sys_id=";

return url+vaInputs.select_the_exact_user;

})()

Hope this helps 

Mark this response as correct if that really helps

Thanks,

Siva