JOIN records from two tables

[Panagiotis Kons]

Hello all,

I have a table in ServiceNow, called "tasks". In this table I have a Reference field to another table called "workers". In the "tasks" list I can see the name of the worker who is doing the task. I am trying to implement a script that queries a specific task and displays the number of tasks for a specific worker. What I have done so far is the following

var tasks = new GlideRecord("tasks");
tasks.addQuery("number","12345");
tasks.addJoinQuery("workers", "name", "Bob");
tasks.query();

gs.info('******* tasks count: ' + tasks.getRowCount());

I have two workers, and for the task with number 12345, Alice has 5 tasks and Bob does not have any. However the query returns 5 for both Alice and Bob.

Any ideas what I am doing wrong?

Thank you

Panos

[Rene El Hamzh · Accepted solution]

Hi @Panagiotis Kons,

you could achieve this by using a GlideAggregate with an orderBy. Update with your reference field name and try this: 

var tasks = new GlideAggregate("tasks");
tasks.addAggregate("COUNT");
tasks.groupBy("worker_field"); 
tasks.query();
while (tasks.next()) {
    gs.info(tasks.getDisplayValue("worker_field") + " " + tasks.getAggregate("COUNT"));
}

[Adrian Ubeda · Accepted solution]

Hello, 

You can use 'addQuery' statement as a normal GlideRecord, check this useful blog related to GlideAggregate and how it work (there are so many different examples that are similar to your case): https://developer.servicenow.com/blog.do?p=/post/glideaggregate/

[Rene El Hamzh · Accepted solution]

Hi @Panagiotis Kons,

you could achieve this by using a GlideAggregate with an orderBy. Update with your reference field name and try this: 

var tasks = new GlideAggregate("tasks");
tasks.addAggregate("COUNT");
tasks.groupBy("worker_field"); 
tasks.query();
while (tasks.next()) {
    gs.info(tasks.getDisplayValue("worker_field") + " " + tasks.getAggregate("COUNT"));
}

[Panagiotis Kons]

Hello Rene, 

thank you for your quick reply. How do I pass the worker name in the aggregate?

Regards

Panos

[Adrian Ubeda · Accepted solution]

Hello, 

You can use 'addQuery' statement as a normal GlideRecord, check this useful blog related to GlideAggregate and how it work (there are so many different examples that are similar to your case): https://developer.servicenow.com/blog.do?p=/post/glideaggregate/

[Rene El Hamzh · Accepted solution]

If it's only for a specific worker then we don't need the groupBy and can just add an addQuery.

var tasks = new GlideAggregate("tasks");
tasks.addAggregate("COUNT");
tasks.addQuery("worker_field", "worker_sys_id"); 
tasks.query();
if (tasks.next()) {
    gs.info(tasks.getAggregate("COUNT"));
}