how to auto populate the data in the tabular format using ui page.

Manoj50
Tera Contributor

This is the ui page that i have created in the ui based on the city of user i am trying to get the details as below ,

as per the script i am able to get the details of user column but i need to populate country and email of that user as well 

can any suggest me to achieve the same.

find_real_file.png

HTML code:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">

<html>
<style>
table {
border-collapse: collapse;
width: 100%;
}

th, td {
text-align: left;
padding: 8px;
}

tr:nth-child(even){background-color: #f2f2f2}

th {
background-color: #4CAF50;
color: white;
}
</style>

<body>
<input type="text" id="citytext" size="4"></input>
<br/>

<input type="button" onclick="showUsers()" name="Search" value="Search"></input>
<br/>
<div id="dvTable">
</div>

<p id="no_users" style="color:blue;display:none">No users matching query</p>

</body>
</html>

</j:jelly>

 

Client script :

function showUsers() {

var tableHeaders =  ['User','country','Email'];
var city = gel('citytext').value;

var gr = new GlideRecord("sys_user");
if (city != ''){
gr.addQuery('city', city);
gr.query();

if (gr.rows.length > 0) {

var table = document.createElement("TABLE");
table.border = "2";
table.padding = "10px";
table.id = "myTable";
var columnCount = tableHeaders.length;
var row = table.insertRow(-1);
for (var i = 0; i < columnCount; i++) {
var headerCell = document.createElement("TH");
headerCell.innerHTML = tableHeaders[i];
row.appendChild(headerCell);
}

while (gr.next()) {
row = table.insertRow(-1);

var user = row.insertCell(0);

if (gr.name != '') {
user.innerHTML = gr.name;
}
else {
user.innerHTML = '';
}

var dvTable = document.getElementById("dvTable");
dvTable.innerHTML = "";
dvTable.appendChild(table);
}
document.getElementById('no_users').style.display = 'none';
}
else{
document.getElementById('no_users').style.display = '';
var Table = document.getElementById("dvTable");
Table.innerHTML = "";
}

}
else{
var Table = document.getElementById("dvTable");
Table.innerHTML = "";
}
}

 

 

Thanks in advance.

1 ACCEPTED SOLUTION

Ankur Bawiskar
Tera Patron
Tera Patron

Hi,

I had shared something similar solution few years ago

enhance for other columns

Client Script: updated

function showUsers() {

    var tableHeaders =  ['User','country','Email'];
    var city = gel('citytext').value;

    var gr = new GlideRecord("sys_user");
    if (city != ''){
        gr.addQuery('city', city);
        gr.query();

        if (gr.rows.length > 0) {

            var table = document.createElement("TABLE");
            table.border = "2";
            table.padding = "10px";
            table.id = "myTable";
            var columnCount = tableHeaders.length;
            var row = table.insertRow(-1);
            for (var i = 0; i < columnCount; i++) {
                var headerCell = document.createElement("TH");
                headerCell.innerHTML = tableHeaders[i];
                row.appendChild(headerCell);
            }

            while (gr.next()) {
                row = table.insertRow(-1);

                var country = row.insertCell(0);
                var email = row.insertCell(1);

                var user = row.insertCell(0);

                if (gr.name != '') {
                    user.innerHTML = gr.name;
                }
                else {
                    user.innerHTML = '';
                }
                
                if(gr.email)
                    email.innerHTML = gr.email;
                
                if(gr.country)
                    country.innerHTML = gr.country;

                var dvTable = document.getElementById("dvTable");
                dvTable.innerHTML = "";
                dvTable.appendChild(table);
            }
            document.getElementById('no_users').style.display = 'none';
        }
        else{
            document.getElementById('no_users').style.display = '';
            var Table = document.getElementById("dvTable");
            Table.innerHTML = "";
        }

    }
    else{
        var Table = document.getElementById("dvTable");
        Table.innerHTML = "";
    }
}

Output:

find_real_file.png

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

View solution in original post

9 REPLIES 9

Ankur Bawiskar
Tera Patron
Tera Patron

Hi,

I had shared something similar solution few years ago

enhance for other columns

Client Script: updated

function showUsers() {

    var tableHeaders =  ['User','country','Email'];
    var city = gel('citytext').value;

    var gr = new GlideRecord("sys_user");
    if (city != ''){
        gr.addQuery('city', city);
        gr.query();

        if (gr.rows.length > 0) {

            var table = document.createElement("TABLE");
            table.border = "2";
            table.padding = "10px";
            table.id = "myTable";
            var columnCount = tableHeaders.length;
            var row = table.insertRow(-1);
            for (var i = 0; i < columnCount; i++) {
                var headerCell = document.createElement("TH");
                headerCell.innerHTML = tableHeaders[i];
                row.appendChild(headerCell);
            }

            while (gr.next()) {
                row = table.insertRow(-1);

                var country = row.insertCell(0);
                var email = row.insertCell(1);

                var user = row.insertCell(0);

                if (gr.name != '') {
                    user.innerHTML = gr.name;
                }
                else {
                    user.innerHTML = '';
                }
                
                if(gr.email)
                    email.innerHTML = gr.email;
                
                if(gr.country)
                    country.innerHTML = gr.country;

                var dvTable = document.getElementById("dvTable");
                dvTable.innerHTML = "";
                dvTable.appendChild(table);
            }
            document.getElementById('no_users').style.display = 'none';
        }
        else{
            document.getElementById('no_users').style.display = '';
            var Table = document.getElementById("dvTable");
            Table.innerHTML = "";
        }

    }
    else{
        var Table = document.getElementById("dvTable");
        Table.innerHTML = "";
    }
}

Output:

find_real_file.png

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

Hi Ankur,

Thanks for your response , The above code worked for me.

 

find_real_file.png

 

 

While we are trying to populate the reference field value , but we are getting the sys_id as shown in the above can you help me to achieve the same.

 

Client Script:

function showUsers() {

var tableHeaders = ['User','country code','Email','Manager','company'];
var city = gel('citytext').value;

var gr = new GlideRecord("sys_user");
if (city != ''){
gr.addQuery('city', city);
gr.query();

if (gr.rows.length > 0) {

var table = document.createElement("TABLE");
table.border = "2";
table.padding = "10px";
table.id = "myTable";
var columnCount = tableHeaders.length;
var row = table.insertRow(-1);
for (var i = 0; i < columnCount; i++) {
var headerCell = document.createElement("TH");
headerCell.innerHTML = tableHeaders[i];
row.appendChild(headerCell);
}

while (gr.next()) {
row = table.insertRow(-1);

var country = row.insertCell(0);
var email = row.insertCell(1);
var manager = row.insertCell(2);
var company = row.insertCell(3);

var user = row.insertCell(0);

if (gr.name != '') {
user.innerHTML = gr.name;
}
else {
user.innerHTML = '';
}

if(gr.manager)
manager.innerHTML = gr.manager;
if(gr.company)
company.innerHTML = gr.company;

if(gr.email)
email.innerHTML = gr.email;

if(gr.country)
country.innerHTML = gr.country;

var dvTable = document.getElementById("dvTable");
dvTable.innerHTML = "";
dvTable.appendChild(table);
}
document.getElementById('no_users').style.display = 'none';
}
else{
document.getElementById('no_users').style.display = '';
var Table = document.getElementById("dvTable");
Table.innerHTML = "";
}

}
else{
var Table = document.getElementById("dvTable");
Table.innerHTML = "";
}
}

 

getDispalyValue() , toString() both the functions i tried but it is not working for me.

Hi,

getDisplayValue() won't work in client side

try this

if(gr.manager)
manager.innerHTML = gr.manager.name;
if(gr.company)
company.innerHTML = gr.company.name;

If the above doesn't work then you need to query sys_user table and company table with sys_id and get the name

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

Hi Ankur,

we tried this manager.innerHTML = gr.manager.name; but it is not working. it is showing as undefined.

i am unable to understand where to place the query because on the client script we have  already GlideRecord the user table . can you please help me on this.

Thanks in advance

 

Regards,

manoj