How to Set Values of a Related Field from another table via Script Backgrounds?

[Leo Ruggiero]

Hey all,

So there's a specific thing I'm trying to accomplish here for data retention for the business but I'm running into an issue where my values are coming up NULL and I'm not sure what's happening.

Long story short, business is wanting two different tables to have the same categories and if they change the category choices on table1, table2 must always update as well. So what I've done is, I removed table2's category fields and added related table1 category fields. So now when table1 gets updated, table2 reflects exactly the same. So good, that works. However... The issue I thought of is that now all of table2's categories will appear empty, the old category fields specific to the table would still be answered obviously, but the table is reflecting the related table1 category fields. So I decided to try and build a script that will take that old answer, and apply it to the new field. However I cannot get this to work. 

Can anyone review my script and see what needs to change so that I can retain this data?

var gr = new GlideRecord('u_pes');
gr.addEncodedQuery('number=PES0001003');
gr.query();
while(gr.next()){
	var number = gr.getValue('number');
	var category = gr.getValue('u_category_bs');
	var subCategory = gr.getValue('u_subcategory_bs');
	var subSubCategory = gr.getValue('u_sub_subcategory_bs');
	gs.log('Old FBS Category = ' + category);
	gs.log('Old FBS SubCategory = ' + subCategory);
	gs.log('Old FBS Sub-SubCategory = ' + subSubCategory);
	
	gr.setValue('u_related_fbs.u_category_bs',category);
	gr.setValue('u_related_fbs.u_subcategory_bs',subCategory);
	gr.setValue('u_related_fbs.u_sub_subcategory_bs',subSubCategory);

	gr.update();
    
	var newCat = gr.getValue('u_related_fbs.u_category_bs');
	var newSubCat = gr.getValue('u_related_fbs.u_subcategory_bs');
	var newSubSubCat = gr.getValue('u_related_fbs.u_sub_subcategory_bs');
	gs.log('New FBS Category = ' + newCat);
	gs.log('New FBS SubCategory = ' + newSubCat);
	gs.log('New FBS Sub-SubCategory = ' + newSubSubCat);

	gs.log('Ticket '+number+' updated.');
}

*** Script: Old FBS Category = AUS
*** Script: Old FBS SubCategory = Waiver
*** Script: Old FBS Sub-SubCategory = Question AT
*** Script: New FBS Category = null
*** Script: New FBS SubCategory = null
*** Script: New FBS Sub-SubCategory = null
*** Script: Ticket PES0001003 updated.

[Soeren Maucher]

Hello, you are trying to set the value via "dot walking", which is not working this way.
e.g. gr.setValue('u_related_fbs.u_category_bs',category);
When you perform the setValue() or update() on the "gr" object, you can not update the related object. 

I would suggest performing the GlideRecord directly on the table of your related field "u_related_fbs". 

I hope that helped!

Greetings,
Sören

[Leo Ruggiero]

I had a strong feeling the dot walking was the issue here, but I'm not sure where to even begin with setting up a gliderecord on the table for the related field I'm using. Never done this type of glide.

Would you be able to point me to any documentation that might help me with that? 

The fields themselves are on the table I'm gliding on, i.e.

The idea here is once I get 'u_category_bs' value, I can just put it to 'u_related_fbs.u_category_bs' on the form, once that's done I can just take the old 'u_category_bs' off the form.

[Soeren Maucher]

In this case your form was configured to show those fields, even though the values are actually stored on the related table/form. Thats possible and often required by clients. Technically, the values are still however stored on the referenced table and not on the current table. 

I have adjusted your script so that it does a second glide record "gr2" which is actually looping over the referenced table. This way you will perform the setValue() and upate() on the correct table. Here you would just have to add the table name to which the reference field "u_related_fbs" is pointing and it should work.

var gr = new GlideRecord('u_pes');
gr.addEncodedQuery('number=PES0001003');
gr.query();
while(gr.next()){
    
    //loop over your referencing table
    var gr2 = new GlideRecord('Table Name of referenced table'); //Replace by name of referenced Table form the field u_related_fbs
    gr2.addQuery("sys_id", gr.sys_id);
    gr2.query();
    
    
    var number = gr.getValue('number');
    var category = gr.getValue('u_category_bs');
    var subCategory = gr.getValue('u_subcategory_bs');
    var subSubCategory = gr.getValue('u_sub_subcategory_bs');
    gs.log('Old FBS Category = ' + category);
    gs.log('Old FBS SubCategory = ' + subCategory);
    gs.log('Old FBS Sub-SubCategory = ' + subSubCategory);
    
    //set values of gr2 instead of using dot walking on gr
    gr2.setValue('u_category_bs',category);
    gr2.setValue('u_subcategory_bs',subCategory);
    gr2.setValue('u_sub_subcategory_bs',subSubCategory);
    gr2.update();
    
    var newCat = gr2.getValue('u_category_bs');
    var newSubCat = gr2.getValue('u_subcategory_bs');
    var newSubSubCat = gr2.getValue('u_sub_subcategory_bs');
    gs.log('New FBS Category = ' + newCat);
    gs.log('New FBS SubCategory = ' + newSubCat);
    gs.log('New FBS Sub-SubCategory = ' + newSubSubCat);

    gs.log('Ticket '+number+' updated.');
}

Greetings, 
Sören

[Leo Ruggiero]

Running the above script seems to just insert a new record onto the gr2 table instead of updating gr1 table.

What's the point of querying the sys_id? Is it just so that we can query for the fields on that table and have a stored record of them?

var gr = new GlideRecord('u_pes');
gr.addEncodedQuery('number=PES0001003');
gr.query();
while(gr.next()){
    
    var gr2 = new GlideRecord('u_business_support');
    gr2.addQuery("sys_id", gr.sys_id);
    gr2.query();
    
    var number = gr.getValue('number');
    var category = gr.getValue('u_category_bs');
    var subCategory = gr.getValue('u_subcategory_bs');
    var subSubCategory = gr.getValue('u_sub_subcategory_bs');
    gs.log('Old FBS Category = ' + category);
    gs.log('Old FBS SubCategory = ' + subCategory);
    gs.log('Old FBS Sub-SubCategory = ' + subSubCategory);
    
    gr2.setValue('u_category_bs',category);
    gr2.setValue('u_subcategory_bs',subCategory);
    gr2.setValue('u_sub_subcategory_bs',subSubCategory);
    gr2.update();
    
    var newCat = gr2.getValue('u_category_bs');
    var newSubCat = gr2.getValue('u_subcategory_bs');
    var newSubSubCat = gr2.getValue('u_sub_subcategory_bs');
    gs.log('New FBS Category = ' + newCat);
    gs.log('New FBS SubCategory = ' + newSubCat);
    gs.log('New FBS Sub-SubCategory = ' + newSubSubCat);

    gs.log('Ticket '+number+' updated.');
}

[0:00:00.053] Script completed in scope global: script
Script execution history and recovery available here
Operation	Table	Row Count
insert	u_business_support	1
*** Script: Old FBS Category = AUS
*** Script: Old FBS SubCategory = Waiver
*** Script: Old FBS Sub-SubCategory = Question AT
*** Script: New FBS Category = AUS
*** Script: New FBS SubCategory = Waiver
*** Script: New FBS Sub-SubCategory = Question AT
*** Script: Ticket PES0001003 updated.

We would need it to update the related field on the first gr table u_pes and not insert a new record on u_business_support (gr2)