How do I transform a value into a variable and/or why are my values undefined?

[Casey23]

I am working on a new process for hires and terminations. Basically, we will be picking up a file and transforming the data using two different transform maps (hires and terminations). I currently have the transform for new hires working but am experiencing some odd issues with terminations.

For new hires I am simply getting the user's employee number, checking to see if they exist or not in the u_neo_attendees table, and then either inserting or updating the record. In this map, I have two filed maps setup for the empNumber and hireDate. This is all straight forward since I'm just dumping the data into a table.

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

   // Check if the Comparison field contains the letter "A"
   if (source.u_comparison.indexOf('A') !== -1) {

       // Get the EmpNumber field
       var empNumber = source.u_emp_number;

       // Query the u_new_attendees table to check for a match
       var attendeeGr = new GlideRecord('u_neo_attendees');
       attendeeGr.addQuery('u_user_id', empNumber);
       attendeeGr.query();

       if (attendeeGr.next()) {
           // If a match is found, update the record
           target.u_active = true; // Set u_active to true for updates
           target.update();
       } else {
           // If no match is found, insert a new record
           target.u_user_id = empNumber;
           target.insert();
       }
   }

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

The issue I'm running into is with terminations. When a termination is picked up in the file, we are creating a termination request in the instance. You'll notice that in the first "if" statement, I am basically doing the same thing as I am for new hires, I'm just looking for a different index. Then I'm setting variables the same way that I am for new hires. However this is where the issues start. The first issue is that, in the logging of those variables, they are returning "undefined". I originally discovered this by looking at the request that was created and the entry in the employee number and job title variables is indeed "undefined". I THINK the issue here is that I don't have any actual field mapping setup on the transform map (just this script) because you can't map a field from an import to a variable. Or at least this is my understanding right now.

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

    // Check if the Comparison field contains the letter "D" (aka Termination)
    if (source.u_comparison.indexOf('D') !== -1) {

        // Get the values from the source CSV file
        var empNumber = source.u_emp_number;
        var jobTitle = source.u_jobtitle;
        //var termDate = source.u_terminationdate;
        log.error('empNumber: ' + empNumber);
        log.error('jobTitle: ' + jobTitle);
        //log.info('termDate: ' + termDate);

        var cart = new Cart();
        // add in cart, substitute your cat item sys_id
        var item = cart.addItem(''); // In the environment I have the sys_id here

        // Query the user record to get the rest of their data
        var sgr = new GlideRecord("sys_user");
        sgr.addQuery("employee_number", empNumber);
        sgr.query();
        while (sgr.next()) {
            sid = sgr.sys_id;
            ph = sgr.phone;
            mgr = sgr.manager;
            name = sgr.name;
            uid = sgr.user_name;
            title = sgr.title;
            msg = 'This is an auto-generated termination request from HR for Employee ID#: ';
        }
        //cart.setVariable(item,'term_name_check','true');
        cart.setVariable(item, 'term_title', jobTitle);
        cart.setVariable(item, 'term_type', 'mihs');
        cart.setVariable(item, 'term_name', sid);
        cart.setVariable(item, 'term_first_name', sgr.first_name);
        cart.setVariable(item, 'term_last_name', sgr.last_name);
        cart.setVariable(item, 'term_cost_center', sgr.cost_center);
        cart.setVariable(item, 'term_location', sgr.location);
        cart.setVariable(item, 'term_department', sgr.department);
        cart.setVariable(item, 'term_user_id', uid);
        cart.setVariable(item, 'requested_for', sid);
        cart.setVariable(item, 'requested_by', gs.getUserID());
        cart.setVariable(item, 'manager', mgr);
        cart.setVariable(item, 'contact_number_na', '');
        cart.setVariable(item, 'u_requested_for', sid);
        //cart.setVariable(item, 'term_date', termDate);
        cart.setVariable(item, 'term_emp_id', empNumber);
        //cart.setVariable(item,'comments', msg + eid + "\nNotes: " + notes);
        var cartmsg = "Received from ADP: ";
        cart.setVariable(item, 'comments', cartmsg + empNumber);
        var rc = cart.placeOrder();
    }

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

The second issue, assuming the previous one has a resolution, is that the date format in the file doesn't match ServiceNow. This is fine in the new hire import because I just set the field map to match the date format in the file  and then it imports correctly. But if I can't map a field to a variable, then I need to figure out how to handle this because I get errors when I try to import the date.

So ultimately, I think I just need to understand how I can map an import field to a variable (or get the information there by other means) so that this process will work correctly. Anyone have thoughts or experience with this?

TIA

[Carlos Loza]

Hello,

one thing is, have you tried using the CartJS api instead of the Cart one?

iirc, the Cart one does weird things with some fields of the request

other thing, the script parser might be having trouble with the variables on line 23-28, (you need to declare them, or if you already did on another transform script, you need to pass them as parameters)

[Casey23]

I believe this is more related to the field mapping vs the API or the variables you mentioned.

This script is more or less a copy of the script that we are currently using, as the process is setup as an inbound action in production right now. In the request that's created, data appears to be filled in for those fields populated by lines 23-28, so I think it's okay. At least for now until I can figure out how to get the information from employee number, job title and date setup correctly. 

This morning while looking into it a bit more, I noticed in the import log the following error "Invalid map target does not exist in table sc_req_item". And it's an accurate statement. I'm just trying to determine whether or not there is a way to take the value from the spreadsheet and populate a variable without actually dumping the values into a table. It doesn't make sense to add this information to a table when we don't actually need it anywhere but in the request variables.

[Casey23 · Accepted solution]

The issue ended up being with where I had created the transform script. Apparently there are three different ways to setup a transform script:

• On the transform map record
• From the related list of the transform map
• On the individual field mapping

I had created the transform map from the related list. What I found is that by moving the script to the transform map itself, the data was actually showing up instead of coming through as undefined. I don't know why this is, but since it fixes the issue, this is the solution I'm going with.