Transform Map Questions

[jmiskey]

I am trying to set-up a transform map to do the following.   I have a file of Employee Numbers.   Based on that file, I would like to update a custom True/False field we added to the User (sys_User) table.   The transform map coalesces on the Employee Number field.

Question 1:

Rather than having to modify the import file every time and add a column with the value of "True" set to it, can I simply hard-code in a value of "True" for this field for everyone being imported on the file?

Question 2:

Sometimes in ServiceNow, there are multiple records in the sys_user table for a particular Employee Number.   When this happens, no more than one record for each Employee Number will be active, and the rest will be inactive.   For these people, if we import a record for them, it seems to only update the first record for that person it finds (regardless of whether or not that record is active or not).

So, is there a way to have the Transform map updates ALL matching records for each Employee Number we are importing?

If not, is there a way we can tell it to only update the Active records in ServiceNow (and not the Inactive ones)?

Thanks

[jmiskey]

OK, I have figured out the answer to question, using the Script option.   Just using a simple script of:

return "true"

seems to do the trick (and specifying the appropriate Target field).

But I still have issues in question #2, determining how to update ALL matching records, or limiting it to just the Active record.

[raprohaska · Accepted solution]

I agree on point 1, return true.

The purpose of coalesces is to define a unique key. And since you can add more than one coalesce, it is possible to have a composite key of multiple fields. If employee id is not unique, then it is not a primary key on its own. If it should be unique, why is there multiple records? If you only have one active record at a time, then you could do a composite key of Active and Employee ID, but if there are multiple active records, once again you'll be stuck.


Not advisable, but technically you could do a custom script in the field mapping to update the other records, but that would totally defeat the purpose of transform maps.

My best advice is to define a proper unique key.

[jmiskey]

Thanks for the reply.

The issue we have is that we are dealing with imperfect data.   Users are imported from a few different sources, and they don't always agree and are not always perfect.   We are in the process of trying to get those data files cleaned up at the source, but those sort of things never seem to happen as fast as you would like them to.   So in the meantime, we are dealt dealing with some messy data.

So, essentially, the files that are coming in to us use the Employee Number as the identifier.   Unfortunately, that field is not unique in our Users table.   In theory, every Employee Number should only have, at most, one active record.   So I have added the Active field to coalesce, so it will only update Active records.   Unfortunately, we seem to get the occasional bad data where there are two active records with the same Employee Number (it usually results when records are imported from another source that does not always have Employee Number, and someone updates it manually after the fact instead of merging the two records).   So what we have done is write a Background Script that we run every morning to check for these bad duplicate records so we can clean them up before we run this import.

So this workaround should address the issues I raised in the original question.

Thanks

[raprohaska]

Bad data is always a pain and to support code that supports bad data is an even bigger pain.

My opinion probably is not advisable or popular but I would build a report and put it in the hands of who ever maintains the data and force them to clean it on a regular basis and allow the process to work as designed and not introduce a lot of hacks trying to find a way around the data issue. I have found that when someone has to continually clean up bad data, they will find a way to fix the process producing the bad data.

But I understand this is not the way of the world so best I can offer is to create a post transform script, or scheduled job, to sync up the data between the process Import Set records and ALL the matching employee numbers. I don't know of a way to force the transform itself to affect more than one record.

If you come up with an elegant solution, I would be curious to hear it.

Good luck,