Sys Admin useful Service Portal widget to check for duplicates in tables

UPDATE: 

newer improved version here:

===========

Widget defined in global scope; note can set cross scope privileges for access to scoped tables

[- should generate entries in sys_restricted_caller_access which can then be set to permitted]

HTML:

<title>Check ServiceNow Table for duplicates</title>

<div class='container'>

<!-- your widget template -->

  <label>Enter table name here</label>

    <input value ='sys_user' id='tbl_name'>

  <br/>

  <label>Enter field name here</label>

    <input value='email' id='dupl_field'>

  <br/>

  <label>Custom query string</label>

    <input value='active=true' id='custom_query'>

  <br/>

  <br/><br/><button ng-click="runtheoperation()" class="btn btn-danger action-btn pull-left">Click to check dupl</button>

<br/><br/>

   <label>results</label><br/>

   <textarea id='results_q' rows="10" cols="100">{{c.duplrecordsList}}</textarea>

</div>

<h1>Total list:{{c.duplrecordsSize}}</h1>

CSS:

.container {

  clear: both;

  text-align:left;

  align-content: left;

}

.container input {

  width: 100px;

  clear: both;

}

.container label {

  width: 150px;

  clear: both;

}

.container textarea {

  color:black;

}

CLIENT:

function($scope) {

  /* widget controller */

  var c = this;

    c.duplrecordsSize=0;

    //c.duplrecordsList=[];

    c.duplrecordsList='';

   

    $scope.runtheoperation = function() {

        //console.log("message", 'run the op');

        c.server.get({

               action: "get_dupls",

             msg : "Running glideaggregate to check for duplicates...",

               sn_table : angular.element($('#tbl_name')).val(),

               duplfield : angular.element($('#dupl_field')).val(),

               customQuery : angular.element($('#custom_query')).val()

           }).then(function(r){

              // alert('SIZE: ' + res.data.duplrecordsSize);

              //alert('test2: ' + r.data.duplrecordsSize);

              c.duplrecordsSize=r.data.duplrecordsSize;

              var duplData=r.data.duplrecordsList;

              c.duplrecordsList=duplData.join('\n');

             

        });

     

  };

}

SERVER:

(function() {

  /* populate the 'data' object */

  /* e.g., data.table = $sp.getValue('table'); */

    data.duplrecordsList=[];

    data.duplrecordsSize=0;

    if (input && input.action === "get_dupls"){

        data.duplrecordsList=(checkDuplUsers(input.customQuery, input.duplfield,input.sn_table));

       

      data.duplrecordsSize=data.duplrecordsList.length;

    }

function checkDuplUsers(customQuery, fieldParam, table){

  var ga= new GlideAggregate(table);

  ga.addAggregate('COUNT', fieldParam);

  if (customQuery){

    ga.addQuery(customQuery);

  }

  ga.addHaving('COUNT', '>', 1);

  ga.query();

    var dupls=[];

    dupls.push("The following " + ga.getRowCount() + " records are duplicate on " + fieldParam);

  while (ga.next()) {

        var DuplLine=ga.getAggregate('COUNT', fieldParam) + " => " + ga.getElement(fieldParam);

     dupls.push(DuplLine);

 }

    //gs.log(dupls,'duplWidget_arr');

    return dupls;

}

})();