Join the #BuildWithBuildAgent Challenge! Get recognized, earn exclusive swag, and inspire the ServiceNow Community with what you can build using Build Agent.  Join the Challenge.

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

Go to solution
Manoj50
Tera Contributor

‎01-27-2022 03:43 AM

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.

Labels:
Preview file
22 KB
Preview file
21 KB
0 Helpfuls
  • 1,930 Views
1 ACCEPTED SOLUTION

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron

‎01-27-2022 04:03 AM

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

Preview file
41 KB
9 REPLIES 9

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron

‎01-27-2022 04:03 AM

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
Preview file
41 KB

Go to solution
Manoj50
Tera Contributor
In response to Ankur Bawiskar

‎01-27-2022 04:47 AM

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.

Preview file
80 KB
0 Helpfuls

Go to solution
Ankur Bawiskar
Tera Patron
Tera Patron
In response to Manoj50

‎01-27-2022 04:55 AM

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
0 Helpfuls

Go to solution
Manoj50
Tera Contributor
In response to Ankur Bawiskar

‎01-27-2022 06:18 AM

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

0 Helpfuls