Hi everyone,
I stumbled upon a really weird issue I can't wrap my head around.
I am trying to populate a date field in a table based on historical data of specific field-updates ("Status" field updates in the sys_history_line table of the records).
Since I am dealing with ~1600 records, I wanted to accomplish this via a background script - you can find the code below and in the attached screenshot.
The script gets all records of a custom extension of the task table, that are in any closed state and tries to query for those records sys_ids in the history table with the "state" field requirement.
When testing this on single records with their individual sys_ids, I don't have any issues, but when applying it to all 1600 records, I get for most records no match - even though each record that is in a closed state, has to have a historic event, where the state was actually set to any of those closed states.
However, and this is the weird part, once I open a record in the environment manually (List of records of the queried table, and simply opening any of the records), this record will then be queried correctly with the script, but not before that.
My question:
> Is there any necessity to initialize records in an environment before the referencing with the sys_history_line tables work? When testing myself, I can also not use the "Set" column of the sys_history_line table for filtering manually, before I have not opened the record, I am looking for, at least once.
Background:
> The DEV environment I am working in was just mirrored a few days ago from the production environment
I welcome any feedback that might help me to understand the underlying issue here and how to resolve it without opening each individual record before running the script.
Many thanks in advance!
Script below
_________________________________________________________
// query for closed states in table
var queryString = 'state=3^ORstate=4';
var gr = new GlideRecord('x_mscdx_ugc_om_ugc');
gr.addEncodedQuery(queryString);
gr.setLimit(20); //set limiter for testing
gr.query();
while (gr.next()){
// create a GlideRecord of the sys_history_line table
var history_gr = new GlideRecord('sys_history_line');
// query for history entries with the respective sys_id of the record and the state field
history_gr.addQuery('set.id',gr.sys_id);
history_gr.addQuery('field','state')
// put the matching records in order (e.g. all state changes) with latest at the top
history_gr.orderByDesc('update_time');
// get just the record on top (latest record)
history_gr.setLimit(1);
history_gr.query();
//provide individual record information for testing
gs.info(history_gr.getRowCount());
gs.info(gr.number);
gs.info(gr.sys_id);
if (history_gr.next()) {
// set the archiving date of the current normal record to the update-time of the status field + x days
// variable for storing glide records
var archiving_gdt = new GlideDateTime(history_gr.update_time);
archiving_gdt.addDaysUTC(730);
gs.info("update_time:" + archiving_gdt);
}
}
_________________________________________________________
