question Re: Need a script to populate previous sys_journal_line reference field in Developer forum

Need a script to populate previous sys_journal_line reference field

TramaineM — Tue, 19 Mar 2024 15:56:36 GMT

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&colon; 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!

Re: Background script or job to populate previous sys_journal_line reference field

swathisarang98 — Tue, 19 Mar 2024 15:54:07 GMT

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

Re: Background script or job to populate previous sys_journal_line reference field

TramaineM — Tue, 19 Mar 2024 15:57:50 GMT

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.

Re: Need a script to populate previous sys_journal_line reference field

Anand Kumar P — Tue, 19 Mar 2024 16:10:05 GMT

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

Re: Background script or job to populate previous sys_journal_line reference field

swathisarang98 — Tue, 19 Mar 2024 16:25:50 GMT

@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

Re: Need a script to populate previous sys_journal_line reference field

Sumanth16 — Tue, 19 Mar 2024 16:36:15 GMT

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

Re: Need a script to populate previous sys_journal_line reference field

Subhashis Ratna — Fri, 22 Mar 2024 10:44:24 GMT

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