Adding data from a custom table to a catalog item

[Alan42]

I've created a custom table that will be cleared and updated every few weeks with employee vacation data.    I need to populate a field on a catalog item with a field named 'vacationbalance'.  I've created a script include named ‘VacationHoursFetcher’ that's testing out properly.    

Script include named ‘VacationHoursFetcher’:

var VacationHoursFetcher = Class.create();

VacationHoursFetcher.prototype = Object.extend(new AbstractAjaxProcessor(), {

    getVacationHours: function(email) {

        var hours = 0;

        var gr = new GlideRecord('x_atrr_canadian_0_canadian_vacation');

        gr.addQuery('emailid', email); // Ensure this is the correct column name

        gr.query();

        if (gr.next()) {

            hours = gr.getValue('vacationbalance'); // Ensure this field name is correct

        }

        return hours;

    },

    type: 'VacationHoursFetcher'

});

Verification Test :

var test = new VacationHoursFetcher();

var result = test.getVacationHours('*****@*******.com'); // Use the test email address

gs.info('Vacation hours for jdenchev@atlasroofing.com: ' + result); // Log the result

Verification Test Results

*** Script: Vacation hours for *****@*******.com: 81.33

That's the correct hours from the user field so its working properly.

My understanding is that I should use a catalog client script on the item but what I've come up with isn't working.    I'm using a read only, single line text variable named 'vacationbalance' which I think the script below should populate with the users data.    

function onLoad() {

    var userEmail = g_user.email;

    var ga = new GlideAjax('VacationHoursFetcher');

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

    ga.addParam('email', userEmail); // Ensure this parameter matches what the Script Include expects

    ga.getXMLAnswer(function(response) {

        var availableHours = response.responseXML.documentElement.getAttribute('answer');

        g_form.setValue('vacationbalance', availableHours); // Ensure this field ID is correct

    });

}

There are no obvious errors on the console but the data isn't populating the 'vacationbalance' field for the user.   Can you see anything that would cause the values from the script include to not display?  The application is global, applies to a catalog item, type of OnLoad, and applies on a catalog view is checked.     

[Alan42]

How would that work?     Replace 'var userEmail = g_user.email;' with the address?

function onLoad() {
var userEmail = g_user.email;
console.log('User Email: ', userEmail); // Debugging line

var ga = new GlideAjax('VacationHoursFetcher');
ga.addParam('sysparm_name', 'getVacationHours');
ga.addParam('email', userEmail);
ga.getXMLAnswer(function(response) {
var availableHours = response.responseXML.documentElement.getAttribute('answer');
console.log('Available Vacation Hours: ', availableHours); // Debugging line

if (availableHours !== null) {
g_form.setValue('vacationbalance', availableHours);
} else {
console.log('No vacation hours returned.'); // Debugging line
}
});
}

[Slava Savitsky]

Yes, replace g_user.email in your client script with the same email address you used for server-side testing of your script include:

var userEmail = 'jdenchev@atlasroofing.com';

If your code works correctly after that, it means that the problem is indeed caused by fact that you are using a non-existent property of the g_user object.

[Alan42]

Thanks.  Got it working using the script include and catalog client script below:    

Catalog client script

function onLoad() {
// var ga = new GlideAjax('VacationHoursFetcher');
var ga = new GlideAjax('global.VacationHoursFetcher');
ga.addParam('sysparm_name', 'getVacationHours');
ga.addParam('email', ''); // Pass empty string
ga.getXMLAnswer(function(response) {
if (response) {
try {
//var availableHours = response.getXMLAnswer().getText();
var availableHours = response;
g_form.setValue('vacationbalance', availableHours);
console.log('Set vacationbalance to:', availableHours);
} catch (error) {
console.error('Error parsing response:', error.message);
}
} else {
console.error('No response received from VacationHoursFetcher');
}
});
}

Script include

var VacationHoursFetcher = Class.create();
VacationHoursFetcher.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getVacationHours: function(email) {
if (JSUtil.nil(email)) {
email = gs.getUser().getEmail(); // Retrieve email here
}
gs.log('Received email parameter: ' + email); // Log the received email
var vacationBalance = 0;
var gr = new GlideRecord('x_atrr_canadian_0_canadian_vacation');
gr.addQuery('emailid', email);
gr.query();
if (gr.next()) {
vacationBalance = gr.getValue('vacationbalance');
}
gs.log('Vacation balance for ' + email + ': ' + vacationBalance); // Log the vacation balance
return vacationBalance.toString(); // Ensure it's returned as a string
}
});