Use correct User Name for correct User ID Transform Map Data Uploads (for User-multiple User Names)

[WazzaJC]

Use correct User Name for correct User ID Transform Map Data Uploads (for User-multiple User Names)

Dear ServiceNow Community colleagues, I would greatly appreciate your help with a solution to this issue I am facing.

I have a custom table (which will be used to capture access/roles assigned to users) which has both 'User ID' (u_user_name) and 'User Name' (u_name) on it - I have mapped these to the sys_user table, so that these are reference fields.

I am now trying to upload User Access data to this table, via an Excel sheet, using Load Data and Transform Map.

I have replicated this in my PDI to show the issue I am having - using Abel Tuter as an example.

There are 3 'Abel Tuter' Names, although their User ID's are unique. The Name still looks as Abel Tuter, but the User ID's are of course unique.

But when I am trying to upload the Access relevant to Abel Tuter (into my custom table, via standard transform map), it is not pulling the 'User Name' specifically relevant to the User ID, it is pulling into the table one of the other User Names, for one of the other 'Abel Tuter' record names.

Do I have to use a specific Transform Script or coalesce function or a combination of both?

Basically, when the Excel uploads into the custom table, I want to ensure always the relevant User Name is updated for the User ID, not a different User Name. I want one User ID, associated with it's own relevant User Name.

The challenge we have in the client instance, is there are multiple User Names for each user.

I only want to map to one version of the User ID and map to the User Name that is relevant exactly to that User ID.

I have attached screenshots to show what is occuring for me.

I cannot work out what Transform script I need to use or what coalesce function or perhaps a combination of both?

Many thanks for any help/guidance on this, always appreciated.

[Nick Parsons · Accepted solution]

Seems a bit strange to me that you have both User Name and User ID set up as reference fields? These would also store the same user reference wouldn't they? And the display on the form (the import set table form record) would always show the same value in those fields (ie: the User Name field from sys_user). I would expect these two fields to be regular string fields, or just have one User reference field.

If the User IDs are unique, then what is the purpose of having the User Name supplied in the source, seems like you need to ignore that anyway just populate one reference field based on User ID? Or am I missing something?

[WazzaJC]

@Nick Parsons  thanks very much. You are absolutely correct, I revisited my configuration, in line with your suggestions above and this has indeed resolved the issues I was having. Thanks very much for pointing this out, greatly appreciated Nick. 🙂