I want to print a table in notification that contains list of tickets raised by a user.

[abhaysingh98]

Hello All,

I want to print a table in a notification that will be sent every month to submitted by user and that notification includes list of tickets that are raised by the user. I have created a mail script that is used to fetch the details of tickets I am able to get the ticket number and it state in table format but now I want that number to be a type of link and the list will sort in ascending or descending order based on ticket created date.

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,

    /* Optional EmailOutbound */

    email, /* Optional GlideRecord */ email_action,

    /* Optional GlideRecord */

    event) {

    // Add your code here

    var arr = [];

    var gr = new GlideRecord('incident');

    gr.addEncodedQuery('active=true^stateIN1,2^activity_leaderDYNAMIC90d1921e5f510100a9ad2572f2b477fe');

    gr.query();

    while (gr.next()) {

        arr.push(gr.number.toString());

    }

    var rows='';

    for (var i = 0; i < arr.length; i++) {

        var sr = new GlideRecord('incident');

        sr.addQuery('number', arr[i]);

        sr.query();

        while (sr.next()) {

            rows += '<tr>';

            rows += '<td>' + sr.number + '</td>';

            rows += '<td>' + sr.getDisplayValue('state') + '</td>';

            rows += '</tr>';

        }

    }

    var data='';

    data = '<table style="border-collapse: collapse;" border="1" cellpadding="5"><tbody>';

    data += '<tr><th>Name</th><th>state</th></tr>';

    data += rows;

    data += '</tbody></table>';

    template.print(data);

})(current, template, email, email_action, event);

[Bhavya11 · Accepted solution]

Hi @abhaysingh98 ,

could you please try below script

(function (current, /* TemplatePrinter */ template,
/* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
/* Optional GlideRecord */ event) {

// Array to store incident numbers
var arr = [];

// Query active incidents with specified criteria
var gr = new GlideRecord('incident');
gr.addEncodedQuery('active=true^stateIN1,2^activity_leaderDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
gr.orderByDesc('sys_created_on') // add the sorting here
gr.query();
while (gr.next()) {
arr.push(gr.number.toString());
}

// Build rows with clickable URLs
var rows = '';
for (var i = 0; i < arr.length; i++) {
var sr = new GlideRecord('incident');
sr.addQuery('number', arr[i]);
sr.query();
while (sr.next()) {
// Construct the clickable URL
var url = gs.getProperty('glide.servlet.uri') + sr.getTableName() + ".do?sys_id=" + sr.sys_id;

// Add rows to the table
rows += '<tr>';
rows += '<td><a href="' + url + '">' + sr.number + '</a></td>'; // Clickable incident number
rows += '<td>' + sr.getDisplayValue('state') + '</td>';
rows += '</tr>';
}
}

// Build the final table with headers
var data = '';
data = '<table style="border-collapse: collapse;" border="1" cellpadding="5"><thead>';
data += '<tr><th>Incident Number</th><th>State</th></tr></thead><tbody>';
data += rows;
data += '</tbody></table>';

// Print the table in the email
template.print(data);

})(current, template, email, email_action, event);

If my response helped please mark it correct

Thanks,

BK

[abhaysingh98]

Hi @Bhavya11 , Thanks for you reply however, I already configured this but now I want to know is there any way I can schedule this notification to every month first monday.

[Bhavya11]

Hi @abhaysingh98 ,

you can create schedule job with conditions that check is today is first Monday of month  like below 

function checkmonday() {
var gdt = new GlideDateTime();
var day = gdt.getDayOfWeek();
var result = false;
 if (day == 1 && gdt.getDayOfMonth()<=7){
  result = true;
  }
 return result;
}

checkmonday();

to send notifications using Event Queue, in run script try like below 

gs.eventQueue("event_name", glideRecord object, parm1 ( sys_id of user whom you want to send, parm2 (optional));

and change your notification send when to "Event is fired"

Thanks,

BK 

[abhaysingh98]

Do I need to run it daily or monthly and also I want to send this notification to a field on record i.e. opened_by can I set it directly on notification or I need to pass something in gs.eventQueue.