question How to update records in target table using transform map in ITSM forum

How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 04:23:04 GMT

Hi All,

I want to update records , I am using coalesce on name field but i want to update only active records in target table.

how can i achieve that. I am using before transform script ,but it is not updating the records where active is true and name field has a duplicate(one as active=false).

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

if(action=="update" && target.active == true){
action="update";

}

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 04:47:06 GMT

@Priya75

do this

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) { // Add your code here if(action == "insert"){ ignore = true; } if(action == "update" && target.active.toString() == 'false'){ ignore = true; } })(source, map, log, target);

If my response helped please mark it correct and close the thread so that it benefits future readers.

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 05:04:41 GMT

This is ignoring the records where 2 duplicate name is present . it is not even updating the active records.

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 05:03:28 GMT

@Priya75

how can there be 2 names in target if you are marking coalesce on name field?

it should not

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 05:19:41 GMT

There are duplicate records, one is active one is inactive, i want to only update active one.

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 05:22:30 GMT

@Priya75

then it should update only the active record as per the onBefore transform script

Can you share your field maps?

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 06:23:38 GMT

field maps has only 2 fields , one is name is a string field and another field is internal/external(dropdown), so we are updating on the basis of name. i have used it earlier it updated all the records. but some of the active false record as well, so now i m trying to validate it before the insertion.

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 06:15:42 GMT

@Priya75

as per onBefore transform script I shared it should update only the one which is active=true

did you add logs and check what's the value of target.active in onBefore transform script

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 06:53:27 GMT

if(action == "update" && target.active.toString() == 'false'){
		ignore = true;
	}

if i use this then it is not going inside the if condition, if i remove update part and only write target.active is true , then i am getting logs that target.active= false, where as i have 2 records present in the list.

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 06:56:55 GMT

@Priya75

did you check what's the action when you print in logs?

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 07:12:30 GMT

it is showing insert, can we Use source script in the field map of NAME field?

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 07:16:41 GMT

@Priya75

since it's showing insert then the name coming from import set is not an exact match

If it would have been an exact match and since you have marked it as Coalesce it should find and set action as update

please check the name is exact, no leading or trailing space in the import set field

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 07:28:40 GMT

i don't think so there is something like that, i am also getting warning as "More than one target records exists for target table cmdb_ci_business_app with query name=Salesforce "

earlier also i have used the same to bulk uplaod only few records didn't get updated.

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 07:29:47 GMT

@Priya75

if import set field is having exact same name as in target table then it should consider it as Update and not insert.

Definitely some issue with the import set data. please debug it

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 08:04:34 GMT

Okay, now i am getting false and action as update. but still isn't updating active one.

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 08:08:30 GMT

@Priya75

if it's coming inside the IF then that row should be ignored

I hope you have created this script in onBefore transform script

Re: How to update records in target table using transform map

Priya75 — Thu, 08 Jun 2023 08:52:28 GMT

yes, it is a onbefore one. yeah but the active one should be updated right?

it also shows target as empty

Re: How to update records in target table using transform map

Ankur Bawiskar — Thu, 08 Jun 2023 09:18:37 GMT

@Priya75

sorry to say.

It's very difficult to understand without the screenshots.