Using .addJoinQuery() & How to Query Records with Attachments in ServiceNow

Here’s a common question:

“How do I use a scripted GlideRecord query to get all records in a given table that have attachments?”

Often, people in the Community Forums will suggest querying the table, iterating through every single record, running .hasAttachment() on every single one, and adding those records to a list if that returns true.

Or, they might suggest querying the sys_attachment table, querying every attachment associated with the target table, pushing every unique value from the attachment record's table_sys_id field into an array, then using that array to query the target table with something like grMyRecord.addQuery('sys_id', 'IN', arrRecordsWithAttachments.join(','));.

However, these solutions are quite slow and not exactly ideal. Instead, consider the use of GlideRecord's .addJoinQuery() method.

Consider the following code:

logIncidentsWithAttachments();

function logIncidentsWithAttachments() {
    var msg = '';
    var grInc = new GlideRecord('incident');
    grInc.addActiveQuery();
    grInc.addJoinQuery(
        'sys_attachment', //Target table
        'sys_id', //Incident field to "join" on
        'table_sys_id' //Target table field to "join" with the Incident field on
    );
    //grInc.setLimit(10);
    grInc.query();
    while (grInc.next()) {
        msg += (
            grInc.getDisplayValue() + ' has attachments.\n' +
            'Link: ' + gs.getProperty('glide.servlet.uri') + grInc.getLink() +
            '\n\n'
        );
    }
    gs.info(msg.trim());
};

This code will log some messages that look like this:

INC0000031 has attachments. Link: https://INSTANCE.service-now.com/incident.do?sys_id=47064b68a9fe19810186793eefffc9b7&sysparm_stack=incident_list.do?sysparm_query=active=true

The down-side to this approach is that the query is not actually performing a true database join operation. Instead, it's effectively querying both tables and linking them together. This means that you won't actually have access to any fields on the joined table (in this case, Attachment [sys_attachment]).

If you need access to fields on the attachment table, then you're probably going to need to query it directly. You could also potentially create a Database View to accomplish something similar and have access to fields from both tables in a report, but (1) database views can't be queried using a GlideRecord script in the same way a "real" table can; and (2) database views can be very performance-intensive, so do this at your own risk!

Follow Tim on LinkedIn