Skip to main content

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;
}
})();

Comments

Popular posts from this blog

Get URL Parameter - server side script (portal or classic UI)

Classic UI : var sURL_editparam = gs . action . getGlideURI (). getMap (). get ( ' sysparm_aparameter ' ); if ( sURL_editparam == 'true' ) { gs . addInfoMessage ( 'parameter passed ); } Portal : var sURL_editparam = $sp . getParameter ( " sysparm_aparameter " ); if ( sURL_editparam == 'true' ) { gs . addInfoMessage ( 'parameter passed ); }

ServiceNow - script include: build up the results as a JSON object (array) - example 2

function call var sCurrentGroupMembers = new <script_include> (). getGroupMembers ( group_sysid ); gs . print ( 'sCurrentGroupMembers:' + sCurrentGroupMembers ); var oGroupMembers = JSON . parse ( sCurrentGroupMembers ); gs . print ( JSON . stringify ( oGroupMembers . users )); oNewGroupMember = JSON . parse ( group_members ); var oGroupConfig = getGroupDeltas ( oGroupMembers . users , oNewGroupMember ); gs . print ( JSON . stringify ( oGroupConfig . remove )); gs . print ( JSON . stringify ( oGroupConfig . add )) output : *** Script: sCurrentGroupMembers: {"manager":"3b2649efdb0f8c10cc0652f3f39xxxxx", "email":"", "type":"33a2226edb99c340edfc7cbdae96xxxx", "description":"Created for xyz", "users":[{"user":"3b2649efdb0f8c10cc0652f3f396xxxx"},{"user":"856a8f71db3d73041b4ffc45ae96196a"},{"user":"3752771