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!

[Sumanth16]

Hi @TramaineM ,

I think you need to get user values from the sys_audit table not in sys_journal_line table. All the changes made on record store in sys_audit table. 

If I could help you with your Query then, please hit the Thumb Icon and mark it as Correct !!

Thanks & Regards,

Sumanth Meda

[Subhashis Ratna]

Hi @TramaineM ,

Remember to test this script in a non-production environment first to ensure it behaves as expected. Once verified, you can execute it as a Fix Script in your PROD instance.

var journalGr = new GlideRecord('sys_journal_line');
journalGr.addNullQuery('u_user');
journalGr.query();
while (journalGr.next()) {
    var createdBy = journalGr.getValue('created_by');
    var userGr = new GlideRecord('sys_user');
    if (userGr.get(createdBy)) {
        // Set the u_user field with the user reference
        journalGr.u_user = userGr.getUniqueValue();
        journalGr.update();
    }
}

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

Regards,

Subhashis