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");

[Sathish Kumar V]

Hi Woj,

Tried your updated code now, but it is not even inserting the new records into the table. 

Thanks.

[Woj]

Hi Sathish

It seems strange to me, that it doesn't work with the provided code.

Since "relation.addQuery('parent.u_portfolio_id', source.u_eprid);" checks for all records, which have the mentioned id, and "!relation.hasnext()" implies, that it shall only proceed, if the if is not found, which is your requirement, as far as i understood. 

Your initial version " relation.addQuery('parent.u_portfolio_id', '!=', source.u_eprid); " says "give me all, that don't match this id", which will always return something.

I would recommend to create the transform map on cmdb_rel_ci and then do the check of the parent.u_portfolio_id in an onBefore script and set ignore=true, if the value already exists. 

[Sathish Kumar V · Accepted solution]

Thanks for your replies. By the way the issue got solved by making field map with script and by providing the reference value field name in the Field map form. And the above code is not required when i'm using field map in my requirement. 

Thanks.

[Ankur Bawiskar]

Glad to know.

Please mark appropriate response as correct & helpful so that this thread can be closed and others can be benefited by this.

Regards
Ankur