How to check the User present in two table through transform map script

[Munna1]

I have two tables named depatment_table and employee_table in both the table one field is common, called User field(Reference to sys_user table) when ever records are inserted respectively, How can we compare the records from both tables using that common field. I have written this code, but it is not working out. 

And please suggest me , shall I use the Transform script or the Source script.

If the user is present, It has to ignore .  

If not, it has to create in the target table.

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

var myUserTable = new GlideRecord('department_table');    // Source Table

myUserTable.query();

while (myUserTable.next()) {

    var userAccount = myUserTable.getValue('user_name');

    var myOtherUserTable = new GlideRecord('employee_table');   //target table

    myOtherUserTable.addQuery('user_name', userAccount);

    myOtherUserTable.query();

    if (!myOtherUserTable.next()) {

        //do something

        gs.addInfoMessage('user does not exist:' + mainUsers.name);

        myUserTable.update();

    }

}

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

[Ankur Bawiskar]

@Munna1 

you should use onBefore transform script

Script should work fine if table name, field names are correct

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

    var myUserTable = new GlideRecord('department_table'); // Source Table
	myUserTable.addEncodedQuery('user_nameISNOTEMPTY');
    myUserTable.query();
    while (myUserTable.next()) {
        var userAccount = myUserTable.getValue('user_name');
        var myOtherUserTable = new GlideRecord('employee_table'); //target table
        myOtherUserTable.addQuery('user_name', userAccount);
        myOtherUserTable.query();
        if (!myOtherUserTable.hasNext()) {
            //do something
            gs.addInfoMessage('user does not exist:' + mainUsers.name);
            myUserTable.update();
        }
		else{
			ignore = true;
		}
    }

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

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

[Munna1]

Hi @Ankur Bawiskar ,

I have tried with that script but it  is not working as expected

I have three tables named u_depatment_table (Source table) and u_employee_table(Target table)  and sys_user table in these tables one field is common, called User field(Reference to sys_user table) whenever records are coming from source table First it hast to cross check that user is present in the User table or Not  -> if it preset Then it has to insert the records in the target table respectively.

I have added few fields in the field mappings and set the User field to Coalesce

Here we set condition in transform map, to cross check the user is present in the User table before inserting the data in the target table.

And please suggest me , shall I use the Transform Run script or the  On before script.

 I have written this code, but it is not working out. 

var myUser = new GlideRecord('u_department_table'); // Source Table

myUser.addEncodedQuery('user_nameISNOTEMPTY');

    myUser.query();

    while (myUser.next()) {

        var userAccount = myUser.getValue('user_name');

        var myempUser = new GlideRecord('sys_user');  //user table

        myempUser.addQuery('user_name', userAccount);

        myempUser.query();

        if (!myempUser.hasNext()) {

var grtarget = new GlideRecord('u_employee_profile'); //target table

grtarget.initialize();

grtarget.insert();  

        }

else{

ignore = true;

}

    }

[Ankur Bawiskar]

@Munna1 

So department is the import set staging table

Other 2 tables are the target tables, what about "u_employee_profile" ?

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

[Munna1]

Hi @Ankur Bawiskar 

Here

->departmet table is the Source table

->u_employee_profile table is the Target table

And there is one common field in both the tables called User field( reference to sys_user table)

What ever the data we are passing from the Source table (with the help of user field  in source table) first we have to check that in the user table that weather that user is present or not in the Sys_user table

If it is present then we have to insert the records in the target table.