State of Update in Transform script

[Daryll Conway]

I'm running an onBefore script on a transform map and I need to set the Import set Row state to update.

I'm aware of the ignore = true setting but I can't find a way to set this to 'update'.

Is this possible?

[poyntzj]

Let me try something tomorrow.

What is this information that you are bringing in ?   Is there a way I can get an example of a row or two ?

Would also be good to know how you determine if the record is unique or not.

Cheers

[poyntzj]

OK,

I created a test table with the following columns

• Region
• City
• Brand
• Device
• Cost

I imported the attached untitled1.xls file

I created a transform map and let it automap the fields.

On the Field maps, I set all fields except cost to be coalese fields

I ran the transform and I got 17 inserts, 10 updates.

all the records have a cost of 1, except for where the device is called A and this is 11 (as it would be the 11th row of data imported)

Of course this works as I can identify which fields make each row unique.   without this I am not sure how you are really able to say that a row is unique compared to another.

I then created untitled2.xls

this is just adding an extra column called sys_id

I created a new import table and a new transform map

I had to add sys_id mapping to SYS_ID and I made it coalese

Interestingly, I got 3 updates and 24 skips.

Must admit, I did expect 3 updates and 24 inserts.

the error in the transform is - Unable to resolve target record, coalesce values not present: u_sys_id

can you send me what your onBefore script was doing ?

Cheers

[Daryll Conway]

This is my onBefore script in it's entirety...

gs.include('TidalLogger');

var lgr = new TidalLogger();



function processInput() {

     //variable to handle validation status
     var validationPassed = true;



     //Validation for Update Web Service Inputs
     if (action == 'update') {



     //Validate Caller
     if (!JSUtil.nil(source.u_caller_id)) {

     if (!isValidCaller(caller)) { validationPassed = false; }

   }

   }



     //Validation for Insert Web Service Inputs
     if (action == 'insert') {



     //Validate Caller
     if (!isValidCaller(source.u_caller_id)) {

     validationPassed = false;

   }



     //Validate BSS
     var bss = new BSSCategorization();

     bss.setBusinessEntity(source.u_business_entities);

     bss.setService(source.u_service_type);

     bss.setSubService(source.u_sub_service);

     if(!bss.isValid(3)) {

     lgr.info("BSS Validation Failed.\n Business Entity: " + source.u_business_entities + "\nService Type: " + source.u_service_type + "\nSubService: " + source.u_sub_service);

   status_message = "Invalid Categorization";

     validationPassed = false;

   } else {

     //Calculate Priority
     target.priority = calculateIncidentPriority(source.u_impact, source.u_urgency); // Global Business Rule
     }

   }





     if (source.u_region.nil()) {

   target.u_affected_regions.setDisplayValue('US');

   } else {

   target.u_affected_regions.setDisplayValue(source.u_region);

     if (!NYSEUtils.isGuidList(target.u_affected_regions)) {

   response.error_message = "Invalid regions specified: " + source.u_region;

   NYSELogger.error("Invalid regions specified: " + source.u_region, 'TIDAL Incident Web Service');

   ignore = true;

   }

   }



     if (!validationPassed) {

   ignore = true;

     lgr.info("Validation failed for Import Set:" + import_set.number + " Record: " + source.sys_import_row + " request ignored");

   } else {

     lgr.info("Validation passed for Import Set:" + import_set.number + " Record: " + source.sys_import_row + " proceeding with " + action.toString());

   }

     return 0;

}





//Function to Validate the caller is populated and is not a service account
function isValidCaller(caller) {



     //short circuit
     if (JSUtil.nil(caller)) {

   status_message = "Caller value invalid, caller must be a valid service-now user";

     lgr.info("ERROR: Caller empty, for Import Set :" + import_set.number + " Record: " +source.sys_import_row);

     return false;

   }



     //Check User Exists in Service-Now
     var userRecord = new GlideRecord('sys_user');

     userRecord.addQuery('sys_id', caller);

     userRecord.query();



     if(userRecord.getRowCount() <= 0) {

   status_message = "Caller value invalid, caller must be a valid service-now user";

     lgr.info("Error: Invalid caller value, empty or not found in Service-Now. Import Set:" +import_set.number+ " Record: " +source.sys_import_row);

     return false;

   }



     //Check User is not a Service Account User
     if(userRecord.getRowCount() > 1) {

   status_message = "Caller value invalid, input returns ambigous results, caller must be a unique service-now user";

     lgr.info("Error Invalid Caller. Caller value " +caller+ " invalid, input returns ambigous results, caller must be a unique service-now user. Import Set:" +import_set.number+ " Record: " +source.sys_import_row);

     return false;

   }



     //Check is not Service Account
     if(userRecord.getRowCount() === 1) {

     userRecord.next();



     //check if has service account role
     lgr.debug("checking if caller "+caller+" is a service account");

     var userRoleRecord = new GlideRecord('sys_user_has_role');

     userRoleRecord.addQuery('user',userRecord.sys_id);

     userRoleRecord.addQuery('role.name','service_account');

     userRoleRecord.query();



     if(userRoleRecord.getRowCount() > 0) {

   status_message = "Caller value invalid, a Service Account cannot be used as a caller";

     lgr.info("Error Invalid Caller. Caller value " +caller+ " invalid, a Service Account cannot be used as a caller. Import Set:" +import_set.number+ " Record: " +source.sys_import_row);

     return false;

   }

   }



     //If no failures, return true
     return true;

}



//Process input values from web service
processInput();

[Daryll Conway]

I then replaced the last line with this ...

if(String(source.u_inc_sys_id).length > 31){

       var sID = String(source.u_inc_sys_id);

       var gr = new GlideRecord("incident");

       gr.addQuery("sys_id", String(sID));

       gr.query();

       if (gr.next()) {

               gs.log('found exisiting INC, updating ' + gr.number, 'DC Log');

               gr.work_notes = String(source.u_worknotes);

               gr.update();

               status_message = "Updated";

               ignore = true;

       }

}



if(String(source.u_inc_sys_id) == 'new' || String(source.u_inc_sys_id) == ''){

       processInput();

}

Which let me update an existing record when a sys_id is given (in the u_inc_sys_id field)

[poyntzj]

OK, try this

In your transform map, create a new field mapping. you want a source script and you want it to go to the SYS_ID file in the target table.   Make it a coalese

You then want to use this script which will read the Sys_id from the source and if there is none it will create a record and then return that sys_id.

You will need to amend the table name using target did not work

You may need to add some data if there are any mandatory fields in the target table.

once you are happy it works in dev / test, you can remove the gs.log lines.

You will see that the import will show you the new records with no sys_id as an update and not an insert -   due to the record being created by the Sys_id field mapping script

If there is a record with a new sys_id in the source table, this will be shown as an insert.

gs.log('starting');

if (source.u_sys_id != '')

{

       gs.log('found existing : ' + source.u_sys_id);

       answer = source.u_sys_id;

}

else

{       gs.log('creating new record - I hope');

       var gr = new GlideRecord('u_julestest1');

       //var gr = new GlideRecord(target);

       gr.initalize();

       gr.insert();

       answer = gr.sys_id;

       gs.log('record I got it : ' + gr.sys_id);

}