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!

[Bert_c1]

Set the scheduled job to run "on demand".

[Piemur1]

@Bert_c1  I don't want to run it "on demand" as that would require me to continually run it manually. "Periodically" is better because then I can schedule it to run every x minutes or hours or whatever timeframe I wish and repeatedly with a batching option so it doesn't saturate the server with a huge update that hypothetically could be the entire database. As I stated, this is a hypothetical update wherein I wanted to configure a batch job to run on a limited subquery of the database to update a lot of records but batched out to smaller chunks until completed. "On demand" would be like setting the job, but having to manually hit "execute now" over and over and over until they have all been completed. Doable if I were updating 1800 records in 100 record batches only 18 times, but what if I were updating 8,000,000+ records with a lot of updates? Do I lock up the database trying to run it on all those records at once? Let's say the updates are very resource intensive, so it would be best to ONLY run it in chunks of 100 records at a time. Any more, and it would lock the system and no one can get anything done until the batch is done. Do I have to hit the button to run 8000 times? Or set a job to chunk it out every 10 minutes, give the system time to breathe and users able to do some work, and over time will eventually have managed to process all the records? THAT is the intent of the question. I wasn't aware of the x_gr.setLimit(100); function nor some of the functions for setting up queries and x_gr.next() loops, considering I am a relative beginner developer in the environment.

All criticism aside, thank you for the advice. I understand you're just trying to help. ❤️ Just wanted to explain why that option would not work for my hypothetical scenario.

[Bert_c1]

Try the following from Arpan's script:

// Batch Update records
while (true) {
	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();
	var numberRecs = needit_gr.getRowCount();
	// see if we're done, if so exit while loop
	if (numberRecs == 0)
		break;
	gs.info("Batch update records: found " + numberRecs + " to process.");
	while(needit_gr.next()){
		needit_gr.setValue('u_requested_for_email', needit_gr.u_requested_for.email);
		needit_gr.update();
	}
}