Coalesce on a reference table item

[scott_moen]

I am attempting to import a big list of relationships into cmdb_rel_ci using an Import Set. When I define my Transform Map I create a relationship between a hostname in the import table and the target "child" which is a reference table. I have experimented in the field map settings with setting the referenced value field name (and leaving it blank) and also setting the choice action to "reject" but regardless of the combination of these settings, when I run the transform I end up with a new record inserted.

I have experimented enough with coalesce on table elements so I am pretty comfortable with making it work in that context. Is it possible to coalesce on a reference table item as described or do I need to add a custom script to make this work?

[jason_petty]

if you set the field to the target.field.setDisplayValue() on the target table then it may work.

[scott_moen]

Jason,
Thanks for the reply. The data already exists in the related table (and the new records in ci_rel all have the same sys_id so it is doing the lookup properly). Is that what you were getting at?

My suspicion is that this has to be handled via a script rather than a field map. I might, for example, be able to trick it in an onBefore script and setting ignore = true.

[MKhan1]

Scott,

Coalesce on the parent and child fields, setting the choice action to ignore is enough. Adding the type of relationship should be handled through onBefore script. Did you try this?..

[scott_moen]

Mohammed,
The choice field, per the tooltip, specifically relates to the action taken when the reference item does not exist. In my case, the reference item is already there. I have actually chosen to reject the record in the Choice Action field as the result in our system would be undefined.

I am starting to believe that this is a bug in the SNOW platform since the coalesce setting appears to have no behavior under these circumstances. I can probably work around it using the onBefore script, though.

Thanks for your ideas.