BackGround Script

[Annette Kitzmil]

Hi All, 

I am hoping someone can help me with my feeble attempts in creating a background script.  I started thinking my information was going to have to come from 2 different tables, but then discovered that we have an m2m table that contains the case records along with the reason records.  I need to show all duplicates based on the question, then the records that contain the question with a specific sys_id needs to be updated to a different question with a different sys_id so we can delete the three questions with the sys_id that are not the right ones for those cases.  Below is a screen shot showing the table and conditions that will need to be used and I would like gs.info included to show the records that will get this update.

The script below is my feeble attemp of a script that works and I would like to see the record ids if possible.  Thank you ahead of time for any help on this script.

var dup = new GlideAggregate('x_mtbr_ebs_shareit_shareit_decision_reasons'); //table

dup.groupBy('reason'); //field name

dup.query();

while(dup.next()) {

    var dup1 = new GlideRecord('x_mtbr_ebs_shareit_shareit_decision_reasons'); //table

    dup1.addQuery('reason', dup.reason);//field name

    dup1.query();

    dup1.next();

    while(dup1.next())

        gs.info(dup1);

}

[Shane J]

Unless there's some reason I'm missing, you don't need to use GlideAggregate,

And I don't understand what this means:  "I need to show all duplicates based on the question, then the records that contain the question with a specific sys_id needs to be updated to a different question with a different sys_id so we can delete the three questions with the sys_id that are not the right ones for those cases. "

Are you simply saying that a Reference field needs to be updated from one sys_id to another?  You don't mean you actually want to change the sys_id of a record, right?

[Annette Kitzmil]

Hi Shane,

So I have an m2m table referencing two other tables.  I agree the GlideAggregate isn't correct, it is what I had at the time.  

So, what I am saying is I have a reason with sys id - 

83fe5364c31df5d03646468dc001315d - rights of survivorship question

There are only a handful of cases that have been opened where a user may have selected the reason above with that sys id.  So, I first need to identify any records that have that sys_id reason.  Then only on those cases will I update the field (reason) to match this sys id 

0258c3a01b454d10b1082060604bcbeb - rights of survivorship question instead.  

Hopefully this answered you question more clearly and this is probably a closer version with comments of my script:

//this provides complete table access in the "gr" object

var gr = new GlideRecord('x_mtbr_ebs_shareit_shareit_decision_reasons'); //table

//Filter for the state of vermont; paired as field name and value

gr.addQuery('shareit_cases', 'Vermont');

//Filter for the specific reason(s); paired as field name and question

gr.addQuery('reason', sys_id = '83fe5364c31df5d03646468dc001315d');//83fe5364c31df5d03646468dc001315d - rights of survivorship question

gr.addQuery('reason', sys_id = '9dbe1f64c31df5d03646468dc00131b9'); //9dbe1f64c31df5d03646468dc00131b9 - beneficiary designations question

gr.addQuery('reason', sys_id = 'b4432a3dc3e1b1d03646468dc001313d');//b4432a3dc3e1b1d03646468dc001313d - our determination question

//Then execute filter(s) or only return the record with the conditions listed in the queries

gr.query();

//Display records on screen that meet these conditions

gs.info();

/*Now we will iterate over the data and implement the business logic which is to replace the decision reasons above

with the same decision reason, but one with a different sys_id*/

while (gr.next()) {

if(gr.next()) {

    var reasonOne = sys_id = '83fe5364c31df5d03646468dc001315d'; //2023 decison reason sys_id

    gr.setValue('shareit_decision_reasons.reason', sys_id = '0258c3a01b454d10b1082060604bcbeb');//2022 decision reason sys_id

    gr.update();

} else if {

    var reasonTwo = sys_id = 'b4432a3dc3e1b1d03646468dc001313d';//2023 decison reason sys_id

    gr.setValue('shareit_decision_reasons.reason', sys_id = 'b24847601b454d10b1082060604bcb73');//2022 decision reason sys_id

    gr.update();

} else {

    var reasonThree = sys_id = '9dbe1f64c31df5d03646468dc00131b9';//2023 decison reason sys_id

    gr.setValue('shareit_decision_reasons.reason', sys_id = '635843601b454d10b1082060604bcbd1');//2022 decision reason sys_id

    gr.update();

}

}

[Shane J · Accepted solution]

OK Annette, a couple things here.

First, I've started creating my background script as Fix Script instead.  That way you don't lose the transitory nature of a background script, and also get the bonuses of the script editor.

Second, you should always try to put script like this within their own function (just in case).  I took your script and revamped it how I think you intended it to be.  Note that if you run this as is, and records are found - you are going to update the records!  You can // (ie comment) out the gr.update() to prevent that from happening though, and just have it 'print' the results to you to verify.

AnnetteKBGScript();

function AnnetteKBGScript() {

    var gr = new GlideRecord('x_mtbr_ebs_shareit_shareit_decision_reasons'); //table
    //Filter for the state of vermont; paired as field name and value
    gr.addQuery('shareit_cases', 'Vermont');
    //Filter for the specific reason(s); paired as field name and question
    gr.addQuery('reason', '83fe5364c31df5d03646468dc001315d').addOrCondition('reason', '9dbe1f64c31df5d03646468dc00131b9').addOrCondition('reason', 'b4432a3dc3e1b1d03646468dc001313d');
    //83fe5364c31df5d03646468dc001315d - rights of survivorship question
    //9dbe1f64c31df5d03646468dc00131b9 - beneficiary designations question
    //b4432a3dc3e1b1d03646468dc001313d - our determination question
    //Then execute filter(s) or only return the record with the conditions listed in the queries
    gr.query();
    /*Now we will iterate over the data and implement the business logic which is to replace the decision reasons above
    with the same decision reason, but one with a different sys_id*/
    while (gr.next()) {
        gs.print('Record sys_id: ' + gr.sys_id); //Display records on screen that meet these conditions
        if (gr.reason == '83fe5364c31df5d03646468dc001315d') { //2023 decison reason sys_id
			gs.print('Record sys_id: ' + gr.sys_id + ' - rights of survivorship question');
            gr.setValue('shareit_decision_reasons.reason', '0258c3a01b454d10b1082060604bcbeb'); //2022 decision reason sys_id
        } else if (gr.reason == 'b4432a3dc3e1b1d03646468dc001313d') { //2023 decison reason sys_id
            gs.print('Record sys_id: ' + gr.sys_id + ' - beneficiary designations question');
			gr.setValue('shareit_decision_reasons.reason', 'b24847601b454d10b1082060604bcb73'); //2022 decision reason sys_id
        } else if (gr.reason == '9dbe1f64c31df5d03646468dc00131b9') { //2023 decison reason sys_id
            gs.print('Record sys_id: ' + gr.sys_id + ' - our determination question');
			gr.setValue('shareit_decision_reasons.reason', '635843601b454d10b1082060604bcbd1'); //2022 decision reason sys_id
        }
        gr.update();
    }
}

[Bert_c1]

As stated by Shane J above, use for GlideAggregate is not appropriate in you script. 

https://developer.servicenow.com/dev.do#!/reference/api/utah/server_legacy/c_GlideAggregateAPI

Here's a script that may help in what you are trying to do, I used a m2m table in my instance that has records.  PA dashboards can have the same Tabs

var dup = new GlideRecord('pa_m2m_dashboard_tabs'); //table
//dup.orderBy('dashboard'); //field name
dup.groupBy('dashboard'); //field name
dup.query();
while(dup.next()) {
	var dup1 = new GlideRecord('pa_m2m_dashboard_tabs'); //table
	dup1.addQuery('tab', dup.tab);//field name
	dup1.query();
	var dupCount = dup1.getRowCount();
	if (dupCount > 1) {
                gs.info("Dashboard = " + dup.dashboard.getDisplayValue() + ", tab = " + dup.tab.getDisplayValue());
		gs.info("Found " + dup1.getRowCount() + " records for tab: " + dup.tab.getDisplayValue());
		while(dup1.next())
			gs.info(dup1.dashboard.getDisplayValue() + ": " + dup1.tab.getDisplayValue());
	}
}

Change table and field name to match what you are looking for.