GlideRecord Counting by Parent / Child Records

TCole94 · ‎02-24-2025

I have to show print an html email table with that counts each GlideRecord in primary/subprimary order and number them in a column. For example,

There are 10 incidents.

2 incident records have 4 child incidents. So ideally, the count would look like this (below):

1 = Incident Parent xxx

1.1 = Child Incident of Parent xxx

1.2 = Child Incident of Parent xxx

1.3 = Child Incident of Parent xxx

1.4 = Child Incident of Parent xxx

2 = Incident Parent xyz

2.1 = Child Incident of Parent xyz

2.2 = Child Incident of Parent xyz

2.3 = Child Incident of Parent xyz

Any idea of how to do this?

SakshamSahu · ‎02-24-2025

Hi @TCole94 ,

You can write a script something similar to this:

var emailBody = '<html><body>';
emailBody += '<h2>Incident Hierarchy Report</h2>';
emailBody += '<table border="1" style="border-collapse: collapse;">';
emailBody += '<tr><th>#</th><th>Incident Number</th><th>Short Description</th></tr>';

// Query all Parent Incidents (incidents that don't have a parent)
var parentGR = new GlideRecord('incident');
parentGR.addNullQuery('parent'); // Only get parent incidents
parentGR.orderBy('sys_created_on'); // Sort by creation date
parentGR.query();

var parentCount = 0;

while (parentGR.next()) {
    parentCount++;

    // Set dynamic Short Description for Parent
    var parentDesc = "Parent Incident";

    // Print Parent Incident
    emailBody += '<tr style="font-weight: bold;">';
    emailBody += '<td>' + parentCount + '</td>';
    emailBody += '<td>' + parentGR.number + '</td>';
    emailBody += '<td>' + parentDesc + '</td>';
    emailBody += '</tr>';

    // Query Child Incidents (linked to this parent)
    var childGR = new GlideRecord('incident');
    childGR.addQuery('parent', parentGR.sys_id);
    childGR.orderBy('sys_created_on'); // Sort by creation date
    childGR.query();

    var childCount = 0;

    while (childGR.next()) {
        childCount++;

        // Set dynamic Short Description for Child
        var childDesc = "Child of " + parentGR.number;

        // Print Child Incident with sub-numbering (e.g., 1.1, 1.2)
        emailBody += '<tr>';
        emailBody += '<td>' + parentCount + '.' + childCount + '</td>';
        emailBody += '<td>' + childGR.number + '</td>';
        emailBody += '<td>' + childDesc + '</td>';
        emailBody += '</tr>';
    }
}

emailBody += '</table>';
emailBody += '</body></html>';

// Send Email or Use in Notification

// gs.eventQueue('incident.report.email', current, emailBody, '');