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

[Ankur Bawiskar · Accepted solution]

@Surbhi Srivasta 

try this

var gaUser = new GlideAggregate('tableName');
gaUser.addAggregate('count');
gaUser.orderByAggregate('count');
gaUser.groupBy('directoryField'); 
gaUser.addEncodedQuery('my query');
gaUser.query();
while(gaUser.next()){
var roleCount = gaUser.getAggregate('count');
gs.info('Directory: ' + gaUser.directoryField + ' has roles : ' + roleCount);
}

I used something similar on sys_user and it worked for me

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

[Ankur Bawiskar]

@Surbhi Srivasta 

Thank you for marking my response as helpful.

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

[Surbhi Srivasta]

Hello @Ankur Bawiskar ,

Thanks for the above response. I tired and your script helps me display the count of roles which I have for each directory.

I was looking for to display the names of those roles comma separated against each directory. Currently as per you script where you helped me it's just showing me the count of those.

Can you please help me on this.

[Surbhi Srivasta]

@Ankur Bawiskar 

Any thoughts on above if it can be achieved. Is there an alternative solution you can assist to achieve the same?

[Ankur Bawiskar]

@Surbhi Srivasta 

for that you can enhance the script further.

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