Update record using On After sctipt on ETL Definition

[Community Alums]

Hello,
I'm trying to update record in the interface using Robust Import Set Transformer with additional Timestamp & source value. The idea is just to modify "source" field in target user record.

(function onAfter(source, target, importLog) {

var gr = new GlideRecord('sys_user');
gr.get(target.sys_id);
gr.setValue('source', "SourceStampTEST");
gr.update();

})(source, target, importLog);

I use the code above, however it does not update records modified while executing the job. If I replace target.sys_id with the sys_id value, it updates the record correctly.

How can I access target record's sys_id? It's clearly the blocking point for me on this stage. Target does not work in ETL Definition? Thanks in advance for help!

[Voona Rohila]

Hi @Community Alums 

Did you keep a log and verified the target sys_id value?

Try this and see the log value.

(function onAfter(source, target, importLog) {

var gr = new GlideRecord('sys_user');
gs.info("Target sysid " +target.sys_id.toString());
if(gr.get(target.sys_id.toString()))
{
gr.setValue('source', "SourceStampTEST");
gr.update();
}
})(source, target, importLog);

[Community Alums]

Thank you for this suggestion. Indeed, I did not get the clear SysID, it was encoded in JSON. I needed to use JSON.stringify to retrieve real value:

    var str = JSON.stringify(target);
    var parser = new JSONParser();
    var parsed = parser.parse(str);
    var result = parsed.sys_user[0].sys_id;

[Community Alums · Accepted solution]

Just not to let topic without solution - ETL definition does not give the target record straightforward (as it is in Transform Maps). You need retrieve SysID value from JSON. Do you think it's a bug or feature? Or maybe there is a different (easier) method to update target records?

This is ready code:

// This scripts sets time stamp in "Source" field of target user record.

(function onAfter(source, target, importLog) {

    var gr = new GlideRecord('sys_user');

    // Finding Sys ID of target user record
    var str = JSON.stringify(target);
    var parser = new JSONParser();
    var parsed = parser.parse(str);
    var result = parsed.sys_user[0].sys_id;    

    // Composing content of text note in "Source" field - using "gs.nowDateTime()" to get user's time (admin's time), so it's be adjusted to system time
    
    timeStamp = gs.nowDateTime();
    sourceText = "YOUR TEXT | " + timeStamp;

    // Updating target record with time stamp in "Source" feild
    if (result){
    gr.get(result);
    gr.setValue('source', sourceText);
    gr.update();
    }

})(source, target, importLog);

[RiteshSwarnakar]

Hi @Community Alums,

You are correct. 

We cannot directly get the sys_id from target by using target.sys_id, as the target Object is something like below:

{"sys_user":[{"name":"Peter","sys_domain.sys_id":"b9a3404cdbbb819ce4d46b6ge6745","company.sys_id":"6fer44351b5b2d50f4b687b1f54bcb1c","sys_id":"108ac2871b47f5d01b15844ee54bcb80"}]}

An easier way to get the user record sys_id would be to use the below logic, that way we don't have to hardcode any table name like parsed.sys_user[0].sys_id as there can be multiple tables/entities being used in same ETL definition.

(function onAfter(source, target, importLog) {

for (i in target){
    var id=target[i][0].sys_id;
}

var timeStamp = gs.nowDateTime();
var sourceText = "YOUR TEXT | " + timeStamp;

var gr = new GlideRecord('sys_user');
    gr.get(id);
    gr.setValue('source', sourceText);
    gr.update();
 
})(source, target, importLog);