Creating a Transform Map to upload records if the Employee_number is not blank

[Peter Williams]

Good Day,

i am having issues here, i have created a DataSource that is linked to a SQL staging table.

In this table it has employee_numbers and also a Department Code for the Sys_user table

What i want to do, is to first check if the employee_number exist on the Sys_user table and if so then Update the Department code in the u_department_code field i created on the sys_user table

I tried the Coalesce Field of the Employee_number to be indexed on the sys_user table and then update the department_code base on that however i found out some records doesnt have an Employee_number which cause the transform map to create Ghost records with no name or details just the eomployee number and department code

please help

[Murthy Ch]

Hello @Peter Williams 

Write onBefore script to check if the employee number is empty or not. If empty ignore that particular record inserting into the user table.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // Add your code here
    if (gs.nil(source.employee_number)) { //update with your actual employee number field
        ignore = true;
    }

})(source, map, log, target);

Hope it helps:)

[Murthy Ch]

@Peter Williams 

Also, make sure to uncheck the below field if it's checked.

[Peter Williams]

Thank you i was able to figure it out and created this script 

(function transformEntry(source) {

    ignore = true; // Start by ignoring the record

    // Query the sys_user table for the matching employee number

    var userGR = new GlideRecord('sys_user');

    userGR.addQuery('employee_number', source.u_employee_code);

    userGR.query();

    if (userGR.next()) {

        // If the record exists, update the department code

        userGR.u_department_code = source.u_department_code;

        userGR.update();

        // Allow the record to be processed

        ignore = false;

    }

})(source);

and have uncheck Run a Business Rule and it seem to work 

[Murthy Ch]

@Peter Williams Got it!