Restrict duplicate entries

[shaik17]

Hi all,

i have a requirement when a record is updated in sc_item_variables_task table (MRVS) it is creating duplicates here is the script they used after insert business rule

(function executeRule(current, previous /*null when async*/) {

 

var qList = [];

//get variable sets related to item
var vsr = new GlideRecord('io_set_item');
vsr.addQuery('sc_cat_item', current.request_item.cat_item.sys_id);
vsr.query();
//get variables for each set
while(vsr.next()){
var vs = new GlideRecord('item_option_new');
vs.addQuery('variable_set', vsr.variable_set);
vs.addQuery('active', true);
vs.addQuery('global', false);
vs.query();
while(vs.next()){
var id = vs.sys_id.toString();
qList.push(id);
}
}

//push Vatiable Task record for each variable set
for (var i = 0; i < qList.length; i++) {
var ivt = new GlideRecord('sc_item_variables_task');
ivt.task = current.sys_id;
ivt.variable = qList[i];
ivt.insert();
}
}

})(current, previous);

Pls help,

Thanks.

[Aravind2799]

Here's a breakdown of the script and potential causes for duplicate records:

1. Querying Variable Sets:

• The script correctly queries for variable sets related to the catalog item using io_set_item.

2. Retrieving Variables:

• It fetches variables for each variable set using item_option_new.
• It filters for active and non-global variables.

3. Creating sc_item_variables_task Records:

• It iterates through the list of variable sys_ids (qList).
• For each variable, it creates a new sc_item_variables_task record with:
  • task: The current task sys_id.
  • variable: The variable sys_id from qList.
  • It inserts the record.

Potential Causes for Duplicates:

1. Multiple Executions:

   • Check if the business rule is configured to run "after update" as well. If so, it would execute on both insert and update events, creating duplicates.
   • Ensure it only runs "after insert" or find a way to prevent multiple executions.

2. Lack of Unique Constraint:

   • If there's no unique constraint on the combination of task and variable fields in sc_item_variables_task, duplicates could exist.
   • Consider adding a unique constraint to prevent this.

3. Existing Record Check:

   • The script doesn't check if a record already exists with the same task and variable combination before inserting.
   • Add a check to only insert if a matching record doesn't exist.

(function executeRule(current, previous) {
  var qList = [];

  // ... (variable set retrieval logic remains the same) ...

  // Check for existing records and insert only if not found
  for (var i = 0; i < qList.length; i++) {
    var ivt = new GlideRecord('sc_item_variables_task');
    ivt.addQuery('task', current.sys_id);
    ivt.addQuery('variable', qList[i]);
    ivt.query();
    if (!ivt.next()) {
      ivt.insert(); // Insert only if not found
    }
  }
})(current, previous);

Additional Considerations:

• Review the business rule configuration to ensure it runs only on the intended events.
• Consider adding a unique constraint to sc_item_variables_task for the task and variable fields.
• If duplicates still occur, thoroughly debug the script's execution flow and data context for further analysis.

Thanks

Aravind Panchanathan

[Amit Pandey · Accepted solution]

Hi @shaik17 

Can you try the following BR-

(function executeRule(current, previous /*null when async*/) {

    var qList = [];

    // Get variable sets related to the item
    var vsr = new GlideRecord('io_set_item');
    vsr.addQuery('sc_cat_item', current.request_item.cat_item.sys_id);
    vsr.query();

    // Get variables for each set
    while(vsr.next()) {
        var vs = new GlideRecord('item_option_new');
        vs.addQuery('variable_set', vsr.variable_set);
        vs.addQuery('active', true);
        vs.addQuery('global', false);
        vs.query();
        while(vs.next()) {
            var id = vs.sys_id.toString();
            qList.push(id);
        }
    }

    // Check if a record with the same task and variable already exists
    for (var i = 0; i < qList.length; i++) {
        var variableId = qList[i];
        var existingRecord = new GlideRecord('sc_item_variables_task');
        existingRecord.addQuery('task', current.sys_id);
        existingRecord.addQuery('variable', variableId);
        existingRecord.query();
        if (!existingRecord.next()) { // If no existing record found, insert a new one
            var ivt = new GlideRecord('sc_item_variables_task');
            ivt.task = current.sys_id;
            ivt.variable = variableId;
            ivt.insert();
        }
    }
})(current, previous);