Field Normalization for a Reference field - Step by Step Configuration

[Narsing1]

Note: Before you begin, Install Field Normalization Plugin. Also, please make sure that you are not logging each update using the field normalization as it will have a huge impact on retrieving the audit records.  So do like this after you install the plugin

Go to the Field Normalization ==> Administration ==> Preferences

• Uncheck the "Enable Field Normalization Auditing"
• Logging Option only to the "Error" 

Introduction

The OOTB Field Normalization plugin only works for String/Integer type field transformations. This article provides you an overview/configure when you would like to fill a Reference field.

For example, User table has a Department and from the LDAP Import you are getting the department head email address instead of Department Name. Now, lets see how you can configure this scenario in Field normalization. However, you can simply write a Business rule / a transform map script, but you can also do with Field Normalization. Here is how it can be done.

Step#1

Go to Field Normalization ==> Administration ==> Normalization Field Types

Create an entry like this

Step#2

Go to Field Normalization ==> Administration ==> Transform Categories

Here you will observe entries with Text & Numeric.  Create a new Category called User Department and add the above Field type under the related list like this

Step#3

Go to Field Normalization ==> Administration ==> Transform Definition

Create an entry like this

function(variables, value, parameters) {
    // return the transformed value...
    var val = value + "";
    var gr = new GlideRecord("cmn_department");
    gr.addQuery("sys_id", val); // First assume that you are getting the sysid from import
    gr.query();
    if (gr.hasNext()) {
        gr.next();
        val = gr.getUniqueValue();
    } else {
        gr.initialize();
        gr.addEncodedQuery("dept_head.email=" + val); // When getting the Dept Head Email.
        gr.query();
        if (gr.next()) {
            val = gr.getUniqueValue();
        } else {
            val = "";
        }
    }

    return val;
}

Step#4

Go to Field Normalization ==> Administration ==> Transform Categories

Under the "Transform Definition" related list, add the above Transform Definition like this

Step#5

Go to the Field Normalization ==> Configurations ==> Transformations

Create an entry like this

Step#6

Under the "Transforms" related list of the above Transformation, create an entry here.

Click on "New" button, you will be prompted to use the Transform definition, select that like this

Note: while creating the Transformation, set the Mode to "Test" initially and use "Test Transformation" before you make that as active transformation.

Step#7

Test the transform like this by providing a Department Head Email on Raw Data Field and click on OK. Once you click on "OK", you will see the transformed data as a sysid of the department.

Note:  You may go to the Department Table (cmn_department) and take one of the Department head email address and test using that.

Now, you are ready to activate this Transformation.  Go a head and activate this transform.

Step#8

Take a record from sys_user table and execute the below script from the background script like this

var gr = new GlideRecord("sys_user");
gr.get("62826bf03710200044e0bfc8bcbe5df1"); //Abel Tuter
gr.department = "rob.woodbyrne@example.com";
gr.update();

Since Rob Woodbyrne is the head of "Customer Support" Department, it will update the department with this even though you updated the department field with his email.

Once you execute the above script, it will update the record like this

Note: If you are using "Xanadu" version, you will see an option called "Turn on ECMAScript 2021 (ES12) mode" in Transform Definition.  Either you can toggle off that / you can modify the function by providing its identification like this

Feel free to provide your feedback /queries here and Like/Share if you think its useful.

Thanks,

Narsing