How to avoid Duplicate Records getting inserted from Transform Map Script using Scheduled Imports.

[Sathish Kumar V]

Hi,

I have a requirement to insert/ update ci from the excel file into the cmdb relationship table [cmdb_rel_ci].

The excel file contains Application name [u_application_name] and EPRID [u_eprid].

Before inserting i have to check whether the field called 'u_portfolio_id' in the cmdb Business application table [cmdb_ci_business_app] is having same id present in the source excel file field name called 'u_eprid'.

If it matches then i have to insert/ update the ci present in the excel file to the  cmdb relationship table [cmdb_rel_ci].

Ci's from the excel file are Inserting properly, but when schedule import runs next time, duplicate records are creating.

So how to avoid duplicate records in below script?

Note: i have tried this code in Transform script, OnBefore Transform Event Script , nothing works.

          Coalesce not used because i'm checking with other table initially. (differ from target table)

Suggest me with the correct script.

Thanks.

(function transformRow(source, target, map, log, isUpdate) { //This code is in Transform Script
     var businessApp = new GlideRecord('cmdb_ci_business_app');
     businessApp.addQuery('u_portfolio_id', source.u_eprid);
     businessApp.query();
     if (businessApp.next()) {

        var relation = new GlideRecord('cmdb_rel_ci');
        relation.addQuery('parent.u_portfolio_id', '!=', source.u_eprid); // if record not present then trying to insert
        relation.query();
        if (relation.next()) {
              var relationship = new GlideRecord('cmdb_rel_ci');
              relationship.initialize();
              relationship.parent = businessApp.sys_id;  // parent is the application name which is present in the excel file
              relationship.child = 'c3d7793edb781050ffe38d374896194d';  // making particular Information object as a child
              relationship.type = 'cb5592603751200032ff8c00dfbe5d17';
              relationship.insert(); 
           }
     }

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

[Ankur Bawiskar]

Hi,

update as this

(function transformRow(source, target, map, log, isUpdate) { //This code is in Transform Script
     var businessApp = new GlideRecord('cmdb_ci_business_app');
     businessApp.addQuery('u_portfolio_id', source.u_eprid);
     businessApp.query();
     if (businessApp.next()) {

        var relation = new GlideRecord('cmdb_rel_ci');
        relation.addQuery('parent.u_portfolio_id', '!=', source.u_eprid); // if record not present then trying to insert
        relation.query();
        if (relation.next()) {
              var relationship = new GlideRecord('cmdb_rel_ci');
              relationship.initialize();
              relationship.parent = businessApp.sys_id;  // parent is the application name which is present in the excel file
              relationship.child = 'c3d7793edb781050ffe38d374896194d';  // making particular Information object as a child
              relationship.type = 'cb5592603751200032ff8c00dfbe5d17';
              relationship.insert(); 

             ignore = true;
           }
     }

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

Regards
Ankur

[Sathish Kumar V]

Hi Ankur Bawiskar,

Thanks for your suggestion, I have tried this already, but its still inserting the duplicates.

[Ankur Bawiskar]

@Sathish Kumar V A 

I assume your target table is cmdb_rel_ci

try this

1) in the query search for the value;

2) if value is not found then do insert

Also check any before insert Business rule is having current.insert() which is creating duplicate

(function transformRow(source, target, map, log, isUpdate) { //This code is in Transform Script
     var businessApp = new GlideRecord('cmdb_ci_business_app');
     businessApp.addQuery('u_portfolio_id', source.u_eprid);
     businessApp.query();
     if (businessApp.next()) {

        var relation = new GlideRecord('cmdb_rel_ci');
        relation.addQuery('parent.u_portfolio_id', source.u_eprid); // if record not present then trying to insert
        relation.query();
        if (!relation.next()) {
              var relationship = new GlideRecord('cmdb_rel_ci');
              relationship.initialize();
              relationship.parent = businessApp.sys_id;  // parent is the application name which is present in the excel file
              relationship.child = 'c3d7793edb781050ffe38d374896194d';  // making particular Information object as a child
              relationship.type = 'cb5592603751200032ff8c00dfbe5d17';
              relationship.insert(); 

             ignore = true;
           }

          else{

          // record found so if you want to update the record do here

         ignore = true;

          }
     }

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

Regards
Ankur

[Woj]

Hi Sathish

I think you made a small logic error. 
Try something like this:

var relation = new GlideRecord('cmdb_rel_ci');
relation.addQuery('parent.u_portfolio_id', source.u_eprid);
relation.query();

if(!relation.hasNext()) {
//insert
}