Should import set transform map update all Coalesce target records?

Les1 · ‎01-26-2022

So i did searching around docs & community and couldnt resolve this question: is it expected that during a transform map, all target records that the coalesce field matches will be updated?

example: doing an import test against hardware assets (alm_hardware) and just have a couple of target records i would expect to be updated based on a coalesce field on Serial number. However, the result is that one record gets updated and the other seems ignored. In my very simple test case, the field i'm expecting updated is "asset tag."

So i wondered if the transform map only updates the first record it finds? I will also say that the record that is not being updated was created by our MIDSERVER, i believe from sccm plugin activity (so maybe theres a blockage there?) and if so where do i go next in isolating the cause. mainly just trying to understand for sure the expectation.

Allen Andreas · ‎01-28-2022

Hi,

When the one row is transformed, it looks for a coalesce field. It'll find the first one that's a match in your target table (since you have 2 or more, it's just going to grab one, could even be random of which one it does, but it queries for just 1 and goes with it).

It's only 1 transform process per 1 row (unless you have multiple transform maps for the same staging table, but that's a different conversation). The system does not take 1 row and then query for all matching serial numbers (for example) on the target table and then run over and over again per 'n' amount. It's just a 1 time run for that 1 row.

Please mark reply as Helpful/Correct, if applicable. Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

View solution in original post

Community Alums · ‎01-26-2022

Yes, the coallesce is the , kind of, unique indentifier (or multiple coalesce fields) but once indentified, the system will update the fields which are not with the same values.

If you want to skip this behaviour you can try to skip those

Les1 · ‎01-26-2022

so i should expect that all records which match on the Coalesced field SHOULD be updated right?

what I am observing is that only one matching record gets updated and other is not updated so i am confused by the behavior.

Community Alums · ‎01-26-2022

Hmm, how so ? You mean that :

existing record A [ field A == 'AAA', field B == 'BBB' ]

existing record B [ field A == 'BAA' field B == 'BBB' ]

incoming data updating both records :

new data is : for field A -> '111' field B -> '222' ===> updated

for field B -> '111' field B -> '222' ===> skipped

so after all the records are :

[ field A == '111', field B == '222' ]

[ field A == 'BAA' field B == 'BBB' ]

Is that it?

If so, can you post a screenshot of your mapping ?

Les1 · ‎01-26-2022

i'm coelescing on Serial number for Asset records, in the transform map it is the only coalesced field.

I've got two existing asset records that have the same Serial Number (i know that this is not healthy data, i'm actually dealing with duplicate record cleanup but anyway...)

my expectation is that since the duplicate records have the same serial number then the transform would simply update both records with the same updates. but i'm not seeing that happens. Instead, one of the records seems to get ignored entirely (ie it either isnt being seen as a "match" for Serial number or it is being Ignored for updates).

ultimately i'll be removing the duplicates but i'm trying to understand the Transform map functionality as to why it isnt performing an update on both records which have the same coalese field and should register as a match.