Multiple table search

[ベイカレエ]

I would like to create a search page using UIpage.

The conditions are as follows

・Create a search box.
・When a term is entered and the search button is clicked, two tables (sys_user and sys_user_group) are searched.
・If the term is found in the two tables, the records in the table with the term will be excerpted and displayed.

Can you give me a specific script to create this?

[Bhavya11]

Hi @ベイカレエ 

you can try something 

ui page pagesearch script 

<!-- Search Page UI -->
<div>
    <h1>Search Sys Users and Groups</h1>
    <input type="text" id="search_term" placeholder="Enter search term" />
    <button onclick="searchRecords()">Search</button>
</div>

<div id="search_results"></div>

<style>
    table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
    }
    th, td {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }
    th {
        background-color: #f2f2f2;
    }
    tr:nth-child(even) {
        background-color: #f9f9f9;
    }
    tr:hover {
        background-color: #e0e0e0;
    }
</style>

<script>
    function searchRecords() {
        var searchTerm = document.getElementById('search_term').value;
        if (!searchTerm) {
            alert('Please enter a search term');
            return;
        }

        // Make an AJAX call to the server-side GlideAjax script
        var ga = new GlideAjax('SearchSysUserAndGroup');
        ga.addParam('sysparm_name', 'searchRecords');
        ga.addParam('sysparm_search_term', searchTerm);
        ga.getXMLAnswer(function(response) {
            try {
                var result = JSON.parse(response);
                displayResults(result);
            } catch (e) {
                console.error("Failed to parse response: ", e);
                alert("An error occurred while processing the results. Please try again.");
            }
        });
    }

    function displayResults(results) {
        var resultDiv = document.getElementById('search_results');
        resultDiv.innerHTML = '';  // Clear previous results

        // Ensure results is an array
        if (!Array.isArray(results) || results.length === 0) {
            resultDiv.innerHTML = '<p>No results found.</p>';
            return;
        }

        // Create a table for displaying results
        var table = '<table><tr><th>Name of Table</th><th>Name</th></tr>';

        results.forEach(function(record) {
            // Ensure record has the expected properties
            var recordName = record.name || 'N/A';
            var recordTable = record.table || 'N/A';
            var recordCreated = record.created || 'N/A'; // Ensure 'created' exists or set a default
            
            
            
            table += '<tr><td>' + recordTable + '</td><td>' + recordName + '</td></tr>';
        });

        table += '</table>';
        resultDiv.innerHTML = table;
    }
</script>

Script include :

var SearchSysUserAndGroup = Class.create();
SearchSysUserAndGroup.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    searchRecords: function() {
        var searchTerm = this.getParameter('sysparm_search_term');
        var resultArray = [];

        // Search sys_user table
        var userGR = new GlideRecord('sys_user');
        userGR.addQuery('name', 'CONTAINS', searchTerm);
        userGR.query();
        while (userGR.next()) {
            resultArray.push({
                table: 'sys_user',
                name: userGR.getValue('name'),
                sys_id: userGR.getValue('sys_id')
            });
        }

        // Search sys_user_group table
        var groupGR = new GlideRecord('sys_user_grmember');
        groupGR.addQuery('user.name', 'CONTAINS', searchTerm);
        groupGR.query();
        while (groupGR.next()) {
            resultArray.push({
                table: 'sys_user_group',
                name: groupGR.group.getDisplayValue(),
                sys_id: groupGR.getValue('sys_id')
            });
        }

        // Return results as a JSON string
        return JSON.stringify(resultArray);
    }

});

output:

Please mark helpful & correct answer if it's really worthy for you.

Thanks,

BK

[ベイカレエ]

Where do I enter each of these on the UI page?
Above is "HTML"  and below is "client script"?

[ベイカレエ]

Where do I enter each of these on the UI page?
Above is the HTML and below is the client script?

[Bhavya11 · Accepted solution]

Hi @ベイカレエ ,

create record in sys_ui_page table

type sys_ui_page.LIST page 

like on new button then past below code there HTML section

<!-- Search Page UI -->
<div>
    <h1>Search Sys Users and Groups</h1>
    <input type="text" id="search_term" placeholder="Enter search term" />
    <button onclick="searchRecords()">Search</button>
</div>

<div id="search_results"></div>

<style>
    table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
    }
    th, td {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }
    th {
        background-color: #f2f2f2;
    }
    tr:nth-child(even) {
        background-color: #f9f9f9;
    }
    tr:hover {
        background-color: #e0e0e0;
    }
</style>

<script>
    function searchRecords() {
        var searchTerm = document.getElementById('search_term').value;
        if (!searchTerm) {
            alert('Please enter a search term');
            return;
        }

        // Make an AJAX call to the server-side GlideAjax script
        var ga = new GlideAjax('SearchSysUserAndGroup');
        ga.addParam('sysparm_name', 'searchRecords');
        ga.addParam('sysparm_search_term', searchTerm);
        ga.getXMLAnswer(function(response) {
            try {
                var result = JSON.parse(response);
                displayResults(result);
            } catch (e) {
                console.error("Failed to parse response: ", e);
                alert("An error occurred while processing the results. Please try again.");
            }
        });
    }

    function displayResults(results) {
        var resultDiv = document.getElementById('search_results');
        resultDiv.innerHTML = '';  // Clear previous results

        // Ensure results is an array
        if (!Array.isArray(results) || results.length === 0) {
            resultDiv.innerHTML = '<p>No results found.</p>';
            return;
        }

        // Create a table for displaying results
        var table = '<table><tr><th>Name of Table</th><th>Name</th></tr>';

        results.forEach(function(record) {
            // Ensure record has the expected properties
            var recordName = record.name || 'N/A';
            var recordTable = record.table || 'N/A';
            var recordCreated = record.created || 'N/A'; // Ensure 'created' exists or set a default
            
            
            
            table += '<tr><td>' + recordTable + '</td><td>' + recordName + '</td></tr>';
        });

        table += '</table>';
        resultDiv.innerHTML = table;
    }
</script>

goto navigation sys_script_include.LIST then click on new button

give name SearchSysUserAndGroup and

Client callable: true

then past this in script section

var SearchSysUserAndGroup = Class.create();
SearchSysUserAndGroup.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    searchRecords: function() {
        var searchTerm = this.getParameter('sysparm_search_term');
        var resultArray = [];

        // Search sys_user table
        var userGR = new GlideRecord('sys_user');
        userGR.addQuery('name', 'CONTAINS', searchTerm);
        userGR.query();
        while (userGR.next()) {
            resultArray.push({
                table: 'sys_user',
                name: userGR.getValue('name'),
                sys_id: userGR.getValue('sys_id')
            });
        }

        // Search sys_user_group table
        var groupGR = new GlideRecord('sys_user_grmember');
        groupGR.addQuery('user.name', 'CONTAINS', searchTerm);
        groupGR.query();
        while (groupGR.next()) {
            resultArray.push({
                table: 'sys_user_group',
                name: groupGR.group.getDisplayValue(),
                sys_id: groupGR.getValue('sys_id')
            });
        }

        // Return results as a JSON string
        return JSON.stringify(resultArray);
    }

});

Please mark helpful & correct answer if it's really worthy for you.

Thanks,

BK