Transform script to check the source record exist in target table

[Sangeetha8]

Hi All,

I had a requirement to check the source field(caller)incident table is exist in the target field(approver)user table (sys_user) if the record is exist then map that field,else keep the target field as empty 

How to achieve this using tranform script in field mapping

Thanks

[Robbie]

Hi @Sangeetha8,

This is very easily skipped by the use of an 'onBefore' 'Transform Script'.

See the below screenshot to help visualize and use the following code within the Source Script. (Obviously, check against the appropriate field for your needs. Here I am using the email address)

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

if(source.u_email == target.email || source.u_email == target.user_name){

ignore = true;

}

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

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Robbie]

Hi @Sangeetha8,

Did you see my earlier response? Did this provide the solution? It should have.... how can I help you close this out?

My earlier response:

This is very easily skipped by the use of an 'onBefore' 'Transform Script'.

See the below screenshot to help visualize and use the following code within the Source Script. (Obviously, check against the appropriate field for your needs. Here I am using the email address)

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

if(source.u_email == target.email || source.u_email == target.user_name){

ignore = true;

}

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

@Sangeetha8 - To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie

[Sangeetha8]

Hi Robbie,

Instead of writing onbefore script, I have written a source script

answer = (function transformEntry(source) {

    var userRecord = new GlideRecord('sys_user');

    userRecord.addQuery('userRecord.name', source.u_approver);

    userRecord.query();

    if (userRecord.hasNext()) {

        if(target.approver==source.u_approver)

        {

        target.approver = source.u_approver;

        gs.log("if loop);

        }

    else

     {

        // No user records exist in the target table, set the target field as empty      

        target.approver = '';

        gs.log("user record is empty ");

     }

    }

})(source);

The log is not working in transform script ,how to write a transform script log.I need to know whether the field is target field (approver) is set to empty if no match is found in user table

[Robbie]

Hi @Sangeetha8,

I see a few issues in your script. Please note the highlighted in bold subtle but important changes I've made to help you with scripting moving forward.

answer = (function transformEntry(source) {

gs.log("Running Transform Script");

var userRecord = new GlideRecord('sys_user');

userRecord.addQuery('name', source.u_approver);

userRecord.query();

if (userRecord.hasNext()) {

if(target.approver==source.u_approver)

{

gs.log("Inside Approver match if loop");

target.approver = source.u_approver;

}

else {

// No user records exist in the target table, set the target field as empty

gs.log("user record is empty ");

target.approver = '';

}

}

})(source);

As an observation with this type of match, a name is not unique - it is not uncommon to have 2 users with the same name. I would steer you to use a unique identifier for a user such as email for example.

@Sangeetha8- To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Helpful.

Thanks, Robbie