Transform Map Questions

jmiskey · ‎09-20-2016

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

raprohaska · ‎09-20-2016

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.

View solution in original post

randrews · ‎03-15-2017

I would <and have> add a notes field to the user record...

then i would create a business rule that runs when active changes to false and wipe the employee ID out adding to the note field employee id changed from #### to null because user is inactive.

then i would run a back ground script to clear the employee numbers of all inactive employees... this would make your employee number a unique key... HOWEVER if you reactivate those people and use the employee number as the key to reactivate you will have issues.....

just something to consider.