How to make a Coalesce field update only non retired records

[Aditya Banka2]

I have source field called as u_client on which I have set coalesce as true with name field on cmdb_ci_server table.

The issue here is that there are duplicate records in cmdb_ci_server table where the transform map is updating the retired record and not updating the record which actually needs to be updated.

how to fix this

[Giles Lewis]

If you have a single coalesce field, then it needs to be a unique key. Suppose you coalesce on name, and you have two records with the same name: one active and one inactive. Then there is no way to control which one will be updated. In order for your update to work correctly, you need to give the inactive record a different name. This is typically done by appending some text to the name of the inactive record. If you have two records with the name "ABC" then you might rename one of them to "ABC INACTIVE".

It is no different if you have multiple coalesce fields. This means that you have a composite key. If the composite key is client + name, then client + name must be unique. If you have two records with the same client and the same name, then there is no way to control which one will be updated. For the retired record, you must either change the name or change the client. Once again, this is typically done by appending some text to either the name or the client of the retired record.

[Ankur Bawiskar]

Hi,

you can use onBefore transform script and check what's the target record's state

Based on that allow the update

something like this

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

	// Add your code here
	if(target.state == 'retired' && action == 'update')
		ignore = true;

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

Regards
Ankur