UI page to display data from a table based on the search fields on the page

RiteshAlla · ‎11-15-2022

1) I want two separate search fields on the page - Select user from (sys_user) table. And Select Company from (core_company) table. When searching in user field, any data in Company field should be cleared and vice versa.

2) Based on user selected on the page, i want to show all the requested items and incidents that belong to that user. Page should allow to click on the task number and see the task.

Section of the page should also show user details from sys_user table. These user details should only be ReadOnly.

3) Based on the company field, i want to show all the requested items and incidents of that company. Page should allow to click on the task number and see the task.

Below is my code. My code works okay to show for Step 1 and part of step 2. For Step3, when searched for Company, no results show up on the page. It is blank. Please help

HTML:

<?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>
<body>

<table>

<th>Select User:</th>

<th><g:ui_reference name="userRecords" id="userRecords" table="sys_user" completer="AJAXTableCompleter" onchange="generateTable()"/></th>

<th>Select Company:</th>
<th><g:ui_reference name="compRecords" id="compRecords" table="core_company" completer="AJAXTableCompleter" onchange="generateTable()"/></th>

</table>

<br/>
<div id="dvTable2" style="overflow: hidden; height: 100%; width: 100%; position: absolute;" height="100%" width="100%">
</div>
<br/>
<br/>
<div id="dvTable">
<iframe id="incident" name="incident" src="" style="overflow: hidden; height: 100%;
width: 100%; position: absolute;" height="100%" width="100%"></iframe>
</div>
<br/>
<div id="dvTable3">
<iframe id="task" name="task" src="" style="overflow: hidden; height: 100%;
width: 100%; position: absolute;" height="100%" width="100%"></iframe>
</div>
</body>
</html>
</j:jelly>

Client Script:

function generateTable() {

var userSysId = gel('userRecords').value;
if (userSysId != '') {
var gr = new GlideRecord("incident");
gr.addQuery("caller_id", userSysId);
gr.orderBy('number');
//gr.addEncodedQuery('active=true^caller_id=userSysId');
//gr.addActiveQuery();
gr.query();

if (gr.rows.length > 0) {
document.getElementById('incident').src='/incident_list.do?sysparm_query=caller_idDYNAMIC' + userSysId;
document.getElementById('dvTable').style.display = '';
} else {
// no incs found so clear src of iframe and hide the div
alert("No INCs found");
document.getElementById('incident').src='';
document.getElementById('dvTable').style.display = 'none';
}

} else {
// remove the src attribute of iframe and show alert and hide the div
document.getElementById('incident').src='';
document.getElementById('dvTable').style.display = 'none';
}

var userSysId2 = gel('userRecords').value;
var tableHeaders = ['FirstName','LastName', 'Email', 'UserID','Company'];

if (userSysId2 != '') {
var gr2 = new GlideRecord("sys_user");
gr2.addQuery("sys_id", userSysId2);
gr2.query();

if (gr2.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 (gr2.next()) {
row = table.insertRow(-1);
var nameCell = row.insertCell(0);
var lastNameCell = row.insertCell(1);
var emailCell = row.insertCell(2);
var userIdCell = row.insertCell(3);
var companyCell = row.insertCell(4);

var comp = gr2.company;
var xy = '';
var cp = new GlideRecord('core_company');
cp.addQuery('sys_id', comp);
cp.query();
if(cp.next()){
xy = cp.name;
}

nameCell.innerHTML = gr2.first_name;
emailCell.innerHTML = gr2.email;
userIdCell.innerHTML = gr2.user_name;
lastNameCell.innerHTML = gr2.last_name;
companyCell.innerHTML = xy;

var dvTable2 = document.getElementById("dvTable2");

dvTable2.innerHTML = "";
dvTable2.appendChild(table);
dvTable2.appendChild("<h1>Hi</h1>");

}

} else {

alert("No user");
if (document.getElementById("dvTable2")) {
var Table = document.getElementById("dvTable2");
Table.innerHTML = "";
}
}

} else {

// this code will clear out the table when user is cleared out
var Table2 = document.getElementById("dvTable2");
Table2.innerHTML = "";
}

var userSysId3 = gel('compRecords').value;
if (userSysId3 != '') {
//var queryString = "active=true^company=userSysId3^sys_class_name=incident";

var gr3 = new GlideRecord("task");
//gr3.addQuery('active', true);
gr3.addQuery('company', userSysId3);
gr3.addQuery('sys_class_name', 'sc_req_item').addOrCondition('sys_class_name', 'incident');
// gr3.addQuery('sys_class_name', 'incident');
//gr3.addEncodedQuery(queryString);
gr3.orderBy('number');
//gr.addEncodedQuery('active=true^caller_id=userSysId');
//gr.addActiveQuery();
gr3.query();

if (gr3.rows.length > 0) {
document.getElementById('task').src='/task_list.do?sysparm_query=companyDYNAMIC' + userSysId3;
document.getElementById('dvTable3').style.display = '';
} else {
// no incs found so clear src of iframe and hide the div
alert("No tasks found");
document.getElementById('task').src='';
document.getElementById('dvTable3').style.display = 'none';
}

} else {
// remove the src attribute of iframe and show alert and hide the div
document.getElementById('task').src='';
document.getElementById('dvTable3').style.display = 'none';
}

}

RiteshAlla · ‎11-23-2022

I have solved this issue. Queried both Incident and Requested Item tables separately and combined them. It works okay now

View solution in original post

RiteshAlla · ‎11-15-2022

Hi @Ankur Bawiskar , Can you please check this question and help if you can. This question is similar to or in extension of the issue you helped here - https://www.servicenow.com/community/developer-forum/ui-page-to-display-table-data-based-on-referenc...

RiteshAlla · ‎11-23-2022

I have solved this issue. Queried both Incident and Requested Item tables separately and combined them. It works okay now