Populating dl using glide Ajax with another table

[VinuvarshitSR]

I am trying to do it with Glide ajax

1. case Table Fields:

• Responsible Team (u_responsible_teams): This field is a reference to the sys_user_group table. It stores the team name (as referenced from the sys_user_group table).
• Priority (priority): This field stores the priority for the selected responsible team (integer type).
• Responsible Team DL (u_responsible_team_dls): This field will store the email addresses (comma-separated) retrieved based on the selected responsible team and priority.

2. u_dl_u_responsible_dl_lookup Table Fields:

• Responsible Team (u_responsible_team): This is a reference field, where the team name is stored. It corresponds to the u_responsible_teams field in the u_case table. both refer to group table
• Priority (u_priority): This is a string field that stores the priority value. This field must match the priority in the u_case table.
• Responsible DL (u_responsible_dl): This is a string field that stores the email address associated with the team for the given priority. This email needs to be fetched and populated in the u_responsible_team_dls field in the u_case table.

3. Objective:

• When a user selects a responsible team in the u_case table, the system should check for the priority and match it with records in the u_dl_u_responsible_dl_lookup table.
• The system should then retrieve the email addresses (u_responsible_dl) from the u_dl_u_responsible_dl_lookup table for that team and priority.
• The emails should be returned as a comma-separated string and populated in the u_responsible_team_dls field in the u_case table.
  
  

Script include
var ResponsibleTeamHandler = Class.create();
ResponsibleTeamHandler.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    // Function to fetch email addresses based on team names and priority
    fetchGroupEmails: function() {
        // Get the comma-separated team names and the priority from the client script
        var teamNames = this.getParameter('sysparm_teams'); // The responsible team names passed from client
        var priority = this.getParameter('sysparm_priority'); // The priority passed from client

        // If either team names or priority is not provided, return an empty string
        if (!teamNames || !priority) {
            return ''; // Return empty if no team names or priority are provided
        }

        // Convert the comma-separated team names string into an array
        var teamNamesArray = teamNames.split(','); // Convert the team names into an array

        var teamEmails = []; // Array to store email addresses

        // Create a GlideRecord query on the u_dl_u_responsible_dl_lookup table
        var gr = new GlideRecord('u_dl_u_responsible_dl_lookup');
        gr.addQuery('u_priority', priority); // Match the priority field
        gr.addQuery('u_responsible_team', 'IN', teamNamesArray); // Match the team names field
        gr.query(); // Execute the query

        // Loop through the records and collect the email addresses
        while (gr.next()) {
            if (gr.u_responsible_dl) {
                teamEmails.push(gr.u_responsible_dl.toString()); // Add the email to the array
            }
        }

        // Return the email addresses as a comma-separated string
        return teamEmails.join(', '); // Join the email addresses with commas and return
    },

    // Define the Script Include name
    type: 'ResponsibleTeamHandler'
});
​

---

CLient script
function onChange(control, oldValue, newValue, isLoading, isTemplate) {

    // Exit early if the form is loading or if there is no change in the value
    if (isLoading || newValue === '') {
        return;
    }

    // Get the value of the selected Responsible Teams (team name) and Priority
    var responsibleTeams = g_form.getValue('u_responsible_teams'); // The Responsible Team field value
    var priority = g_form.getValue('priority'); // The Priority field value

    // Use GlideAjax to call the Script Include
    var ga = new GlideAjax('ResponsibleTeamHandler');
    ga.addParam('sysparm_name', 'fetchGroupEmails'); // The function name to call in the Script Include
    ga.addParam('sysparm_teams', responsibleTeams); // Send the responsible team names to the Script Include
    ga.addParam('sysparm_priority', priority); // Send the priority value to the Script Include

    // Execute the GlideAjax request and handle the response
    ga.getXMLAnswer(function(answer) {
        // Populate the Responsible Team DL field with the comma-separated email addresses returned by the Script Include
        g_form.setValue('u_responsible_team_dls', answer); // The emails returned by the Script Include will be populated here
    });

}

The dls are not populating.

[Ankur Bawiskar]

@VinuvarshitSR 

field u_responsible_team is string type hence the query is not working.

Make it reference to sys_user_group and then it will work

If my response helped please mark it correct and close the thread so that it benefits future readers.

[VinuvarshitSR]

Hi @Ankur Bawiskar One small doubt i changed to Reference list should i select the team again and populate the data to make the above script work?

[Ankur Bawiskar]

@VinuvarshitSR

yes perform fresh testing

also clear the value everytime onchange runs

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
    if (isLoading || newValue === '') {
        return;
    }
   g_form.clearValue('u_responsible_team_dls');
    var responsibleTeams = g_form.getValue('u_responsible_teams');
    var priority = g_form.getValue('priority');

    alert('Responsible Teams: ' + responsibleTeams);
    alert('Priority: ' + priority);

    var ga = new GlideAjax('ResponsibleTeamHandler');
    ga.addParam('sysparm_name', 'fetchGroupEmails');
    ga.addParam('sysparm_teams', responsibleTeams);
    ga.addParam('sysparm_priority', priority);

    ga.getXMLAnswer(function(answer) {
        alert('Emails returned: ' + answer);
        g_form.setValue('u_responsible_team_dls', answer);
    });
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@VinuvarshitSR 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[VinuvarshitSR]

Hi @Ankur Bawiskar i tried with your solution but still dls are not populating. I have changed the Responsible team in lookup table to list and mapped the group in the table

var FetchResponsibleTeamDL = Class.create();
FetchResponsibleTeamDL.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    // Method to get responsible team DL emails based on team and priority
    // Method to get responsible team DL emails based on team name and priority
    // Function to retrieve emails for the responsible team and priority
    getEmailsForTeamAndPriority: function() {
        var responsibleTeamSysId = this.getParameter('sys_id');
        var priority = this.getParameter('priority');
        var emails = [];
        
        // Query the u_dl_u_responsible_dl_lookup table
        var gr = new GlideRecord('u_dl_u_responsible_dl_lookup');
        gr.addQuery('u_responsible_team', responsibleTeamSysId);
        gr.addQuery('u_priority', priority);
        gr.query();

        // Collect emails into an array
        while (gr.next()) {
            emails.push(gr.u_responsible_dl.toString()); // Assuming u_responsible_dl is the email field
        }
        
        // Return the emails as a comma-separated string
        return emails.join(',');
    },
    
    // Override the getAjaxAnswer function to return the result to the client script
    getAjaxAnswer: function() {
        return this.getEmailsForTeamAndPriority();
    },

    type: 'FetchResponsibleTeamDL'
});

// Client Script (onChange or onSubmit)
function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading) {
        return;
    }

    // Get responsible team and priority values
    var responsibleTeam = g_form.getValue('u_responsible_teams');
    var priority = g_form.getValue('priority');

    // Call GlideAjax to fetch emails based on responsible team and priority
    var ga = new GlideAjax('FetchResponsibleTeamDL');
    ga.addParam('sys_id', responsibleTeam); // Pass the responsible team sys_id
    ga.addParam('priority', priority); // Pass the priority value
    ga.getXMLAnswer(function(response) {
        var emails = response.responseXML.documentElement.getAttribute('answer');
        // Set the comma-separated emails to the field
        g_form.setValue('u_responsible_team_dls', emails);
    });
}