How can I get a count from unique values?

[robhaas]

This may get a bit confusing so I will do my best to explain.

I have Table A and Table B. Table B contains children of objects in Table A (parent). Table A has a field called receivers which are comma separated userids. Items from A and B are assigned to different people.

I am needing to query all items from B where they are assigned to a specific person, and count the receivers from the corresponding parent for each.

There will always be the same number of receivers for each child of a single parent.

Example:

A=Parent

B=Children

A1 > Receivers = rcv1, rcv2, rcv3

A2 > Receivers = rcv1

A3 > Receivers = rcv2

B1 > Parent = A1 - Assigned to U1

B2 > Parent = A1 - Assigned to U1

B3 > Parent = A1 - Assigned to U1

B4 > Parent = A1 - Assigned to U2

B5 > Parent = A3

(Current) I want to query table B where the assigned to is U1, and count the receivers   from the parent A. If I were to do this now, I would get a total of 9 receivers because 3 children have the same parent, which has 3 receivers, ergo, 9 total. I need to find the distinct parent, in this case is A1, and count the receivers for only it.

(Need) New query - table B where assigned to is U1, realizing there are 3 children with the same parent, we choose 1 of them as they all point to the same number of receivers on the parent. Thus the query would provide a value of 3.

The code I have below is what I currently have which provides duplicate values:

var person = 'sys_id_here';
var inc = new GlideRecord('incident');
inc.addQuery('closed_at', '>=', gs.beginningOfThisMonth());
inc.addQuery('closed_at', '<=', gs.endOfThisMonth());
inc.addQuery('assigned_to', person);
inc.groupBy('parent');
inc.query();
var count = 0;
var rcvtotal = 0;
while (inc.next()) {
   var receivers = inc.parent.receiver_ids.toString();
   receivers = receivers.split(",");
   count = receivers.length;
   gs.print(inc.parent.number + ' : ' + count);
   rcvtotal = rcvtotal + count;
   var tot = rcvtotal;
}
gs.print('Total is: ' + tot);

Any thought on how I can do this?

[Chuck Tomasi · Accepted solution]

Hi Robert,

Take a look at this page and search for "distinct".

GlideAggregate - ServiceNow Wiki

[robhaas]

Yeah, I've looked at that, but I'm not sure how to aggregate correctly to get the data I need. I'm struggling with the code right now.

[robhaas]

Ok, I had to take a step back and start fresh. I did a GlideAggregate distinct on the parent. That gave me a unique list of items from table A. I can simply add the rest of my previous query to count the receivers. Thanks Chuck!

[johnram]

The ServiceNow Wiki content is no longer supported. Updated information about this topic is located here: GlideAggregate

Visit http://docs.servicenow.com for the latest product documentation