Restricting a related list

[Brian Broadhurs]

I have a related list on the Incident form that shows all the active incidents for the same company. This is for an MSP, and the list can be very long. I currently restrict the list to those created within the last 7 days, but I would like to restrict it to "the most recent 10". I have a Relationship defined which defines the query, but I can't find a way of saying that I want the 10 most recent. Can anybody help?

Brian Broadhurst

[kcaldwell]

You could create a Global Business Rule which will put the sys_ids of the most recent 10 into a Array and then use the function in your conditions.

Example:
Look at the Business Rule: getMyApprovals
The function "getMyApprovals" in the Business Rule is used in the condition for the 'Service Desk' Application > 'My Approvals' Module.

[john_roberts]

Here's how to do it with a standard GlideRecord query. I'm not sure if it works in a relationship query.

var gr = new GlideRecord('incident');
gr.orderByDesc('sys_created_on');
gr.setLimit(10);
gr.query();

[Brian Broadhurs]

Thanks for the feedback on this subject. My colleague at TeamUltra, Nigel Bell, developed the following script to go in the Query With field of the Relationship:

// Specify the limit on the number of records we want
var count = 10;

/* Do our own search to find them, remembering to exclude the parent
and setting the limit based on the number above */
var recs = new GlideRecord('incident');
recs.addQuery('company', parent.company);
recs.addQuery('active', true);
recs.addQuery('sys_id','!=',parent.sys_id);
recs.orderByDesc('opened_at');
recs.setLimit(count);
recs.query();

/* If we've found some records add them all to the query for the
related list */
if ( recs.hasNext() ) {
recs.next();
qc = current.addQuery('sys_id',recs.sys_id);

while ( recs.next() ) {
qc.addOrCondition('sys_id',recs.sys_id);
}
/* Otherwise add a query that will never find anything to make sure
we get an empty list instead of every record */
} else {
current.addQuery('sys_id','0');
}

[JayGervais]

This really helped me out! Thanks for posting this. I did this with related items:

(function refineQuery(current, parent) {

	// Add your code here, such as current.addQuery(field, value);
	
	var item = new GlideRecord('sc_req_item');
	item.addQuery('request.requested_for', parent.caller_id);
	item.orderByDesc('sys_created_on');
	item.setLimit(10);
	item.query();
	
	if (item.hasNext()) {
		item.next();
		var list = current.addQuery('sys_id', item.sys_id);
		
		while (item.next()) {
			list.addOrCondition('sys_id', item.sys_id);
		}
	} else {
		current.addQuery('sys_id', '0');
	}

})(current, parent);