How to Fix a Broken Coalesce Field on a Transform Map

[Rogers Cadenhe1]

I have a data source that builds a table of company sites using a string field called site_code as the coalesce value. It runs once a day.

Every day, the same record was being inserted into the database with the same 32-character string in site_code.

The purpose of the coalesce field in a transform map is to prevent this exact situation. When the site_code matches an existing record's site_code, that record is updated instead of a new record being created. I couldn't understand why coalesce wasn't doing its job.

After a lot of time spent fruitlessly researching and testing, I realized what was happening.

The site_code for that one record from the data source is 38 characters long.

The site_code field in my ServiceNow table is 32 characters long.

The coalesce code in data source was comparing a 38-character string to a 32-character string. They didn't match, so the 38-character string was stored in site_code, which chopped it down to 32 characters.

When I expanded the size of the site_code field in SN to 50 characters, it solved the problem. The record is no longer being inserted each time the data source import is run.

[adamn]

Nice catch, I bet that was frustrating for a while.

Im trying to remember if I have ever dealt with a truncation issue in ServiceNow. 

Im wondering if the import set logs identified the truncation was occuring? If not, thats probably a good enhancement idea for ServiceNow to implement.

[Rogers Cadenhe1]

It was. There was considerable weeping and gnashing of teeth.

I looked in the import set logs and there was no error reported. It would be a good idea since a coalesce field will never work in that situation.

I wonder what the preferred way is to suggest that kind of platform enhancement. A HI ticket?

[Rogers Cadenhe1]

Thanks for the suggestion. 

I've filed an enhancement request on HI portal to add some kind of "coalesce field too big for destination" error to the import set log.