how to compare every record of a table with another table

[a2gupta]

Hi,

I have a table called u_contractors which gets updated with record each time a request is submitted from workflow Run Script activity.The below script is used:-

var first = current.variables.First_Name;

var last = current.variables.Last_Name;

var fullname = first + " " + last;

gs.log("fullname=" +fullname);

var gr = new GlideRecord('u_contractors');

gr.initialize();

gr.u_start_date = current.variables.Start_Date;

gr.u_end_date = current.variables.End_Date;

gr.u_name = current.request.requested_for;

gr.u_fullname = fullname;

gr.insert();

Now the requirement is we have to check in User(sys_user) table to see if that user record exists or not.User data is loaded into system daily. So after the request is submitted,we have to check each entry in u_contractor table to each entry in User table. If the "u_fullname" field matches with 'name' field in user table,then only should the contractor table should be updated. If I understand correctly,we have to glide the user table and store every record in an array and then compare each array element with each of the 'fullnames' in contractor table. I wrote the below code but it is returning as undefined:-

var ag = new GlideRecord('sys_user');

ag.addActiveQuery();

ag.query();

var array = [];

while(ag.next()){

array.push(ag.getValue('sys_id'));

}

for(var count=0;count < array.length; count++){

gs.log("array =" +array[count].name);

}

How can this be achieved? Any script shared will be helpful,

[Chuck Tomasi]

Hi Anurag,

Is there some field you are using for uniqueness such as an email address? It is very likely that if all you are storing is first and last name that you will eventually get conflicts.

As for checking against existing users, don't retrieve everything, let the database do that with something like:

var ag = new GlideRecord('sys_user');

ag.addQuery('u_name', current.variables.requested_for);

ag.query();

if (ag.next()) {

        gs.log('That user already exists');

}

[Chuck Tomasi]

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

If you are viewing this from the community inbox you will not see the correct answer button.   If so, please review How to Mark Answers Correct From Inbox View.

Thank you