Struggling to populate MRVS from catalog item form

[TracyC]

I have been working on this for days using various other posts from the community and can't seem to get it.  I have a catalog item with a MRVS at the bottom of the form.  When a value (the household name) is populated on the main form, I need all the household members info to be populated in the MRVS row(s) WITHOUT clicking into it so the user can add/remove/update household members.  I feel like my scripts are "this close" to working but I need help.  I also keep getting an "unhandled exception in GlideAjax" error in the console.

SCRIPT INCLUDE

//The AJAX response gives total rows and all household member details.  All logs are showing the info is being retrieved and being sent to the client.
// https://www.servicenow.com/community/developer-articles/prefill-data-in-multi-row-variable-sets/ta-p/2298269 


var PopulateHouseholdMembers = Class.create();
PopulateHouseholdMembers.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
    getHouseholdMembers: function() {
        // Get household name from catalog item
        var household = this.getParameter('existingHouseholdName');
        gs.info("Received request to get household members for: " + household);

        var householdObj = {};
        var householdMembersArr = [];

        householdObj = new PopulateHouseholdMembers().getMemberDetails(household);
        if (householdObj) {
            householdMembersArr = [{
                "total_rows": householdObj["total_rows"],
                "household_member": JSON.stringify(householdObj["household_member"])
            }];
            var jsonString = JSON.stringify(householdMembersArr[0]);
            gs.info("Response sent to client: " + jsonString);
            return jsonString;
        } else {
            gs.error("Error: Failed to retrieve household member details.");
            return null;
        }
    },

    getMemberDetails: function(household) {
        var eachMbr = {};
        var eachMbrDet = [];
        var total_rows = 0;

        // Query the sys_id of the household based on its name
        var consumerGR = new GlideRecord('csm_consumer');
        consumerGR.addQuery('household.household_name', household); //query through the reference field
        consumerGR.query();

        // Check if a household record exists
        if (consumerGR.next()) {
            var householdSysId = consumerGR.getValue('household');
            gs.info('Found household record with sys_id: ' + householdSysId);

            // Query for household members matching the household sysid
            var householdMembersGR = new GlideRecord('csm_consumer');
            householdMembersGR.addQuery('household', householdSysId);
            householdMembersGR.query();

            while (householdMembersGR.next()) {
                total_rows++;
                eachMbr = {
                    first_name: householdMembersGR.getValue('first_name'),
                    middle_name: householdMembersGR.getValue('middle_name'),
                    last_name: householdMembersGR.getValue('last_name'),
                    suffix: householdMembersGR.getValue('suffix'),
                    dob: householdMembersGR.getDisplayValue('constituent.date_of_birth'),
                    gender: householdMembersGR.getValue('gender'),
                    race: householdMembersGR.getDisplayValue('constituent.race'),
                    ethnicity: householdMembersGR.getDisplayValue('constituent.ethnicity')
                };
                eachMbrDet.push(eachMbr);
            }
        } else {
            gs.info('No household record found for: ' + household);
        }

        if (total_rows > 0) {
            gs.info("Total rows: " + total_rows);
            gs.info("Household member details: " + JSON.stringify(eachMbrDet));

            return {
                "total_rows": total_rows,
                "household_member": eachMbrDet
            };
        } else {
            return null;
        }
    },

    type: 'PopulateHouseholdMembers'
});

//CATALOG CLIENT SCRIPT


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

    // Retrieve the name of the existing household from the form
    var existingHouseholdName = g_form.getValue('existing_household_name');
    console.log("Existing household name: " + existingHouseholdName);

    // Check if the existing household name is not empty
    if (existingHouseholdName) {
        console.log("Querying household members for: " + existingHouseholdName);

        // Call the server-side function to get household members
        queryHouseholdMembers(existingHouseholdName);
    } else {
        console.log("Existing household name is empty.");
    }
}

function queryHouseholdMembers(existingHouseholdName) {
    var ga = new GlideAjax('PopulateHouseholdMembers'); // Script Include name
    ga.addParam('sysparm_name', 'getHouseholdMembers');
    ga.addParam('existingHouseholdName', existingHouseholdName); // Pass household name to script include

    ga.getXMLAnswer(function(response) {
        console.log("AJAX Response: ", response);

        // Process the response
        var householdObj = JSON.parse(response);
        if (!householdObj || !householdObj.household_member) {
            console.error("Error: No household member data found.");
            return;
        }

        // Clear existing rows in MRVS
        var my_varset = g_form.getControl("household_member");
        my_varset.clearRows();

        // Populate MRVS with household member data
        var householdMembers = householdObj.household_member;
        for (var i = 0; i < householdMembers.length; i++) {
            var member = householdMembers[i];
            var newRow = my_varset.addRow();

            newRow.setValue('first_name', member.first_name);
            newRow.setValue('middle_name', member.middle_name);
            newRow.setValue('last_name', member.last_name);
            newRow.setValue('suffix', member.suffix);
            newRow.setValue('dob', member.dob);
            newRow.setValue('gender', member.gender);
            newRow.setValue('race', member.race);
            newRow.setValue('ethnicity', member.ethnicity);
        }
    });
}

function parseData(householdObj) {
    var my_varset = g_form.getControl("household_member"); // Get the MRVS control

    console.log("MRVS Control:", my_varset); // Log my_varset to inspect its properties

    // Clear existing rows in MRVS
    my_varset.clearRows();

    if (householdObj && householdObj.household_member) {
        var householdMembers = householdObj.household_member;
        console.log("Parsed household member data: ", householdMembers);

        for (var i = 0; i < householdMembers.length; i++) {
            var member = householdMembers[i];
            var newRow = my_varset.addRow();

            newRow.setValue('first_name', member.first_name);
            newRow.setValue('middle_name', member.middle_name);
            newRow.setValue('last_name', member.last_name);
            newRow.setValue('suffix', member.suffix);
            newRow.setValue('dob', member.dob);
            newRow.setValue('gender', member.gender);
            newRow.setValue('race', member.race);
            newRow.setValue('ethnicity', member.ethnicity);
        }
    } else {
        console.log("No household member data found.");
    }
}

CONSOLE LOG:

SYSTEM LOG:

 

[Nick Parsons · Accepted solution]

Where are you trying to run this client script? On the Service Portal? If so, g_form.getControl() is not supported on the Service Portal (according to the ServiceNow docs), you'll need to create an array of objects and call setValue on your multirow variable set to update it. 

The other thing that you should be aware of is your code is double-stringifying your data. Once for the response object and once for the inner household_member array. If you wish to loop through househouse_members, then you should be parsing that as well:

var householdMembers = JSON.parse(householdObj.household_member);

But with that being said, you don't need to loop through it like you are if you ditch the form-control idea and instead just call:

// Update the household_member variable set to hold the stringified array of objects from the householdObj.household_member property
g_form.setValue("household_member", householdObj.household_member)

[TracyC]

OMG it worked!!  Thank you so much!  I've been staring at so much code I didn't even notice I had the same section twice.  

Here's the final script if it can help anyone else.

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

    // Retrieve the name of the existing household from the form
    var existingHouseholdName = g_form.getValue('existing_household_name');
    // console.log("Existing household name: " + existingHouseholdName);

    // Check if the existing household name is not empty
    if (existingHouseholdName) {
        // console.log("Querying household members for: " + existingHouseholdName);

        // Call the server-side function to get household members
        queryHouseholdMembers(existingHouseholdName);
    } else {
        // console.log("Existing household name is empty.");
    }
}

function queryHouseholdMembers(existingHouseholdName) {
    var ga = new GlideAjax('PopulateHouseholdMembers'); // Script Include name
    ga.addParam('sysparm_name', 'getHouseholdMembers');
    ga.addParam('existingHouseholdName', existingHouseholdName); // Pass household name to script include

    ga.getXMLAnswer(function(response) {
        // console.log("AJAX Response: ", response);

        // Process the response
        var householdObj = JSON.parse(response);
        if (!householdObj || !householdObj.household_member) {
            console.error("Error: No household member data found.");
            return;
        }

        // Populate MRVS with household member data
		g_form.setValue("household_member", householdObj.household_member);

    });
}