Hello Community!
We have a transform map that we've had working for a few years without any hiccups. However, here recently we've come across an issue where we have had at least one instance of having users who have the same name and the transform map populates the fields as intended but being that the same name exists we are getting the wrong user mapped. We have an excel file that we are using so the information is correct but I need to come up with a way to "populate/map" the correct user if more than one with the same name exists. in our file we also have their email address so that is how we know we have the wrong user so I have been thinking of how can I reference that email to then update the name field based off that email.
The name field we are populating is a reference to the User table but the email is just a string field.
A little background on what this is used for is for approvers for various applications based on spend levels in the system so having the wrong user getting an approval for something is not what we want.
I am not sure if this could be achieved with a Transform script or maybe a business rule that runs after so I am looking for some guidance on how I can correctly update the name by querying the user table for the email address and then when it finds the unique email it can replace the name with that account that matches the email.
Being that this is a rare occurrence I am looking for the simplest approach as everything up until this has been working without a hitch.
Any ideas?
Hi, when transforming data you need to coalesce on unique values.
Based on your post it sounds like you are coalescing on user name field, when you should be using user email
- user name AND user email should also work.
The coalesce options are set via a tick box in your field mapping records.
example from PDI - Note: you can also set coalesce from field mapping list view.
/now/nav/ui/classic/params/target/sys_transform_entry.do%3Fsys_id%3D87501a42c0a8006f00a93932c3cfa06b
So we are coalescing on some unique values for where these approvers go which is a sort of LOB table so that we aren't creating multiple. These limits are not across the board and who may be a $5000 approver for one LOB may not be the same one for another LOB and same for the various amounts. Could I still coalesce in that type of scenario?
