Batching Update of records

[Piemur1]

Hello, I am going to use the NeedIt example practice/training application as an example. How would I update the NeedIt records to look up the email of each of the records for the "requested_for" user field and store the result in the "requested_for_email" in the record? So I would like to make it a scheduled script execution running every few minutes to only update the first 100 records where the email address is blank. I was thinking along the lines of setting a dirty-bit hidden field and updating only the first 100 records where that field hasn't been marked until every record has been marked as having been processed.

Or conversely, I could mark the first 100 records where the email still HASN'T been added and process THOSE records until the marking process cannot mark anymore records with a missing email address (ie all records where email field has an email address.). When it processes a record in the course of its loop, it removes that marked field.

The purpose for this is I am attempting to update records in batches to simulate having a very large database where I would need to update every record with a missing data field and don't want to lock up the system by attempting to update every record with one single script all in one go.

I used to do something like this with a SQL script but moving to ServiceNow from another platform I am attempting to learn JavaScript and the methods and syntaxes for all that. Still getting used to GlideRecords and Ajax and such, so I'm learning how to perform a somewhat complex process such as batching to pull email from a user record whose user record is from a field and all that, I just want to wrap my head around it. How to pull the email field from the "requested_for" user and put that in the "requested_for_email" field?

The pseudocode would look something like this:

Scheduled Script Run every 5 minutes

//Initialize mark first 100 records without an email
var counter = 0;
while counter <= 100; //Yes I know this can be an endless loop, I need to figure out how to break if it has gone through every record and counter is still less than 100
{
    if (NeedIt.requested_for_email == null)
    {
        NeedIt.dirtybit = true; //Mark 100 records with no Email
        counter =+ 1; //Increment counter
    }
}
//Can skip processing altogether if the counter is 0 at the end
if (counter == 0)
{return}

for each (NeedIt.dirtybit = true) //loop through database for only the marked records or just update each record marked.
{
    var emailLookup = lookup(requested_for); //look up email address for user in requested_for field
    NeedIt.requested_for_email = emailLookup; //set email into the NeedIt Record
    NeedIt.dirtybit = false; //clear dirty bit
}

I'm not entirely sure what the exact syntax for all this would look like, so taking the NeedIt application as a foundational example could someone help write it as a proper script so I can learn the syntax. There are many many different ways to do this so if you know a better method, by all means share as well!

[Arpan Baishya · Accepted solution]

Hi @Piemur1,

You could try writing your script like this.

var needit_gr = new GlideRecord('insert_the_name_of_the_needit_table');
needit_gr.addNullQuery('u_requested_for_email');
needit_gr.setLimit(100);
needit_gr.query();

while(needit_gr.next()){
    needit_gr.setValue('u_requested_for_email', needit_gr.u_requested_for.email);
    needit_gr.update();
}

Hope this helps. 

[Piemur1]

Thank you. Is there a way to write the conditional on the Scheduled Script Execution so it only runs if there are any records where the Requested_for_Email is null? I would prefer that it not run unnecessarily if there are no records to even be run against. Is there a method of setting the Scheduled Script Execution to be inactive (Uncheck the active checkbox on the record itself)?

[Piemur1]

It's running like a charm! Thank you! So now, is there a way to set an Order By to for example update the oldest Created dates first? Right now, there doesn't seem any rhyme or reason in which order these are being updated, but at the moment it does NOT appear to be updating by "Number" order or create date. Is there a preference for what order it does these updates, so long as they fulfill the .addNullQuery requirement?

[Arpan Baishya]

You may use the line below to order the NeedIt records in ascending order.

needit_gr.orderBy('sys_created_on');