Need a script to populate previous sys_journal_line reference field

[TramaineM]

Hello,

I've recently added a reference field (u_user) on the sys_journal_line table that will default the user who submits it (javascript: gs.getUserID();). This was done in large part because it's very difficult to report on the system string fields of created_by and updated_by. This is working perfectly for new entries as it shows the created_by's display value.

However now, I want to go through all of the previous journal entries to populate the u_user reference field. How can I achieve this? Thanks!

[swathisarang98]

HI @TramaineM ,

You might have to write a fixed script or Background - script to update the field for older records.

Please mark this comment as Correct Answer/Helpful if it helped you.

Regards,

Swathi Sarang

[TramaineM]

Hi @swathisarang98,

Yes that's what I'm needing help with as I'm not a scripter. I've updated the title to avoid confusion.

[swathisarang98]

@TramaineM ,

You can run the below code in background script or fixed script,

var gr = new GlideRecord('sys_history_line');
gr.addEncodedQuery('u_user=NULL');
gr.query();
while (gr.next()){
    var users = new GlideRecord('sys_user');
    users.addQuery('name', gr.user_name);
    users.query();
    while(users.next()){
        gr.u_user = users.name;
        gr.updateWithReferences();
    }
    
}

Here i have used field as user name which is available in History table because created by was empty 

Note:  There is already a field called user which is reference to sys_user table which you can use aswell.

Please mark this comment as Correct Answer/Helpful if it helped you.

Regards,

Swathi Sarang

[Anand Kumar P]

Hi @TramaineM,

You can use below script in background script try it in lower instance

var journalLineGR = new GlideRecord('sys_journal_line');

journalLineGR.addQuery('u_user', 'ISEMPTY');

journalLineGR.query();

while (journalLineGR.next()) {
var createdBy = journalLineGR.getValue('created_by');

if (createdBy) {
journalLineGR.u_user = createdBy;
journalLineGR.update();
}
}

Mark it as helpful and solution proposed if it serves your purpose.
Thanks,
Anand