How to write a script to change value of a field based on another field on a table

[Sri63]

Hi All,

I am new to SNow and need some help from experts like you all.

So I have a table with fields and I would like to change the value of a field based on other field value

EG:

In the above table I am looking for

1. For table "table_123".
2. I want to search "NAME" column which ends with [-Test / -UAT / -dr]  for ID [ ID001 ]
3. Once a match is found from this search  
4. I want to change the value of "ACTIVE" of those matched records from "true" --> "false"

Can some one help me on how to achieve this 

[Brent Sutton · Accepted solution]

Hi Srini,

You can use the following in a fix script to correct your records:

While loop example:

var query = "nameENDSWITH-Test^ORnameENDSWITH-UAT^ORnameENDSWITH-dr^id=ID001^active=true"; //parameters for your query

var gr = new GlideRecord("table_123"); //your table
gr.addEncodedQuery(query); //add encoded query
gr.query(); //commence query

//loop through the returned records
while (gr.next()) {
	gr.setValue("active",false); //set active to false
	gr.update(); //update record.
}

For extra speed you could also use updateMultiple()

updateMultiple example:

var query = "nameENDSWITH-Test^ORnameENDSWITH-UAT^ORnameENDSWITH-dr^id=ID001^active=true"; //parameters for your query

var gr = new GlideRecord("table_123"); //your table
gr.addEncodedQuery(query); //add encoded query
gr.query(); //commence query

//for extra speed you can use updateMultiple to update all records at once rather than looping through them.
gr.setValue("active",false); //set active to false
gr.updateMultiple(); //update all returned records as inactive.

Let me know how you get along.

Brent

P.S. If my suggestion helped then please mark as helpful and/or correct so other community members can benefit from this information.

[codycotulla]

Hi Srini,

Here is a version using the addOrCondition.

var gr = new GlideRecord("table_123");
var qc = gr.addQuery("name","CONTAINS" "-Test");
 qc.addOrCondition("name","CONTAINS" "-UAT");
qc.addOrCondition("name","CONTAINS" "-dr");
gr.query();
while (gr.next()) {
	gr.active = false;
	// field name is probably u_active
	// gr.u_active = false;
	gr.update();
 
}

Note a cool thing in the editor, 

type help and then hit tab and your will see a list of macros that you can use in your scripts.

The Syntax Editor macros are:
-----------------------------
doc - Documentation Header
for - Standard loop for arrays
vargror - Example GlideRecord Or Query
info -
method - Standard JavaScript Class Method
vargr - A common pattern of creating and querying a GlideRecord

Thanks,

Cody

[Sri63]

Hi Cody,

Thanks for the response. 

Your result also works fine like Brent who replied earlier to this post. 

Any ways I would consider as an working alternative declaration of query.

Thank You 

[puneetgoels1]

use reference qualifier