Need to check coalesce on multiple fields separately in a transform map.

[mkhan123]

We import CIs into ServiceNow. In a transform map, I have coalesce on 4 fields - name, asset_tag, serial_number and company.

As per ServiceNow documentation, if a matching value is found for one of the coalescing fields but not on the other, a new record is inserted.

However, in my case, I want name, asset_tag and serial_number fields to be unique. The only case these 3 fields can be same is if they belong to different companies. For example, if it finds a matching name and asset_tag but a non-matching serial_number for a particular company, I don't want it to insert a new record. Instead, I want to ignore such rows.

[Chuck Tomasi]

The coalesce fields are what they are. All fields that are marked "true" must match to do an update of an existing record. There's no conditional capabilities that I'm aware of (and I've been proven wrong many times).

What I recommend is to do something like section 2.2.2, and write your own field matching.

http://wiki.servicenow.com/index.php?title=Transform_Map_Scripts#gsc.tab=0

[kuttti]

@Chuck Tomasi the below link doesn't work anymore can you please help with latest one ?

Thanks!

[robpickering]

Asad,

Agree with Chuck.   If you can provide a link to the documentation where you read that only ONE of the fields must match, we can look at it and get it corrected.   My personal experience is that ALL of them must match to perform an update, otherwise you've failed the "AND" conditions on the coalesce fields and a new record will be inserted.

Since you've got some unique logic on whether or not to Ignore, Insert, or Update, Chuck's solution should work for you.   You'd therefore reduce yourself to a single coalesce field (say asset_tag) and then write a short script to perform the other matches, if they don't match your logic, you can just set them to ignore and the rows on the import will be ignored.

-Rob

[mkhan123]

Hi Rob,

You're right that all of the coalesce fields must match to perform an update. However, I was talking about how it inserts when we have multiple coalesce fields in a map.

if a matching value is found for one of the coalescing fields but not on the other coalesce fields, a new record is inserted and I think that's the expected behavior and there's no need to correct any documentation.

My requirement is that it should check 3 of the coalesce fields before inserting, if it don't find any of the 3 matching fields, then it should insert the record.