Is it possible to update a reference variable field without losing data on existing request records?

Go to solution
Danny Rhoades
Kilo Guru

‎03-16-2021 05:21 PM

Hi Experts!  I need help figuring this out. 

Situation:

  • I have a variable that currently points to our Departments table. 
  • Turns out, the data really belongs somewhere else and we need switch to using the Business Units table instead.
  • So far so good.
  • We'll also need to update the reference variables that currently point to these tables so they now point to the BU table.
  • These updates/changes are trivial with no problems per se... 

Problem:

  • As you may already know, when the reference variable is updated to point to the new table, the previous values that were selected and showing up on the RITMs go blank, since there is no longer a valid reference. 
  • Thus, I need to find a way to restore those previous values. 

Assumptions:

  • Find a way to query the affected records and variable fields, and if possible, populate them with their previous value.
  • Explore sc_item_option table and see how that might help

I'm still just a novice at scripting queries, so I'm not exactly sure of the right queries to invoke here.  My initial thoughts were to query RITMs that matched the cat items where the variable is in use.  Unfortunately, that's where I get stuck and I'm not sure how to proceed since I don't know how to lookup the previous field values. 

Another method I had considered was to copy the existing values to a temporary custom field on the RITM table and then pull from that field to restore the data after the update and then delete the field when complete.  But I'm not sure I like that method very much.

Please let me know what might be the best approach.  Thanks!

0 Helpfuls
  • 2,830 Views
1 ACCEPTED SOLUTION

Go to solution
Michael Jones -
Giga Sage
In response to Danny Rhoades

‎03-17-2021 10:58 AM

Glad I was able to help and you're on the right track; in this case you don't need to set "current" as you already have the reference to the item in your outer GlideRecord for ugr (you want to update the records that you find). 

Also, just as a heads up, it's usually best to avoid nesting GlideRecord queries as this can have a performance impact, but for something you only need to run once, you won't likely encounter any issues unless you have 500k+ records to update. Just something to be aware of for future development. 

Something like this should work for you!

var ugr = new GlideRecord('sc_req_item');
ugr.addEncodedQuery('cat_item=<my_cat_item_sys_id>^ORcat_item=<my_cat_item_sys_id>');
ugr.query();

while (ugr.next()) {
if (!JSUtil.nil(ugr.variables.<my_ref_field>)) {

        var ref_rec = new GlideRecord('cmn_department');
        ref_rec.get(ugr.variables.<my_ref_field>);

        var new_ref = new GlideRecord('business_unit');
        new_ref.addQuery('name', ref_rec.getDisplayValue());
        new_ref.query();

        if (new_ref.next()) {
            ugr.variables.<my_ref_field> = new_ref.getUniqueValue();
            ugr.update();
        }
    }

}

I hope this helps!

Michael Jones - Proud member of the CloudPires team!

I hope this helps!
Michael D. Jones
Proud member of the GlideFast Consulting Team!

View solution in original post

5 REPLIES 5

Go to solution
Danny Rhoades
Kilo Guru

‎03-16-2021 07:25 PM

Thanks for the response, super helpful.

I’m not sure hiding would solve my issue since it would hide the previous values on the RITM records as well, right? So I’d kind of be right back to a blank field on the RITM record either way.

The variable in question happens to be part of a variable set. Does that change anything?

I like the idea of keeping the field for historical data purposes but I’m not sure how to do it in a less complicated way than replacing it and updating it with a fix script.

I’m going to try out your scripts tonight and see how it goes. Either way, your response was super helpful. Thank you!

0 Helpfuls