ServiceNow Learning 63: Functionality of "addJoinQuery" in GlideRecord in ServiceNow

[Shamma Negi]

Hi All,

I have found very interesting function to join two table records together without having any database view created.

Please have a look below:

Adds a filter to return records based on a relationship in a related table.

For example, find all the users that are in the database group (users via sys_user_grmember table). Another example would be find all problems that have an assigned incident (problems via the incident.problem_id relationship).

 1. addJoinQuery(String table)

Example

Find problems that have an incident attached. This returns problems that have associated incidents. However, it won't pull values from the incidents that are returned as a part of the query.

var prob = new GlideRecord('problem');
prob.addJoinQuery('incident');
prob.query();

Example

Find active=false problems with associated incidents.

// Look for Problem records
var now_GR = new GlideRecord('problem');
 
// That have associated Incident records
var grSQ = now_GR.addJoinQuery('incident');
 
// Where the Problem records are "active=false"
now_GR.addQuery('active', 'false');
 
// And the Incident records are "active=true"
grSQ.addCondition('active', 'true');
 
// Query
now_GR.query();
 
// Iterate and print results
while (now_GR.next()) {
    gs.print(now_GR.getValue('number'));
}

 

2. addJoinQuery(String table, String primaryField)

Adds a filter to return records based on a relationship in a related table.

 

Example

Find problems that have incidents using the open_by field at the join key instead of the sys_id.

var now_GR = new GlideRecord('problem'); 
now_GR.addJoinQuery('incident', 'opened_by'); 
now_GR.query();

3. addJoinQuery(String table, String primaryField, String joinTableField)

Adds a filter to return records based on a relationship in a related table.

Example

Find problems that have incidents associated where the incident caller_id field value matches that of the problem opened_by field.

var now_GR = new GlideRecord('problem'); 
now_GR.addJoinQuery('incident', 'opened_by', 'caller_id'); 
now_GR.query();

Hope this helps.

I hope this article helpful. Please mark it as helpful and bookmark if you like it.

Regards,

Shamma