I am performing easy import on custom table , issues with reference field

[Mishu]

I am performing easy import on the custom table, but the string field from Excel' u_owner' is not getting mapped to the reference field 'Owner' on the target table. It is mapping the other fields but the owner field just does not get copied and leaves blank.

Need Urgent help on this. 

[Mishu]

@Ankur Bawiskar - I observed that when it's an active user the value is getting mapped in target table, but in case of locked out/inactive user it is coming blank after import, but customer want the inactive user also to be displayed on data import...i have not used any reference qualifier or any validation to remove inactive users to be selected or displayed, What could be the issue it is not showing up ?

In below screenshot 'Abrahim' was an active user so it shows up but other record the value is empty as i tried with inactive users.

Please suggest

[Tai Vu · Accepted solution]

Hi  @Mishu 

Most probably because of this rule.
Name: user query

URL: https://<instance_name>/sys_script.do?sys_id=62a7bfaf0a0a0a6500c49682bd82376a

[Mishu]

@Tai Vu -- Checking

[Mishu]

It worked now !! Thanks much 🙂

[Amit Gujarathi]

HI @Mishu ,
I trust you are doing great.

When you're importing data into a reference field in ServiceNow, the system doesn't look for the display value (like a string from your Excel). Instead, it expects the sys_id of the referenced record. If you're trying to import a string value into a reference field, it won't work directly.

Here's how you can address this:

(function transformEntry(source, target, map, log, isUpdate) {
    var ownerName = source.u_owner.toString();
    var ownerSysId = '';

    // Query the user table to get the sys_id based on the user's name or any other unique identifier
    var userGR = new GlideRecord('sys_user');
    userGR.addQuery('name', ownerName); // Adjust the field name as per your instance
    userGR.query();

    if (userGR.next()) {
        ownerSysId = userGR.sys_id.toString();
    }

    // Set the sys_id to the reference field
    target.owner = ownerSysId;
})();