Avoid duplicate records in a table using script include

Pratiksha Lang1
Kilo Sage

‎04-19-2023 05:39 AM

I have written below script include to get the checked records on related list and insert those checked records to the related list table itself and I have called this script include in UI action - ADD. while the records are checked and user clicks on add ui action, I want that the duplicate records should not be inserted.

 

script include :

 

addSelectedRec: function() {
var checkedRecords = this.getParameter('sysparm_checked_records');
gs.info('check loop 1 smdrs : ' +checkedRecords);
var olaTab = new GlideRecord('x_amspi_smdrs_app_olas');
olaTab.addQuery('sys_idIN' + checkedRecords);
olaTab.query();
while (olaTab.next()) {
gs.info('check loop 2 smdrs');
var importConfigSite = new GlideRecord('x_amspi_smdrs_app_import_configuration');
importConfigSite.addQuery('source_site', olaTab.site);
importConfigSite.query();
while (importConfigSite.next()) {
gs.info('check loop 3 smdrs');
var targetTable = new GlideRecord("x_amspi_smdrs_app_olas");
targetTable.initialize();
targetTable.ola_name = olaTab.ola_name;
targetTable.frequency_ola = olaTab.frequency_ola;
targetTable.function_ola = olaTab.function_ola;
targetTable.sub_function_ola = olaTab.sub_function_ola;
targetTable.formula_ola = olaTab.formula_ola;
targetTable.ola_type = olaTab.ola_type;
targetTable.status = olaTab.status;
targetTable.role = olaTab.role;
targetTable.artefacts = olaTab.artefacts;
// targetTable.site = olaTab.site;
targetTable.site = importConfigSite.destination_site;
targetTable.insert();
}
}
}, 

 

 

UI Action Script:

 

if (g_list.getChecked().toString().length > 0) {
var ga = new GlideAjax('SmdrsImportConfig');
ga.addParam('sysparm_name', 'addSelectedRec');
ga.addParam('sysparm_checked_records', g_list.getChecked());
ga.getXML();

}

 

 

0 Helpfuls
  • 813 Views
4 REPLIES 4

DUGGI
Giga Guru

‎04-19-2023 05:59 AM

@Pratiksha Lang1 

 

To prevent duplicate records from being inserted when using the script include and UI action that you provided, you can modify the script include to check if a record with the same values already exists in the related list table before inserting a new record.

Here's an updated version of your script include:

javascriptCopy code
addSelectedRec: function() {
  var checkedRecords = this.getParameter('sysparm_checked_records');
  gs.info('check loop 1 smdrs : ' +checkedRecords);
  var olaTab = new GlideRecord('x_amspi_smdrs_app_olas');
  olaTab.addQuery('sys_idIN' + checkedRecords);
  olaTab.query();
  while (olaTab.next()) {
    gs.info('check loop 2 smdrs');
    var importConfigSite = new GlideRecord('x_amspi_smdrs_app_import_configuration');
    importConfigSite.addQuery('source_site', olaTab.site);
    importConfigSite.query();
    while (importConfigSite.next()) {
      gs.info('check loop 3 smdrs');
      var targetTable = new GlideRecord('x_amspi_smdrs_app_olas');
      targetTable.addQuery('ola_name', olaTab.ola_name);
      targetTable.addQuery('frequency_ola', olaTab.frequency_ola);
      targetTable.addQuery('function_ola', olaTab.function_ola);
      targetTable.addQuery('sub_function_ola', olaTab.sub_function_ola);
      targetTable.addQuery('formula_ola', olaTab.formula_ola);
      targetTable.addQuery('ola_type', olaTab.ola_type);
      targetTable.addQuery('status', olaTab.status);
      targetTable.addQuery('role', olaTab.role);
      targetTable.addQuery('artefacts', olaTab.artefacts);
      targetTable.addQuery('site', importConfigSite.destination_site);
      targetTable.query();
      if (targetTable.next()) {
        gs.info('Record already exists in related list');
      } else {
        gs.info('Inserting new record in related list');
        targetTable.initialize();
        targetTable.ola_name = olaTab.ola_name;
        targetTable.frequency_ola = olaTab.frequency_ola;
        targetTable.function_ola = olaTab.function_ola;
        targetTable.sub_function_ola = olaTab.sub_function_ola;
        targetTable.formula_ola = olaTab.formula_ola;
        targetTable.ola_type = olaTab.ola_type;
        targetTable.status = olaTab.status;
        targetTable.role = olaTab.role;
        targetTable.artefacts = olaTab.artefacts;
        targetTable.site = importConfigSite.destination_site;
        targetTable.insert();
      }
    }
  }
}

This updated script checks if a record with the same values already exists in the related list table before inserting a new record. If a matching record is found, the script logs a message and skips the insert. If no matching record is found, the script initializes a new GlideRecord object, sets the field values, and inserts the record.

By performing this check, you can prevent duplicate records from being inserted into the related list table.

Pratiksha Lang1
Kilo Sage
In response to DUGGI

‎04-19-2023 06:26 AM

@DUGGI It is not going in else condition if it is a new record and not existing one. 

0 Helpfuls

Ratnakar7
Mega Sage
Mega Sage

‎04-20-2023 03:16 AM

Hi @Pratiksha Lang1 ,

 

You can modify the addSelectedRec function in your script include to first check if a record with the same values already exists in the target table before inserting a new one.

Here is an updated version of your function with this check:

 

addSelectedRec: function() {
  var checkedRecords = this.getParameter('sysparm_checked_records');
  gs.info('check loop 1 smdrs : ' +checkedRecords);
  
  var olaTab = new GlideRecord('x_amspi_smdrs_app_olas');
  olaTab.addQuery('sys_idIN' + checkedRecords);
  olaTab.query();
  
  while (olaTab.next()) {
    gs.info('check loop 2 smdrs');
    
    var importConfigSite = new GlideRecord('x_amspi_smdrs_app_import_configuration');
    importConfigSite.addQuery('source_site', olaTab.site);
    importConfigSite.query();
    
    while (importConfigSite.next()) {
      gs.info('check loop 3 smdrs');
      
      var targetTable = new GlideRecord("x_amspi_smdrs_app_olas");
      targetTable.addQuery('ola_name', olaTab.ola_name);
      targetTable.addQuery('frequency_ola', olaTab.frequency_ola);
      targetTable.addQuery('function_ola', olaTab.function_ola);
      targetTable.addQuery('sub_function_ola', olaTab.sub_function_ola);
      targetTable.addQuery('formula_ola', olaTab.formula_ola);
      targetTable.addQuery('ola_type', olaTab.ola_type);
      targetTable.addQuery('status', olaTab.status);
      targetTable.addQuery('role', olaTab.role);
      targetTable.addQuery('artefacts', olaTab.artefacts);
      targetTable.addQuery('site', importConfigSite.destination_site);
      targetTable.query();
      
      if (!targetTable.next()) {
        // Record with same values doesn't exist, insert new one
        var targetRecord = new GlideRecord("x_amspi_smdrs_app_olas");
        targetRecord.initialize();
        targetRecord.ola_name = olaTab.ola_name;
        targetRecord.frequency_ola = olaTab.frequency_ola;
        targetRecord.function_ola = olaTab.function_ola;
        targetRecord.sub_function_ola = olaTab.sub_function_ola;
        targetRecord.formula_ola = olaTab.formula_ola;
        targetRecord.ola_type = olaTab.ola_type;
        targetRecord.status = olaTab.status;
        targetRecord.role = olaTab.role;
        targetRecord.artefacts = olaTab.artefacts;
        targetRecord.site = importConfigSite.destination_site;
        targetRecord.insert();
      }
    }
  }
}

 

 

Thanks,

Ratnakar

Pratiksha Lang1
Kilo Sage
In response to Ratnakar7

‎04-20-2023 06:11 AM

@DUGGI  @Ratnakar7 In the same script how can I insert only current record. I mean I want this entire script to run only on current record 

0 Helpfuls