Help required with Script for Transform Map

[Jason Nicholas]

Hi

I have a transform map that without the script does the following after importing all project tasks via a spreadsheet:

Source.u_line_unique_key > Target.u_unique_ref (Coalesce)

Source.u_start_date > Target.start_date

Source.u_end_date > Target.end_date

As part of the mapping I have the following script, what I am trying to achieve is

• Where the Project Task is NOT in a pending (-5) state ignore altogether
• If the Task state is pending (-5) then clear the date field
• However if the Task state is Pending AND the u_set_date on the task is marked as is true then set the dates (I will eventually want it to set it to leave the dates as they are)

I have tried every which way to configure that I can think of but it either 

• Updates all tasks based on the spreadsheet dates
• Clears all dates on every project
• Does nothing

Can anyone advise please

------------------------------------------------------------------------------------------------------------------------------------------

(function transformRow(source, target, map, log, isUpdate) {

    var PTask = new GlideRecord('pm_project_task');

    //  PTask.addQuery('u_set_dates', true);

        PTask.addQuery('state', -5);

        PTask.query();

while(PTask.next()){

        if (PTask.u_set_dates == 'true'){

            target.start_date = '01/01/2025';  

            target.end_date = '02/01/2025';

            gs.log('wxy: project found ' + PTask.number + '- ' + target.start_date + ' - ' + target.end_date);

        //  ignore=true;

        }

            target.end_date = '';  

            target.start_date = '';

        //  ignore=false;

    }

})(source, target, map, log, action==="update");

------------------------------------------------------------------------------------------------------------------------------------------

[Ankur Bawiskar]

@Jason Nicholas 

where have you written this? onBefore transform script?

what's the field map?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Jason Nicholas]

Its written on a transform Map

[Zach N]

Hey Jason!

Based on what you provided, it looks like you're running what ServiceNow calls an explicit transform map script. It runs after the source field values have been copied over to the target record, and before they are written to the database.

Based on that information, I think part of the issue is that you are performing a lookup in the database outside of the transform map process, which means that this script is running for each row in your import but before it has been imported into ServiceNow. That probably explains some of the odd behavior you're seeing as it is trying to modify data that doesn't exist yet.

With that, you will probably want to update your code to something like this:

(function transformRow(source, target, map, log, isUpdate) {

  // First we set some variables for readability.
  //
  var pTaskState = target.state;
  var pending = -5;

  // Then we check if the task is pending.
  //
  if (pTaskState == pending) {

    // We use target instead of source because the target record already exists.
    //
    target.start_date = "";
    target.end_date = "";

    if (target.u_set_dates == "true") {

      target.start_date = "01/01/2025";
      target.end_date = "02/01/2025";

    }

  } else {
    // Otherwise, we set the ignore flag to true and skip the record.
    ignore = true;
}

})(source, target, map, log, action === "update");

Note that the above code is untested, so please try it out and fix any errors that might come up. In general though, since this script is running per row, we want to use target to update our records and shouldn't need to perform a lookup elsewhere in the database.

Hope this helps! Let me know if the script doesn't work or you run into any other issues.

EDIT: Forget else statement.

[Jason Nicholas]

Thanks Zach

I am now half way there as it is now only updating the pending tasks with a blank date and updating the others according to the spreadsheet.

The one with set dates true is also updating as blank so I now just need to tweak the code to change this 

Thanks again

Jason