Autopopulate variables based on selection made in another variable

[WayneH08]

I have a custom app with a catalog item. For one of my use cases, I am trying to populate the first and last name variables based on the selection of the email address in a variable that references the custom table. In the attached screenshot the variable Contractor Email Lookup is the referenced field and based on this value I need to populate First Name and Last Name. 

I have created a Catalog Client Script:

function onChange(control, oldValue, newValue, isLoading) {
if (isLoading || newValue === '' || newValue === oldValue) {
return;
}

var EMAIL_FIELD = 'new_end_term_date_contractor';
var FIRST_NAME_FIELD = 'first_name';
var LAST_NAME_FIELD = 'last_name';

var email = g_form.getValue(EMAIL_FIELD);

if (!email || !email.includes('@')) {
g_form.showFieldMsg(EMAIL_FIELD, 'Invalid email address.', 'error');
return;
}

g_form.showFieldMsg(EMAIL_FIELD, 'Loading contractor details...', 'info');

var ga = new GlideAjax('GetContractorDetails');
ga.addParam('sysparm_name', 'getDetails');
ga.addParam('sysparm_email', email);

ga.getXMLAnswer(function(response) {
g_form.clearMessages();
try {
var details = JSON.parse(response);
if (details) {
g_form.setValue(FIRST_NAME_FIELD, details.first_name || '');
g_form.setValue(LAST_NAME_FIELD, details.last_name || '');
} else {
g_form.showFieldMsg(EMAIL_FIELD, 'No contractor details found.', 'error');
}
} catch (error) {
g_form.showFieldMsg(EMAIL_FIELD, 'Error fetching contractor details.', 'error');
console.error('Error parsing response:', error);
}
});
}

Also a Script Include:

var GetContractorDetails = Class.create();
GetContractorDetails.prototype = {
initialize: function() {},

getDetails: function() {
var result = {};

// Get the email parameter
var email = this.getParameter('sysparm_email');
if (!email) {
result.error = "Email parameter is missing.";
return JSON.stringify(result);
}

try {
// Query the custom table for the contractor details
var gr = new GlideRecord('x_unchc_unch_con_0_contractor_badge_requests');
gr.addQuery('email_address', email);
gr.query();

if (gr.next()) {
result.first_name = gr.getValue('first_name');
result.last_name = gr.getValue('last_name');
} else {
result.error = "No records found for the given email.";
}
} catch (e) {
gs.error("Error in GetContractorDetails Script Include: " + e.message);
result.error = "An unexpected error occurred while retrieving contractor details.";
}

return JSON.stringify(result); // Return the data as JSON
},

type: 'GetContractorDetails'
};

However the auto-population of First Name and Last Name is not happening. I'm hoping someone may be willing to review my work and provide guidance as to how I can get this working. Thank you.

[jcmings]

At first glance, your code looks fine to me. I think the issue is with your script include class declaration. Try shifting to this:

var GetContractorDetails = Class.create();
GetContractorDetails.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {

Edit to add -- also, when you do g_form.getValue(EMAIL_FIELD), please note that you are likely passing through a sys_id and not an email address.

[WayneH08]

Thanks for your quick response @jcmings 

I made the change to the script include, please see below. Still not working. In terms of "the g_form.getValue(EMAIL_FIELD), please note that you are likely passing through a sys_id and not an email address." - is there a change you'd recommend for this?

var GetContractorDetails = Class.create();
GetContractorDetails.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
    initialize: function() {},

    getDetails: function() {
        var result = {};

        // Get the email parameter
        var email = this.getParameter('sysparm_email');
        if (!email) {
            result.error = "Email parameter is missing.";
            return JSON.stringify(result);
        }

        try {
            // Query the custom table for the contractor details
            var gr = new GlideRecord('x_unchc_unch_con_0_contractor_badge_requests');
            gr.addQuery('email_address', email); 
            gr.query();

            if (gr.next()) {
                result.first_name = gr.getValue('first_name');
                result.last_name = gr.getValue('last_name');
            } else {
                result.error = "No records found for the given email.";
            }
        } catch (e) {
            gs.error("Error in GetContractorDetails Script Include: " + e.message);
            result.error = "An unexpected error occurred while retrieving contractor details.";
        }

        return JSON.stringify(result); // Return the data as JSON
    },

    type: 'GetContractorDetails'
});

[jcmings]

If I'm reading your form screenshot correctly, it looks like you have a reference field titled Contractor Email Lookup. You are grabbing its value (sys_id). But in your script include, your GlideRecord is trying to find an email address (I am assuming email_address is a string) but passing through the contractor's sys_id as the lookup value. You need to instead be looking up the contractor associated with that email address, since you're searching with a sys_id.

It would look something like this:

 var gr = new GlideRecord('x_unchc_unch_con_0_contractor_badge_requests');
            gr.addQuery('contractor', email); //changed email_address to contractor
            gr.query();

This GlideRecord is looking up the contractor whose sys_id matches the field selected on the catalog item.

[Jon23]

Hi @WayneH08,

Have you considered the OOB auto-populate functionality?

Does the Contractor Email Address reference the same table that holds the First/Last name data?

If YES, use the auto-populate functionality to fill in the first/last name fields.

If NO, create a new reference field that uses the other table, then auto-populate it based on the contractor email address. You can then use this field to auto-populate first/last name.

This will avoid any scripting 🙂