How to group by an attribute and print values using background script

[Surbhi Srivasta]

Hi,

I have a table where I have 2 columns named Directory and Roles. Now Directory can be same on multiple records, I need help to print out the value of all Roles for the same directory in 1 single line.

For example, this is how my table is structured:

There are 2 D1 which are having 2 different Roles R1 and R3. Similarly for D2, there are 2 roles R2 and R4

Output: 

R1,R3
R2,R4

I have tried below script so far to group by using glide aggregate but this is not working. Both Directory and Roles are reference attributes

var gr = new GlideAggregate('tableName');
gr.addEncodedQuery('my query');
gr.groupBy('directory');
//gr.setGroup(false);
gr.query();
while(gr.next()){
	gs.info(gr.getValue('roles'));
}

Kindly assist on how can i achieve the above output.

Regards,

Surbhi

[KrishnaMohan]

Hi @Surbhi Srivasta 

please try below code and let us know.

var gr = new GlideAggregate('tableName');
gr.addEncodedQuery('my query');
gr.groupBy('directory');
gr.query();
while(gr.next()){
	var getRoles = new GlideRecord("roles_table_name");
        getRoles.addQuery("directory", gr.getValue('directory'));
       getRoles.query()
       while(getRoles.next()){
            gs.print(getRoles.getValue("roles"))
          }
}

 However, I believe we can do it using join query as mentioned below

Note : join query records works based on a relationship in a related table 

var now_GR = new GlideRecord('roles'); 
now_GR.addJoinQuery('dictionary'); 
now_GR.query();
while(now_GR.next()){
     gs.print(now_GR.getValue("role"))
}

To know more about Join Query, Please refer below docs link

https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server_legacy/c_GlideRecordAPI#r_Glid... 

If this helps, please mark this as correct/helpful.

Regards,

Krishnamohan

[Surbhi Srivasta]

Hi @KrishnaMohan ,

Tries the script above, it prints blank and no output is displayed. Below is the script I tried:

var gr = new GlideAggregate('table name');
gr.addEncodedQuery('Query here');
gr.groupBy('directory');
gr.query();
while(gr.next()){
	var getRoles = new GlideRecord("Role Table Name");
        getRoles.addQuery("sys_id", gr.getValue('role'));
        getRoles.query();
        while(getRoles.next()){
            gs.print(getRoles.getValue("role"))
          }
}

Can you please assist

[Surbhi Srivasta]

Also checked if I comment out group by line it prints the output with different roles available. But that is what not I am trying to achieve.

If there are similar directory name available as different rows in my M2M table then it should print different roles against the same directory available in 1 line and rest in other line. 

Similar to screenshot I have attached above.

[Dharma Liyanag]

Hi Surbhi Srivasta,

You can try with simple script without array, split, push ...etc

var gr1 = new GlideAggregate('incident');    // incident table used to test -  

gr1.groupBy('short_description');

//gr1.setLimit(50);

gr1.query();

while(gr1.next()) {

    var gr2 = new GlideRecord('incident'); // same table

    gr2.addQuery('short_description', gr1.short_description);//field name

    gr2.query();

    var rec_cnt = gr2.getRowCount();   // get the cnt;

    if (rec_cnt > 1) {            

        while(gr2.next())

            gs.info(gr2.number + "- " + gr2.short_description);

    }

}

I go this results: Just created few incidents with those short_descriptions to test.

*** Script: INC0000009- Reset my password
*** Script: INC0000035- Reset my password
*** Script: INC0010004- test
*** Script: INC0010020- test
*** Script: INC0009001- test2
*** Script: INC0009005- test2