script to make CI Relationship unique

[pdom]

Hello Friends,

I am trying to load CI Relationship records to cmdb_ci_rel table. I do want to load duplicate records. The Parent, Type, Child combination should be   unique

If any record that is coming from import set which already exist in the target table should be completely ignore

If the parent and child values match but the type value is different then the Type should be update

If there is no matching CI relationship record, the insert a new record

I have created a below On before transform map script. but this does not seem to be working. It is creating duplicate entries. Could you please assist fixing this

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
     
var parent_sysid;
var child_sysid;
var type_sysid;

//fetch source attributes from importset

var sourceparent = source.u_parent.toString();
var sourcechild = source.u_child.toString();
var sourcetype = source.u_type.toString();

//glide query to fetch the sys id of paren and Child Ci's

var ci = new Gliderecord('cmdb_ci');
ci.addQuery('name',sourceparent);
ci.query();
if(next){
  parent_sysid =ci.sys_id;
}
ci.addQuery('name','u_child');
ci.query();
if(next){
  child_sysid=ci.sys_id;
}
       
//glide query to fetch the sys id of CI Relation type

var reltype = new Gliderecord('cmdb_rel_type');
reltype.addQuery('name',sourcetype);
reltype.query();
if(next){
  type_sysid = reltype.sys_id;
}



var cirel = new Gliderecord('cmdb_rel_ci');
cirel.addQuery('target.parent',parent_sysid);
cirel.addQuery('target.child',child_sysid);
cirel.addQuery('target.type',type_sysid);
cirel.query();
if(next){
  ignore = true; //if the CI relationship already exist ignore the record
}
else {
  cirel.addQuery('target.parent',parent_sysid);
  cirel.addQuery('target.child',child_sysid);
  cirel.query();
  if(next){
      cirel.type = target.type;
      cirel.update();     //if the CI relationship exists with a different relationsip type then update the type value
  }
  else{
    ignore = false; // if no Ci relationsip exists then creats the import set record
  }
}

})(source, map, log, target);

[senthilkannandp]

HI Padmini,

For this situation, you can create a business rule for relationship table to restrict the duplication.

function onBefore(current, previous) {

  //This function will be automatically called when this rule is processed.

  var Rcount=0;

  var validRelation = true;

  if (current.operation() =='insert')

  {

  if(getRecordSet(current.parent, current.child,current.type) > 0)

  {

  validRelation =false;

  }

  }

  if(current.operation() =='update')

  {

  if(current.parent.changes() || current.child.changes())

  {

  if(getRecordSet(current.parent, current.child,current.type) >1)

  {

  validRelation =false;

  }

  }

  }

  if(validRelation== false)

  {

  current.setAbortAction(true);

  gs.addInfoMessage('Parent: '+current.parent.name +   ' , child: '+ current.child.name + ' relationship cannot replicate twice');

  }

}

function getRecordSet(p_parent,p_child,p_type)

{

  var objRel = new GlideRecord('cmdb_rel_ci');

  objRel.addQuery('parent',p_parent);

  objRel.addQuery('child',p_child);

  objRel.addQuery('type',p_type);

  objRel.query();

  //gs.log('#Calling Rel Br : '+objRel.getRowCount());

  return objRel.getRowCount();

}

Please check the same and let me know any issues.

Thanks,

Senthil

Hoffensoft — You Dream We Deliver

[pdom · Accepted solution]

Corrected Script

(function transformRow(source, target, map, log, isUpdate) {

      gs.log("Inside function");

  var parent_sysid;

  var child_sysid;

  var type_sysid;

  //fetch source attributes from importset

  var sourceparent = source.u_parent.toString();

  var sourcechild = source.u_child.toString();

  var sourcetype = source.u_type.toString();

  //gs.log("the values inside function " +sourceparent +sourcechild +sourcetype);

  //glide query to fetch the sys id of paren and Child Ci's

  var parentci = new GlideRecord('cmdb_ci');

  parentci.addQuery('name',sourceparent);

  parentci.query();

  if(parentci.next()){

  parent_sysid =parentci.sys_id;

  gs.log("Inside Parent sysid loop");

  }

  var childci = new GlideRecord('cmdb_ci');

  childci.addQuery('name',sourcechild);

  childci.query();

  if(childci.next()){

  gs.log("Inside child sysid loop");

  child_sysid=childci.sys_id;

  }

  //glide query to fetch the sys id of CI Relation type

  var reltype = new GlideRecord('cmdb_rel_type');

  reltype.addQuery('name',sourcetype);

  reltype.query();

  if(reltype.next()){

  gs.log("Inside type sysid loop");

  type_sysid = reltype.sys_id;

  }

  var cirel = new GlideRecord('cmdb_rel_ci');

  cirel.addQuery('parent',parent_sysid);

  cirel.addQuery('child',child_sysid);

  cirel.addQuery('type',type_sysid);

  cirel.query();

  gs.lop( "details are " +parent_sysid, " " +child_sysid, " " +type_sysid);

  if(cirel.next()){

  ignore = true; //if the CI relationship already exist ignore the record

  gs.log("inside 1st if loop");

  }

  else {

  gs.log("inside 1st else loop");

  var _cirel2 = new GlideRecord('cmdb_rel_ci');

  _cirel2.addQuery('parent',parent_sysid);

  _cirel2.addQuery('child',child_sysid);

  _cirel2.query();

  if(_cirel2.next()){

  gs.log("Inside 2nd If loop");

    ignore = true;

  _cirel2.type = type_sysid;

  _cirel2.update();     //if the CI relationship exists with a different relationsip type then update the type value

  }

  else{

  ignore = false; // if no Ci relationsip exists then creats the import set record

  gs.log("Inside Else loop");

  }

  }

})(source, target, map, log, action==="update");