Transform coalesce not working as expected with Choice Action = Create

[Rim1]

folks, I see some strange behavior with Transform coalesce functionality when loading data to cmdb_rel_ci table on my personal developer instance (Kingston). 

I have import file as below:

Parent "A123" is existing CI while none of the Childs exist. For this reason, when configuring a transform map, I set Choice Action = Create in order to Create missing child CIs on transform. My transform map is also configured to coalesce on all three fields.  

With this configuration in place, my expectation would be that transform creates three records in cmdb_rel_ci table along with missing CIs. BUT... It does create all Child CIs, but not relationships. Based on the logs, transform looks at the first row, and inserts it as expected. Then looks at the second row, and for some reason thinks that it coalesce with the first one and UPDATES it (what????). On the last row it behaves the same and UPDATES it again! 

So I end up only with three Child CIs created but only one record (last one) out of three in cmdb_rel_ci:

If I re-run the same transform again, it inserts the missing relationships correctly:

Is this expected behavior? Do I miss anything here?

[Ankur Bawiskar · Accepted solution]

Hi Rim,

What I believe is by the time it creates the child record i.e. AAA1 the second import set row runs and finds the record with empty child and hence updates

What is the final value in the update operation?

are the values 

A123 AA3 etc

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Rim1]

yes, the final value is A123 AA3 (last one). you are right, it does sound like transform treats child as blank, and continues updating it to the final row...

Any ideas how to bypass this with a script or smth?

[Ankur Bawiskar]

Hi Rim,

It's better to handle this type of scenario without the field maps and use onBefore script for this

query child table with second column; get the sys_id if found; if not found then create

query target table with combination of those 2 source fields and above sys id; if found then update; if not found then create new

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Rim1]

got it, onBefore should do the trick, thanks!