Transform Map with multiple coalesce fields

[DanLevin]

Hi everyone,

I am working on creating roughly 1500 CIs in the "Communications Devices" table, cmdb_ci_comm. I've run into an issue with the Transform, and I'd appreciate any advice you may be able to provide.

My Transform Map looks like this:

I need Service-Now to choose the correct value for the "Location" field (present on cmdb_ci_comm by default) based on a matching value in the "GP Address ID" field, a secondary field on the cmn_location table that we use in conjunction with "Name" field on cmn_location to ensure that Locations are unique. I added the field map for the "GP Address" ID field manually, as the auto-map created the other field maps without issue. Here's what it looks like:

Unfortunately for me, it seems that Service-Now does not allow the field map to another table (which I must coalesce on), as I receive this error in the Transform History:

If I remove the "Location" and "GP Address" ID fields from my Import Set / Transform Map, I'm able to load the remaining data without issue. To continue my bad luck, getting the "Location" field populated with the correct value is arguably the most important part of this exercise.

Is there a workaround for this?

[balaji_charapal]

can you give a try by enabling Coalesce empty fields

[DanLevin]

Thank you for your response, Balaji.

I did not use the "Coalesce Empty Fields" checkbox on any of my field maps, as none of the fields are empty in my import set, or in our actual instance. I just need the transform map to check the value in the "GP Address ID" field on the cmn_location table to ensure that the correct value is being selected for the "Location" field on each CI being created.

For example, I could have two Locations with "Name" = "123 Any Street". One could have "Company" = "Company 1", with "GP Address ID" = "ABC", while the other Location may also have "Name" = "123 Any Street", but with "Company" = "Company 2", and "GP Address ID" = "XYZ". Lastly, it is possible that "Company 3" may have two Locations with "Name" = "123 Any Street", one with "GP Address ID" = "AEI", and the other with "GP Address ID" = "OU".

[venkatiyer1]

Hi Dan,

Rather than coalescing on three fields, i would coalesce on Sys id and create a source script. So create a field map mapping to the sys id of the target and use the source as source script. Also, check coalesce on.

Then within the script

answer = (function transformEntry(source) {

  var addressId = source.u_gp_address_id + "";

  var location = source.u_location + "";

  var name= source.u_name + "";

  .... Place your script get the sys id of the matching record or else return -1 for insertion

})(source);