Scheduled job to create record

[Luis Nunez]

Hello,

Need a little help:

We have a boolean field for locations (18)   on the user table, if any of these fields are true, a scheduled job should run and query these and create a record for each true field on a separate table (Tape location). I actually managed to get this working with the following scheduled script:

AddTapeLocationLon();
function AddTapeLocationLon() {

   var gr = new GlideRecord('sys_user');
   var gtarget = new GlideRecord('u_tape_location');
   gr.addQuery('u_loc_lon', true);
   gr.query();
   while (gr.next()) {
   gtarget.u_tape_access = "London";
   gtarget.u_employee_name = gr.sys_id;
   gtarget.u_employee_number = gr.employee_number;
   gtarget.insert();
   gs.log("New location inserted: " + gtarget.u_number + " - " + gtarget.u_employee_name.name + " - " + gtarget.u_tape_access, "NUN");
   }
}

The issue is, that every time I run this job a new record gets created for an already existing entry.

I need to add a condition to this job in order to avoid this.

That would be, if any user has u_loc_lon set to true on the user table and matches a record on the Tape location table where the the user exists and has u_tape_access = "London". Of course, as always your help is much appreciated.

PS. I have 18 locations, so on this job, I need to create 18 different functions, one for each location, would there be a way around this, to wrap all into one function?

Cheers,

Luis

[rahulpandey]

Below may help

AddTapeLocationLon();

function AddTapeLocationLon() {

   var gr = new GlideRecord('sys_user'); 

   var gtarget = new GlideRecord('u_tape_location'); 

   gr.addQuery('u_loc_lon', true); 

   gr.query(); 

   while (gr.next()) {

   gtarget.addQuery('u_employee_name', 'DOES NOT CONTAIN', gr.sys_id);

   gtarget.query();

   while (gtarget.next()){

   gtarget.u_tape_access = "London"; 

   gtarget.u_employee_name = gr.sys_id; 

   gtarget.u_employee_number = gr.employee_number; 

   gtarget.insert(); 

   gs.log("New location inserted: " + gtarget.u_number + " - " + gtarget.u_employee_name.name + " - " + gtarget.u_tape_access, "NUN"); 

   }

   } 

}

[Luis Nunez]

Thanks! but this actually doesn't even create a new record.

It should create a record only if the combination of name and location doesn't exist on the target table.

Cheers,

Luis

[rahulpandey]

This may be not working because user is already in table. you can try below:

var gr = new GlideRecord('sys_user');   

   var gtarget = new GlideRecord('u_tape_location');   

   gr.addQuery('u_loc_lon', true);   

   gr.query();   

   while (gr.next()) { 

   gtarget.addEncodedQuery("u_employee_nameNOT LIKE"+gr.sys_id+"^locationNOT LIKELondon");

   gtarget.query(); 

   while (gtarget.next()){ 

   gtarget.u_tape_access = "London";   

   gtarget.u_employee_name = gr.sys_id;   

   gtarget.u_employee_number = gr.employee_number;   

   gtarget.insert();   

   gs.log("New location inserted: " + gtarget.u_number + " - " + gtarget.u_employee_name.name + " - " + gtarget.u_tape_access, "NUN");   

   } 

   }   

}

tweaking may require its just a design.

[poyntzj]

try this.

For each of your user records, is runs a quick get against the gtarget and if there is no match, creates a new one

AddTapeLocationLon(); 

function AddTapeLocationLon() { 



   var gr = new GlideRecord('sys_user'); 

   var gtarget = new GlideRecord('u_tape_location'); 

   gr.addQuery('u_loc_lon', true); 

   gr.query(); 

   while (gr.next())

   { 

             if (!gtarget.get(u_employee_name,gr.sys_id)

             {

                       gtarget.initialise();

                       gtarget.u_tape_access = "London"; 

                       gtarget.u_employee_name = gr.sys_id; 

                       gtarget.u_employee_number = gr.employee_number; 

                       gtarget.insert(); 

                       gs.log("New location inserted: " + gtarget.u_number + " - " + gtarget.u_employee_name.name + " - " + gtarget.u_tape_access, "NUN"); 

             }

   } 

} 

Cheers