How to convert string to journal field through fix script?

[Kevin McGartlan]

Hello, 

We have a custom "Comments" field on the Time card form where users can enter notes on the form. We would like to convert the string field into the OOTB "Notes" journal input field which will roll into the activity stream. What the is the best practice to get this data migrated? We are thinking a fix script, but seeing as how our instance has over 400,000 time card entries, this may take some time and affect system performance. 

This is the fix script:

var gr = new GlideRecord('time_card');
gr.autoSysFields(false);
gr.query();

while (gr.next()) {
gr.notes = gr.u_comments.toString();
gr.setWorkflow(false);
gr.update();
}

With this script, nothing updates on the time_card table. If I remove the gr.setWorkflow(false); it will execute the script and migrate over the data. However, this is not feasible, as we don't want hundreds of thousands of emails to be sent out when all of the time cards are updated. Does anyone understand/know why this setWorkflow(false) is preventing the updates?

Thank you.

[Allen Andreas]

Hi,

As far as how to go about this, if you do it with fix script, you'd want to limit the transactions to maybe 1000 at a time? Something like that. Also, this would run for ALL of them in your current form. So you'd want to tighten the query I think to not run again on ones you've already done else you'll just loop yourself crazy. So maybe clear the comments field in your script as part of the migration? That way you can query for records that only have value in your custom field.

As far as format...the above script you put looks correct to me, so it should work, it could be that the query itself is being limited due to capping results. Again...try to set limit:

gr.setLimit(10);

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Kevin McGartlan]

I tested this in my PDI, where there are only a handful of time card records (9). Even in that environment, it doesn't change any of the values. The query is only that wide right now because of the testing in my PDI, when I eventually bring this script into our own instance, I will be sure to limit it to where the comment field is not empty. 

[Allen Andreas]

Hi,

Can you try setting that notes field to a quote?

Like..just to see if it does that?

gr.notes = "Test comment";

Just curious if that other field is already a string, you don't need to toString() it.

So wanting to see if works with quotes, if so, great, then drop toString from it.

Can you add a log statement to see if you enter the while loop?

I know you said if you remove it, it processes. Just not sure why it wouldn't work unless there's something you have set that is forcing that to be on...somehow.

What BR runs when you update?

Anyways, can you try the above?

Thanks!

[Kevin McGartlan]

Thank you for reply Allen. 

I changed the script to now be: 

var gr = new GlideRecord('time_card');
gr.addNotNullQuery("u_comments");
gr.autoSysFields(false);
//gr.setLimit(5);
gr.query();

while (gr.next()) {
gr.notes = "test comment";
gr.setWorkflow(false);
gs.log(gr.u_comments);
gr.update();
}

With this script, I was able to get log statements for each record that had a comment. However, the time card's notes activity stream was not updated to show "test comment". Also, to answer your question, there are 17 BR that run on update. These are all OOTB BR that were there when I had started my PDI a few days ago. None of the conditions on when to run are for when comments/notes changes, so I don't think that they would be interfering with this fix script. I could of course be wrong.