Importing data into site table in WSD

[harshacool9]

I am importing data into the sn_wsd_core_site table from an Excel file. The source file contains two columns: Region and Site.

During the import, the Site records are being created/updated successfully, and the site names are visible in the sn_wsd_core_site table. However, the Region field is not getting populated.

The Region field is a reference field that points to the sn_wsd_core_region table. In the Transform Map, I have mapped the source Region column to the Region reference field and enabled reference field mapping.

Could anyone advise what additional configuration is required to populate the Region reference field correctly during import? Are there any specific settings for reference fields, coalesce fields, or transform scripts that I should check?

Any guidance would be appreciated. Thank you.

[Tanushree Maiti]

Hi @harshacool9 ,

1. By default, when you map a source string to a reference field, ServiceNow tries to match that string against the  sn_wsd_core_region table's Display Value field.

If your source Excel column contains region data that doesn't match the display column exactly , the mapping fails and leaves the field blank.

Fix: 

• Fix: Write a quick Field map script/ Before Transform Script on your transform map to automatically trim spaces and manually look up the reference record.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    var sourceRegion = source.u_region.toString().trim(); // Replace u_region with your exact source staging column
    
    var regGr = new GlideRecord('sn_wsd_core_region'); // WSD region table
    if (regGr.get('name', sourceRegion)) {
        target.region = regGr.getUniqueValue();
    }
})(source, map, log, target);

2. If the Region name in your Excel sheet does not exist yet in your ServiceNow instance, the Transform Map handles it based on its Choice action rule.

If it is set to Ignore, ServiceNow leaves the field blank instead of creating the new Region.

As Region is foundational data, customer generally not allow to add garbage data instead of Approved one.

Note: Make choice action of the field map's Choice action as Create if you want ServiceNow to automatically create new region records when it encounters unmatched text.