Excel Transform Map: How to map source User ID instead of name to target group member User?

[bbf35621]

Hello!

I already have existing ServiceNow groups and users. I have an Excel spreadsheet that I need to update by adding members to groups. The challenge is that I want to map the user ID instead of the name to the target user in the group member table. I believe reference User is the required name to be able to import it. I do have the user name, but the problem is that some have more than 1 same user name or the username could be the unofficial first name that doesn't match the SN user name. That is why user ID is the safest approach. In transform map, how can I map the user ID from my Excel to a user in the group members table? Here is a sample spreadsheet that I want to use for the transform map.

[Joe S1 · Accepted solution]

Hello,

Sure, all you would need to do is to create your field mapping. Set your source field to User ID and make sure to set the Choice action to "ignore" so that new records don't get created by accident. Then set your target field to User and since it's a reference field set the Referenced value field name to "user_name" which is the field for User ID in the table.

Thanks,

Joe S

[bbf35621]

I have tried that and it isn't working. Here are screenshots,

[Joe S1]

I just noticed in your Transform map you're coalescing on the User ID and the Group Name. Is there a reason you're coalescing on both of those values? I ran through this in my PDI and only coalesced on the User ID and it worked as expected.

[Joe S1]

Sorry my bad, you do need to coalesce on both fields in order for the records to be inserted. I updated my PDI and re-ran the test and I now have 11 records inserted as expected. There are essentially no differences between what you have and what I have so not sure why mine is working.