coalesce on two fields in transform map inserting new records

[Abhishek Kumar8]

I need to coalesce on two fields where one field is the Account(reference to customer_account) field and the other is the User(reference to the user(sys_user)) field.

it should check for both Account and the User field then it should update another field "Technology" ( glide list)record, but for me even if I try to load the same data new entry is created instead of ignore,

Also if the same Account and User are present but with different Technology values, it should update the existing record instead of inserting a new record.

field map - choice action - ignore set for these three fields.

Target table : User account relationship

Tried with Onbefore script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
    gs.log("action" + action, "tech1");

    var techname;
    var queryString = 'u_aacount=' + source.u_client + '^u_user.email=' + source.u_email;
    gs.log("techname" + queryString, "tech1");
    var graccrel = new GlideRecord('u_user_account_relationship');
    graccrel.addEncodedQuery(queryString);
    graccrel.query();
    if (graccrel.next()) {
        //if ((action == "update") || (action == "insert")) {
            var technology = graccrel.x_csm_technology;
                gs.log("techname" + techname +"tech" +technology, "tech1");
            var graccrel1 = new GlideRecord('x_csm_technology');
            graccrel1.addQuery('sys_id', technology);
            graccrel1.query();
            if (graccrel1.next()) {
                techname = graccrel1.u_technology;
                gs.log("techname" + techname, "tech1");

            }

            if (techname == source.u_technology) {
                source.sys_import_state_comment("Duplicate entry, Record with the same Account User combination exist in the system");
                //log.error("setabortaction");
                gs.log("technamecompare" + techname, "tech1");
                //source.setAbortAction(true);
                ignore = true;
            }
        //}
    }

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

Any help on this is really appreciated.

Thanks,

Abhishek

[Ankur Bawiskar]

Hi,

choice action should be reject if you don't wish to insert record into target table if coalesce field is not found

Regards
Ankur

[Abhishek Kumar8]

Hi @Ankur Bawiskar , 

 It's inserting a new entry for existing data load for choice action either reject or ignore for both coalesce fields.

if i uncheck onbefore script :

if ((action == "insert")) {
        ignore = true;
    }

Thanks,

Abhishek

[Ankur Bawiskar]

Hi,

try performing fresh load again with choice action reject

Regards
Ankur

[Abhishek Kumar8]

Hi Ankur,

tried with fresh data load again with choice action as reject but it's inserting a new entry in the target table.

Thanks,

Abhishek

[Abhishek Kumar8]

Hi,

Now, when i tried to load the existing record, now it is skipped with the error below. 

Thanks,

Abhishek