How to change sys_created_on datetime field value using insert business rule

[Milan13]

Hello,

I have an insert/update business rule running upon sc_request table which inserts data into time_card table.

What I need is,  once the sc_request REQ record is updated and the business rule reacts by inserting REQ record data into time_card, sys_created_on field in time_card table should not be filled with actual time stamp of the REQ record update time (approval datetime "timestamp") but should take the date of REQ - "Opened" field (opened_at) and insert it into time_card table sys_created_on field - please see attached screen shots.

Snippet of  Gliderecord code in my business rule which should have done this but is not working:

 gr.sys_created_on = current.opened_at;

But for example this snippet of code in my BR is working fine (REQ user reference field being copied to time_card user reference field)

gr.user = current.requested_for;

Many thanks for any advice,

Milan

[Milan13]

It's quite big.....but not really urgent for the time being...just in case you are interested 🙂

I have highligted one of the code snippet occurences in orange, the part with user works, the other one with sys_created_on not...(as you mentioned - you can only update it).

Thanks a lot for checking but as I said not a priority, so you don't really have to...Milan

(function executeRule(current, previous /*null when async*/) {

//make GlideDateTime object
var dateFromGdt = new GlideDateTime(current.getDisplayValue('u_date_from'));

var dateToGdt = new GlideDateTime(current.getDisplayValue('u_date_to'));


var type = current.u_type;

var request_description = 'Description: ' + current.description.toString() + "\n" + 'Number: ' + current.number.toString();

var dateFromGdate = dateFromGdt.getDate();
var dateToGdate = dateToGdt.getDate();

//get indexes of week days of date_from and date_to
var date_from_day_number = dateFromGdt.getDayOfWeek();
var date_to_day_number = dateToGdt.getDayOfWeek() - 1;


//week_starts_on for date_from and date_to
dateFromGdate.addDays(-(date_from_day_number-1));
dateToGdate.addDays(-(date_to_day_number-1));

//First Monday of request
var first_monday = new GlideDateTime(current.getDisplayValue('u_date_from')).getDate();
first_monday.addDays(-(date_from_day_number-1));

//Array of week_starts_on
var mondayArr = [];
mondayArr.push(first_monday.getValue());


//dateToGdate = last week_starts_on
while(first_monday < dateToGdate) {
first_monday.addDays(7);
mondayArr.push(first_monday.getValue());

}



//first Monday
var mondayArr_min_value = mondayArr[0];

//last Monday
var mondayArr_max_value = mondayArr[mondayArr.length-1];


//determine 0h days in the first week of REQ
// 1 = day in the week for Monday
var day_indexes_0h_first_week = [];
var day_index_req_date_from = dateFromGdt.getDayOfWeek();

//Count of days since beginning of 1st week including Monday with 0h
var days_from_first_monday = day_index_req_date_from-1;


var y;
for (y=1; y<day_index_req_date_from; y++) {

if (y==1) {
day_indexes_0h_first_week.push('monday');
}

if (y==2) {
day_indexes_0h_first_week.push('tuesday');
}

if (y==3) {
day_indexes_0h_first_week.push('wednesday');
}

if (y==4) {
day_indexes_0h_first_week.push('thursday');
}

if (y==5) {
day_indexes_0h_first_week.push('friday');
}
}




//determine 0h days in the last week of REQ
// 5 = day in the week for Friday
var day_indexes_0h_last_week = [];
var day_index_req_date_to = (dateToGdt.getDayOfWeek()) - 1;


//Count of days with 0h since request date_to till Friday
var days_from_req_last_day = 5-day_index_req_date_to;

var z;
for (z=day_index_req_date_to+1; z<6; z++) {

if (z==1) {
day_indexes_0h_last_week.push('monday');
}

if (z==2) {
day_indexes_0h_last_week.push('tuesday');
}

if (z==3) {
day_indexes_0h_last_week.push('wednesday');
}

if (z==4) {
day_indexes_0h_last_week.push('thursday');
}

if (z==5) {
day_indexes_0h_last_week.push('friday');
}

}

////////////////////////////FUNCTION DECLARATIONS - BEGIN/////////////////////////////////////////
function returnReqType(reqType) {
if(reqType == 'free_day') {
//Label:'Free day / Sick day';
type = 'sick_day';

}
else if(reqType == 'holiday') {
//Label: 'Time off';
type = 'time_off';
}

return type;
}


//Function determining whether the day supposed to be filled already has not a value greater than 0
//for given type of entry
function returnDaysWithHours(weekStartsOn, userId, reqType) {

//array of days with hours greater than 0 for given type of request
var arrFilledHours = [];

var grCheck= new GlideRecord('time_card');

grCheck.addQuery('week_starts_on', weekStartsOn);
grCheck.addQuery('u_resource', gs.getUserID());

if(type == 'free_day') {
grCheck.addQuery('category', 'sick_day');
}

if(type == 'holiday') {
grCheck.addQuery('category', 'time_off');
}

grCheck.query();

while(grCheck.next()) {
if(grCheck.monday > 0) {
arrFilledHours.push('monday');
}
if(grCheck.tuesday > 0) {
arrFilledHours.push('tuesday');
}
if(grCheck.wednesday > 0) {
arrFilledHours.push('wednesday');
}
if(grCheck.thursday > 0) {
arrFilledHours.push('thursday');
}
if(grCheck.friday > 0) {
arrFilledHours.push('friday');
}

}

return arrFilledHours;
}

//Function determining whether there is a record for the same person and the same type of request in a given week
function existingRowId (weekStartsOn, reqType, userId) {
var gr = new GlideRecord('time_card');
gr.addQuery('week_starts_on', '=', weekStartsOn);
gr.addQuery('category', '=', reqType);
gr.addQuery('user', '=', userId);
gr.query();
var rows = gr.getRowCount();
var sysId = 0;

//!!!vyzkoušet bez rows==1
//while(gr.next() && rows==1) {
while(gr.next()) {
sysId = gr.sys_id;
}
return sysId;
}

var arrWeekDays = ['monday','tuesday','wednesday','thursday','friday'];

////////////////////////////FUNCTION DECLARATIONS - END/////////////////////////////////////////

////////////////////////SCENARIO 1 - REQ has more than 1 week and 8h - beginning///////////////////////////

if (mondayArr_min_value != mondayArr_max_value && current.u_days != 0.5) {
var i;
for (i = 0; i < mondayArr.length; i++) {
var gr = new GlideRecord('time_card');
gr.initialize();

//for week where all 5 days are holiday days
if(i!=0 && i!=(mondayArr.length)-1) {

var arrFilledHours1A = [];

//checks if days in the week have already been filled by other REQ
arrFilledHours1A = returnDaysWithHours(mondayArr[i], gs.getUserID(), type);

gr.week_starts_on = mondayArr[i];
gr.user = current.requested_for;
gr.sys_created_on = current.opened_at;

gr.category = returnReqType(type);


for(var i1 = 0; i1 < 5; i1++) {

var str = arrWeekDays[i1];

gr.setValue(str, 8);
str = 'u_' + str + '_notes';
gr.setValue(str, request_description);

}

}

if(i==0 ) {

var arrFilledHours1B = [];

arrFilledHours1B = returnDaysWithHours(mondayArr[i], gs.getUserID(), type);


gr.week_starts_on = mondayArr[i];
gr.user = current.requested_for;
gr.sys_created_on = current.opened_at;

gr.category = returnReqType(type);

for(var i2 = 0; i2 < 5; i2++) {

var str2 = arrWeekDays[i2];

if (day_indexes_0h_first_week.indexOf(str2) == -1) {

gr.setValue(str2, 8);

str2 = 'u_' + str2 + '_notes';

gr.setValue(str2, request_description);
}

}

}

if(i==(mondayArr.length)-1 ) {

var arrFilledHours1C = [];
arrFilledHours1C = returnDaysWithHours(mondayArr[i], gs.getUserID(), type);


gr.week_starts_on = mondayArr[i];
gr.user = current.requested_for;
gr.sys_created_on = current.opened_at;

gr.category = returnReqType(type);


for(var i3 = 0; i3 < 5; i3++) {

var str3 = arrWeekDays[i3];

if (day_indexes_0h_last_week.indexOf(str3) == -1) {

gr.setValue(str3, 8);

str3 = 'u_' + str3 + '_notes';

gr.setValue(str3, request_description);
}

}


}

var existingRowSysId1 = existingRowId (mondayArr[i], returnReqType(type), current.requested_for);
gs.log('!MILAN BR TEST - Scenario 1: ' + existingRowSysId1 + ' - Week starts on: ' + mondayArr[i]);

if (existingRowSysId1 == 0) {
gr.insert();
}

else {
gr.addQuery('sys_id', existingRowSysId1);
gr.updateMultiple();

}

}
}

////////////////////////SCENARIO 1 - REQ has more than 1 week and 8h - end///////////////////////////

////////////////////////SCENARIO 2 - REQ has just 1 week and 8h - beginning///////////////////////////

if (mondayArr_min_value == mondayArr_max_value && current.u_days != 0.5) {



var gr2 = new GlideRecord('time_card');
gr2.initialize();

var arrFilledHours2 = [];

arrFilledHours2 = returnDaysWithHours(mondayArr[0], gs.getUserID(), type);

gr2.week_starts_on = mondayArr[0];
gr2.user = current.requested_for;
gr2.sys_created_on = current.opened_at;

gr2.category = returnReqType(type);



for(var j1 = 0; j1 < 5; j1++) {

var dayValue = j1+1;

var str4 = arrWeekDays[j1];

if(dayValue >= date_from_day_number && dayValue <= date_to_day_number){
gr2.setValue(str4, 8);
str4 = 'u_' + str4 + '_notes';
gr2.setValue(str4, request_description);
}
}

var existingRowSysId2 = existingRowId (mondayArr[0], returnReqType(type), current.requested_for);

if (existingRowSysId2 == 0) {
gr2.insert();
}

else {
gr2.addQuery('sys_id', existingRowSysId2);
gr2.updateMultiple();

}




}

////////////////////////SCENARIO 2 - REQ has just 1 week and 8h - end///////////////////////////

////////////////////////SCENARIO 3 - half day REQ - 4h - beginning/////////////////////////////


if (current.u_days == 0.5) {

var gr3 = new GlideRecord('time_card');
gr3.initialize();


var arrFilledHours3 = [];

arrFilledHours3 = returnDaysWithHours(mondayArr[0], gs.getUserID(), type);

gr3.week_starts_on = mondayArr[0];
gr3.user = current.requested_for;
gr3.sys_created_on = current.opened_at;

gr3.category = returnReqType(type);


for(var j2 = 0; j2 < 5; j2++) {

var halfDayValue = j2+1;

var str5 = arrWeekDays[j2];

if(halfDayValue == date_from_day_number){
gr3.setValue(str5, 4);
str5 = 'u_' + str5 + '_notes';
gr3.setValue(str5, request_description);
}

}

var existingRowSysId3 = existingRowId (mondayArr[0], returnReqType(type), current.requested_for);

if (existingRowSysId3 == 0) {
gr3.insert();
}

else {
gr3.addQuery('sys_id', existingRowSysId3);
gr3.updateMultiple();

}

}


////////////////////////SCENARIO 3 - half day REQ - 4h - end//////////////////////////////////



})(current, previous);

[AbhishekGardade]

There is ACL that may restrict you for editing the field:

Thanks,

Abhishek Gardade

[Ankur Bawiskar]

Hi,

why you want to set value to sys_created_on field? what value it is showing for opened_at?

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[The SN Nerd]

There is hidden rules behind the scene that always set sys_created_on to the current time regardless of what you set in your Business Rule.

It is possible to hack it to be whatever value you want, but I don't think that is the right approach to solve your problem.

sys_created_on is a metadata field that ideally reflects the truth of when the record was created.

I've had a similar business problem where we wanted Cases transferred to Incidents to have the same Opened time as the original Case for accurate SLA reporting. We decided to change the value of opened_at to the same as the Case rather than changing sys_created_on.

[NagyDan1]

You need to "hack" sys_audit table. That is the root of the info. You need to overwrite the field there.

But the trick, no one is telling is to check the "internal_checkpoint" of the field you want to overwrite. And you need to update all the other fields checkpoint too, which had the same checkpoint. ServiceNow handles these records as one "batch" that has the same checkpoint. And if you only change one in the "batch" it will know you are hacking, and revert it back, so that you have a bad day.

After you managed to hack the Audit table records, then you probably need to delete the HistorySet for the record, that will be rebuilt automatically based on the Audit records.

So the chain is like:

   Audit -> History Set -> History -> Task Record

You want to intervene at the root, which is Audit. Not at the Top!

Mark this useful, if it ever was 🙂