CODEWITHSUNDEEP
javascriptservicenowservicenow-rest-apiservicenow-hrsd
SE.Edward
SE.Edward

Reputation: 201

How to group by in service now?

I am new in service now and I don't know yet how to manipulate the data unlike in MySQL and SQL. I just want to know how can I group by the data.

Here is my code to show the data:

var group = new GlideRecord("table");
    
group.addEncodedQuery('u_active=True');
group.orderBy('u_order_in_services');
group.query();
            
while (group.next()) {
gs.info(group.group_name);  

}

The result is:

Group 1
Group 1
Group 2 
Group 2
Group 3
Group 3

Needed result is:

Group 1
Group 2
Group 3

Upvotes: 0

Views: 5931

Answers (3)

Joey Day
Joey Day

Reputation: 212

With either GlideQuery or GlideAggregate, to get the result you want you should use the groupBy method. It's hard to tell precisely what you're doing since you didn't give us the actual table name, but, following your example, the code should look like this:

new GlideQuery('table')
  .where('u_active', true)
  .groupBy('group_name')
  .aggregate('count')
  .select()
  .map(function (record) { return record.group.group_name })
  .forEach(GQ.debug);

A comparable GlideAggregate example would look like this:

var ga = GlideAggregate('table');
ga.addQuery('u_active', true);
ga.groupBy('group_name');
ga.addAggregate('COUNT')
ga.query();

while (ga.next()) {
  gs.debug(ga.getValue('group_name'));
}

Note, I removed the orderBy clause of both these queries since it makes less sense when we're grouping the results. Also, if this really is a custom table, I would triple-check that the group_name field isn't actually named u_group_name. If so, you'll need to update my examples to work properly.

Upvotes: 0

ismrs
ismrs

Reputation: 11

You could use GlideQuery for this, example:

var group = new global.GlideQuery('sys_user_group')
    .where('active', true)
    .orderBy('name')
    .select('name')
    .toArray(100); 

GQ.jsonDebug(group)

GlideQuery API Documentation

Upvotes: 0

giles3
giles3

Reputation: 487

Use GlideAggregate (instead of of GlideRecord) if you need a GROUP BY.

There is an introduction here:

Upvotes: 1

Related Questions