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

[Danny Rhoades]

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!

[Michael Jones -]

Typically speaking, and somewhat for the reasons you are posting about, it's a best practice not to change variables, but rather to create new ones and, if necessary, hide the old ones. This maintains your historical data and prevents other potential issues that might break scripts, UI Policies and workflows. 

If you absolutely are set in that you must modify, rather than add a new one, you'll actually find that the old value is still there; it just doesn't look like it because the sys_id stored in the field no longer matches a record on the table you are referencing. 

You can validate this is true with a script like this in scripts - background: 

var req_item = '<sys_id_of_an_existing_ritm>';
var current = new GlideRecord('sc_req_item');
current.get(req_item);
gs.info(current.variables.<your_reference_field>); //should see a sys_id

var ref_rec = new GlideRecord('<your_old_table>');
ref_rec.get(current.variables.<your_reference_field>);
gs.info(ref_rec.getDisplayValue()); //should see the display value of your old record

Provided this is true, you would run a script like this to (hopefully) find the record that matches your new table and set it. 

var req_item = '<sys_id_of_an_existing_ritm>';
var current = new GlideRecord('sc_req_item');
current.get(req_item);

var ref_rec = new GlideRecord('<your_old_table>');
ref_rec.get(current.variables.<your_reference_field>);

var new_ref = new GlideRecord('<your_new_table>');
new_ref.addQuery('name', ref_rec.getDisplayValue()); //name is a guess! Modify as needed
new_ref.query();

if(new_ref.next()) {
current.variables.<your_reference_field> = new_ref.getUniqueValue();
current.update();
}

You would obviously need to test this out extensively before you try mass updates, but this would be the general idea. You'd just need to iterate through your requests. 

I hope this helps!

If this was helpful or correct, please be kind and remember to click appropriately!

Michael Jones - Proud member of the CloudPires team!

[Danny Rhoades]

Michael, your solution worked perfectly, with literally no changes.  Much gratitude!  

I think the only change I'll try to make now is to iterate through all applicable RITMs.  I'm guessing I would bridge the two scripts with <var req_item = ugr> Is that viable?  Here's the code I'm thinking of using, please let me know if I'm crazy or way off mark:

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 req_item = ugr; <-- WRONG!
var req_item = ugr.sys_id; // CORRECT!
        var current = new GlideRecord('sc_req_item');
        current.get(req_item);

        var ref_rec = new GlideRecord('cmn_department');
        ref_rec.get(current.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()) {
            current.variables.<my_ref_field> = new_ref.getUniqueValue();
            current.update();
        }
    }

}

Edit:  I tested this script and found I actually need to bridge with <ugr.sys_id> instead of just ugr

But it works for exactly what I need, so thank you so much again for the help!

Here are my steps in order:

1.  Migrated data from cmn_department to business_unit but left original values in the cmn_department table for the time being.  I also ensured, the names were identical and reverted any changes made during migration.

2.  Updated the type specifications on my reference field variable to point to the business_unit table instead of cmn_department.

3.  Since this happened to be a variable set, I looked at the "Included In" tab to locate all the catalog items it was used in – I also went through all my workflows, business rules, and other automation items to see if anything else relied on this data.

4.  Then I ran a filter on my RITM table for cat_item names that matched the included in tab and copied that query so I could use it in the script

5.  Ran a Fix Script based on the code in this post

[Michael Jones - · Accepted solution]

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!

[Danny Rhoades]

Excellent, thank you for the optimized script!