Defined Related Lists can be a very simple and useful tool to provide users with information in a related list directly on a form (even if that information is not directly associated with the record being viewed). The Service-now wiki contains documentation on this topic so I won’t cover that here. The point of this article is to point out something that often gets overlooked when working with Defined Related Lists and to share a few Defined Related Lists that I’ve used in the past.

One of the familiar defined related lists is the ‘Incidents by Same Caller’ list that you can display at the bottom of your incident form. The name says it all, but it looks something like this…

If you navigate to ‘System Definition -> Relationships’ and open up the ‘Incidents by Same Caller’ relationship record you’ll see that it uses the following query to produce the resultant related list…

current.addQuery('caller_id', parent.caller_id);

But what happens when we set the Caller field with an empty value?

The image above shows that you’ll get exactly what you’re supposed to get, but probably not what you want. Depending on your setup, there might not be a whole lot of value in showing a list of incidents with no caller…at least at the bottom of your incident form. The solution to this is to filter out any records where the field you are querying is empty. In this case, we want to filter out any records where the Caller field is empty like this…

current.addQuery('caller_id', parent.caller_id);
current.addNotNullQuery('caller_id');

Adding the ‘addNotNullQuery’ line gives us the result we would expect at the bottom of our incident form.

Here are a few other examples of defined related lists I have implemented in the past. Please share any defined related lists you have come up with by commenting on this post below!

Approval – Affected CIs
–Shows a list of Affected CIs for the task being approved at the bottom of an approval form.
Name: Affected CIs
Applies to table: Approval (sysapproval_approver)
Queries from table: CIs Affected (task_ci)
Query with:

current.addQuery('task', parent.sysapproval);
current.addNotNullQuery('task');
All Request Attachments
–Shows a list of attachments from the parent request, request item, and catalog task records on a catalog task form.
Name: All Attachments
Applies to table: Catalog task (sc_task)
Queries from table: Attachment (sys_attachment)
Query with:

var qc = current.addQuery('table_sys_id', parent.sys_id);
qc.addOrCondition('table_sys_id', parent.request_item.sys_id);
qc.addOrCondition('table_sys_id', parent.request_item.request.sys_id);
current.addNotNullQuery('table_sys_id');
All Request task and Approval Attachments
–Shows a list of attachments from the parent request, request item, catalog task, and approval records on a request item form.
Name: All Attachments
Applies to table: Requested item (sc_req_item)
Queries from table: Attachment (sys_attachment)
Query with:

//Current record Attachments
var qc = current.addQuery('table_sys_id', parent.sys_id);

//Request Attachments
qc.addOrCondition('table_sys_id', parent.request.sys_id);

//Catalog Task Attachments
var tsk = new GlideRecord('sc_task');
tsk.addQuery('request_item', parent.sys_id);
tsk.query();
var tskIDArr = [];
while(tsk.next()){
   tskIDArr.push(tsk.sys_id.toString());
}
var tskIDStr = tskIDArr.join();
qc.addOrCondition('table_sys_id', 'IN', tskIDStr);

//Approval Attachments
var app = new GlideRecord('sysapproval_approver');
app.addQuery('document_id', parent.sys_id);
app.query();
var appIDArr = [];
while(app.next()){
   appIDArr.push(app.sys_id.toString());
}
var appIDStr = appIDArr.join();
qc.addOrCondition('table_sys_id', 'IN', appIDStr);

//Do not include attachments not associated with a record
current.addNotNullQuery('table_sys_id');
Approval Task Attachments
–Shows a list of attachments from the associated approval task(s) on an approval form. In addition to displaying attachments for the specific task, this also shows attachments from the parent change request (for change tasks), the parent request item and request (for catalog tasks), the parent request (for catalog items), and the associated child items (for catalog requests).
Name: Approval Task Attachments
Applies to table: Approval (sysapproval_approver)
Queries from table: Attachment (sys_attachment)
Query with:

//Add attachments for the approval task
current.addNotNullQuery('table_sys_id');
var qc = current.addQuery('table_sys_id', parent.sysapproval);

//Add parent task attachments based on the approval task type
var appTask = '';
if(parent.sysapproval.sys_class_name){
   appTask = parent.sysapproval.sys_class_name;
}
if(appTask != ''){
var tsk = new GlideRecord(appTask);
tsk.get(parent.sysapproval);

//Check for approval task type and add necessary queries
if(appTask == 'change_task'){
   //Query for attachments from the associated change
   qc.addOrCondition('table_sys_id', tsk.change_request);
}
else if(appTask == 'sc_task'){
   //Query for attachments from the associated item and request
   qc.addOrCondition('table_sys_id', tsk.request_item);
   qc.addOrCondition('table_sys_id', tsk.request_item.request);
}
else if(appTask == 'sc_req_item'){
   //Query for attachments from the associated request
   qc.addOrCondition('table_sys_id', tsk.request);
}
else if(appTask == 'sc_request'){
   //Set up a variable to store item sys_ids
   var items = '';
   //Query for attachments from the associated request items
   var ritm = new GlideRecord('sc_req_item');
   ritm.addQuery('request', parent.sysapproval);
   ritm.query();
   while(ritm.next()){
      items = items + ',' + ritm.sys_id.toString();
   }
   if(items != ''){
      qc.addOrCondition('table_sys_id', 'IN', items);
   }
}
}