question coalesce on two fields in transform map inserting new records in Developer forum

coalesce on two fields in transform map inserting new records

Abhishek Kumar8 — Thu, 22 Sep 2022 14:48:51 GMT

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

Re: coalesce on two fields in transform map inserting new records

Ankur Bawiskar — Thu, 22 Sep 2022 15:11:27 GMT

Hi,

since both the target fields on which coalesce is done are reference type; did you specify the Referenced value field name in the field map?

share the field map screenshots.

Regards
Ankur

Re: coalesce on two fields in transform map inserting new records

Abhishek Kumar8 — Fri, 23 Sep 2022 01:56:37 GMT

Hi @Ankur Bawiskar ,

Yes, I've specified the reference value field name in the field map for both fields.

Still, it's inserting a new record every time for data load.

Thanks,

Abhishek

Re: coalesce on two fields in transform map inserting new records

Ankur Bawiskar — Fri, 23 Sep 2022 03:28:51 GMT

Hi,

if either value is not found it will insert.

If both the values are found then it will update

Did you check the incoming data?

Regards
Ankur

Re: coalesce on two fields in transform map inserting new records

Abhishek Kumar8 — Fri, 23 Sep 2022 03:58:30 GMT

Hi @Ankur Bawiskar , yes, the new record is inserted in the import set table and target table as well. it seems choice action is not working.

also before script is not working, if needed.

Re: coalesce on two fields in transform map inserting new records

Abhishek Kumar8 — Fri, 23 Sep 2022 04:25:44 GMT

Hi,

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

Thanks,

Abhishek

Re: coalesce on two fields in transform map inserting new records

Ankur Bawiskar — Fri, 23 Sep 2022 04:31:59 GMT

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

Re: coalesce on two fields in transform map inserting new records

Abhishek Kumar8 — Fri, 23 Sep 2022 05:11:09 GMT

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

Re: coalesce on two fields in transform map inserting new records

Ankur Bawiskar — Fri, 23 Sep 2022 05:39:29 GMT

Hi,

try performing fresh load again with choice action reject

Regards
Ankur

Re: coalesce on two fields in transform map inserting new records

Abhishek Kumar8 — Fri, 23 Sep 2022 05:56:11 GMT

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