List collector for sys_user on Record Producer submit information into a HTML Field Table

[Peter Williams]

Good Morning everyone,

so i have a tricky request here.....

I have this list collector that is looking up information from the sys_user table on a record producer.

Once the request form is submitted what i would like is to have the users listed in the list collector to be collected and formatted into a table like form on a HTML variable for the fullfiller to see but also i want one column of the table structures be like this

Name	Department
John Smith	Accounting
Jack Wilson	HR

but i also want to have the information organized via department meaning everyone from the same department be groups together like this

Name	Department
John Smith, Jacky Ya, Peter Tyer	Accounting
Jance Yi, Aaron Nunes	HR

[Voona Rohila]

Hi @Peter Williams 

You want to send that format in the email? Did you try any approach?

Similar questions on adding table in email are answered below, Give a try and let us know the updated code so that we can help you out.

https://www.servicenow.com/community/developer-forum/how-to-create-table-in-notification-email-scrip...

https://www.servicenow.com/community/developer-forum/update-mail-script-to-include-a-table/m-p/20605...

[Peter Williams]

No Basically take the list collector information here and format it into a table

[Voona Rohila]

You can use the same approach to build the table using html tags

write a BR to update the description field when your list field changes.

[Peter Williams · Accepted solution]

i was able to figure it out via Business Rule

Created the BR on the Record Producer

Setting is After -> Insert and Update

Script is

(function executeRule(current, previous /*null when async*/) {

    var selectedNames = current.u_sm_legal_team.split(','); // Split the names into an array

    var userIds = []; // Array to hold resolved sys_ids

    var userNamesMap = {}; // Map to store user names by sys_id

    var departmentUsers = {}; // Object to group users by department

    // Resolve sys_ids based on names

    selectedNames.forEach(function(name) {

        var userGR = new GlideRecord('sys_user');

        userGR.addQuery('name', name.trim()); // Trim whitespace and query by name

        userGR.query();

        if (userGR.next()) {

            var sysId = userGR.sys_id.toString();

            var userName = userGR.name.toString();

            var department = userGR.u_department.getDisplayValue() || 'No Department'; // Get department

            userIds.push(sysId); // Add sys_id to the array

            // Group users by department

            if (!departmentUsers[department]) {

                departmentUsers[department] = [];

            }

            departmentUsers[department].push(userName); // Store user names by department

            userNamesMap[sysId] = userName; // Map sys_id to user name

        }

    });

    // Create the HTML table structure

    var html = '<table style="width:100%;border-collapse:collapse;"><thead><tr><th>Name</th><th>Department</th></tr></thead><tbody>';

    // Loop through each department and its user names

    for (var dept in departmentUsers) {

        var names = departmentUsers[dept].join(', '); // Join user names with commas

        html += '<tr><td>' + names + '</td><td>' + dept + '</td></tr>'; // Add row to HTML

    }

    html += '</tbody></table>';

    // Log the generated HTML for debugging

    // Store the formatted HTML in the u_sm_matter_desc_html field for the fulfiller

    current.u_sm_matter_desc_html = html;

    current.update();

})(current, previous);