Why would coalesce fail SOMETIMES on a number

[jlaps]

i have a data source that triggers from an inbound email from our HR that has upstream manager relationships on it. I am coalescing this Excel file by the EMPLOYEE NUMBER to the user table on SN, and updating a field with the string/name. For some reason, some subset of employees are not getting updated, even though the employee number is there and appears correct. Where might the issue be?

Data source-

Transform-

I also have an onBefore script to prevent this file from creating user records, it should only update-

From what I can see, the users not getting updated look like any of the others with nothing jumping out as a cause. The emp# is present on the user record and import file both. Ideas?

[Bhuvan]

@jlaps 

Check from import set table whether there are any whitespaces in employee number field that makes it to not match with existing records. Compare any sample record that was supposed to update the user record but did not do so.

From what is mapped in transform, you have a employee number and a choice field with ignore creation. If employee number already exist in user record and you are not creating a choice, what field(s) you are actually updating ?

If this helped to answer your query, please mark it helpful & accept the solution. 

Thanks,

Bhuvan

[jlaps]

When it matches the employee number, it copies in the L2-EVP into field on user.

[jlaps]

I figured it out, thank you everyone for the things to try. Found some duplicate accounts where HR created the same emp# on more than one account... which is annoying, but it at least is an understanable result for why. That I wish I would have caught before posting!

Thanks again.

[Ajay_Chavan]

most likely causes for partial employee updates in your ServiceNow data source:

Data Format Issues:
Employee numbers may have leading zeros, spaces, or different formatting between Excel and ServiceNow
Check for hidden characters or encoding issues in the Excel data
Verify employee number field types match (string vs. number)

Transform Map Problems:
Review your transform script logic for conditional statements that might skip certain records
Check if there are field mapping conditions excluding some employees
Look for null/empty value handling in the coalesce logic

Import Set Row States:
Check Import Set table for rows that show "Error," "Ignored," or "Skipped" status
Review error messages in the Import Log for failed records

Security/ACL Restrictions:
Verify the import user has proper write access to all user records
Some employees might be in different departments/groups with restricted access
Check if business rules are blocking updates for certain user types

Duplicate/Multiple Record Issues:
Ensure employee numbers are truly unique in both systems
Check if some employees have multiple user records in ServiceNow
Verify coalesce field configuration

Quick Troubleshooting Steps:
Run a query comparing employee numbers between Import Set and User table
Check transform history for specific failed employee numbers
Test the transform with a small subset of the problematic records
Review any custom business rules on the User table that might interfere