Script to add users in various tables to a group for role assignment

[ClareM]

Hi,

I'm trying to run the following script to add users that are in various columns in various tables to a group - it is returning approver sys_id's but it's failing at pushing these into an array and only returning the last value for approvers.

I've seen articles about ensuring the variable type is string before using .push method and I've tried using .toString() but  it's still not working.

Can anyone spot where I'm going wrong or what else I could try? thanks

var tableNames = 'table1,table2,table3';

var tables = tableNames.split(','); //split up the column names

for(var i = 0; i < tables.length; i++) {

var table = tables[i];

var columnNames = '';//TARGET COLUMNS

if (table == 'table1'){

columnNames = 'column1,column2,column3';

}

else if (table == 'table2'){

columnNames = 'column4,column5,column6';

}

else if (table == 'table3'){

columnNames = 'column7,column8,column9';

}

var columns = columnNames.split(','); //split up the column names

for(var x = 0; x < columns.length; x++) {

var column = columns[x];

//Get the Unique Approvers from each column in the table

var gA = new GlideAggregate(table);

gA.addQuery('u_active', true);

gA.addQuery([column],'!=',"");

gA.addNotNullQuery([column])

gA.groupBy([column]);

gA.query();

while(gA.next()) {

// Check if the user exists in the group member table

var grGroup = new GlideRecord('sys_user_grmember');

grGroup.addQuery('group', 'sys_id of group');

grGroup.addQuery('user', gA[column]);

if(!grGroup.hasNext()){

var approver = gA[column];

//var approverString = approver.toString();

//var approver = gA[column].toString();

//gs.print('approver: ' + approver);

var approvers = [];

approvers.push(approver);

}

}

}

}

gs.print('approvers: ' + approvers);

var uniqueApproversArray = new ArrayUtil();

var uniqueApprovers = uniqueApproversArray.unique(approvers.toString().split(','));

var approversArrayStrings = uniqueApprovers.toString().split(',');

//gs.print('approversArrayStrings: ' + approversArrayStrings)

for(var y = 0; y < approversArrayStrings.length; y++) {

var uniqueApprover = approversArrayStrings[y];

var grGroupAdd = new GlideRecord('sys_user_grmember');

grGroupAdd.initialize();

grGroupAdd.group = 'sys_id of group'; //

grGroupAdd.user = uniqueApprover;

//gs.print('uniqueApprover: ' + uniqueApprover);

//grGroupAdd.insert()

}

[Ankur Bawiskar]

@ClareM 

update this line and it will push all values

approvers.push(approver.toString());

If my response helped please mark it correct and close the thread so that it benefits future readers.

[ClareM]

Thanks for your reply, I've tried that but still just getting the one value

[Ankur Bawiskar · Accepted solution]

@ClareM 

try this -> declare the approvers array outside

var tableNames = 'table1,table2,table3';

var approvers = [];

var tables = tableNames.split(','); //split up the column names

for (var i = 0; i < tables.length; i++) {

    var table = tables[i];

    var columnNames = ''; //TARGET COLUMNS

    if (table == 'table1') {

        columnNames = 'column1,column2,column3';

    } else if (table == 'table2') {

        columnNames = 'column4,column5,column6';

    } else if (table == 'table3') {

        columnNames = 'column7,column8,column9';

    }

    var columns = columnNames.split(','); //split up the column names

    for (var x = 0; x < columns.length; x++) {

        var column = columns[x];

        //Get the Unique Approvers from each column in the table

        var gA = new GlideAggregate(table);

        gA.addQuery('u_active', true);

        gA.addQuery([column], '!=', "");

        gA.addNotNullQuery([column])

        gA.groupBy([column]);

        gA.query();

        while (gA.next()) {

            // Check if the user exists in the group member table

            var grGroup = new GlideRecord('sys_user_grmember');

            grGroup.addQuery('group', 'sys_id of group');

            grGroup.addQuery('user', gA[column]);

            if (!grGroup.hasNext()) {

                var approver = gA[column];

                //var approverString = approver.toString();

                //var approver = gA[column].toString();

                //gs.print('approver: ' + approver);

                approvers.push(approver.toString());

            }

        }

    }

}

gs.print('approvers: ' + approvers);

var uniqueApproversArray = new ArrayUtil();

var uniqueApprovers = uniqueApproversArray.unique(approvers.toString().split(','));

var approversArrayStrings = uniqueApprovers.toString().split(',');

//gs.print('approversArrayStrings: ' + approversArrayStrings)

for (var y = 0; y < approversArrayStrings.length; y++) {

    var uniqueApprover = approversArrayStrings[y];

    var grGroupAdd = new GlideRecord('sys_user_grmember');

    grGroupAdd.initialize();

    grGroupAdd.group = 'sys_id of group'; //

    grGroupAdd.user = uniqueApprover;

    //gs.print('uniqueApprover: ' + uniqueApprover);

    //grGroupAdd.insert()

}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[ClareM]

That's it - thanks for your help!