email script not working as expected

tghadage124 · ‎10-24-2024

Hi Guys ,

i have requirement where , I need to email in below given format where i am getting data from core_company table and it_purchase_requisition table , but company table values are getting set and for the other table for some fields sys_id is getting populated .

here is the email script :

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

/* Optional EmailOutbound */

email, /* Optional GlideRecord */ email_action,

/* Optional GlideRecord */

event) {

// Add your code here

var company = new GlideRecord('core_company');

company.get('sys_id', current.company);

var requisition = new GlideRecord('x_jade_procurement_it_purchase_requisition');

requisition.get('sys_id', current.sys_id);

var emailBody = "";

emailBody += "<h2>Company Information</h2>";

emailBody += "<p><strong>Company Name: </strong>" + company.name + "</p>";

emailBody += "<p><strong>Company Address: </strong>" + company.street + "</p>";

emailBody += "<p><strong>Phone: </strong>" + company.phone + "</p>";

emailBody += "<p><strong>City:</strong>" +company.city +"</p>";

emailBody += "<p><strong>Stock Price: </strong>"+company.stock_price+"</p>";

emailBody += "<h2>Requisition Details</h2>";

emailBody += "<table border='1' style='width:100%; border-collapse: collapse;'>";

emailBody += "<tr><th>Requisition Number</th><th>Requestor Name</th><th>Assigned To</th><th>Short Description</th><th>Delivery Location</th><th>Status</th><th>Description</th><th>Quantity</th><th>Department</th></tr>";

emailBody += "<tr>";

emailBody += "<td>" + requisition.requisition_no + "</td>";

emailBody += "<td>" + requisition.requester_name + "</td>";

emailBody += "<td>" + requisition.assigned_to + "</td>";

emailBody += "<td>" + requisition.assignment_group + "</td>";

emailBody += "<td>" + requisition.short_description + "</td>";

emailBody += "<td>" + requisition.status + "</td>";

emailBody += "<td> " + requisition.description + "</td>";

emailBody += "<td>"+ requisition.quantity +"</td>";

emailBody +="<td>"+requisition.department + "</td>";

emailBody += "</tr>";

emailBody += "</table>";

//email.setBody(emailBody);

template.print(emailBody);

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

here is the notification preview :

here is the notification :

please guide !!!

thanks

Brad Bowman · ‎10-24-2024

You can dot-walk to a field like

requisition.requester_name.name

or try

requisition.requester_name.getDisplayValue()

Voona Rohila · ‎10-24-2024

Hi @tghadage124

You don't need to query the 'x_jade_procurement_it_purchase_requisition' table with sys_id. You can use current in your email script.

Also, you've Missed adding Assignmnet group in the <th> part of your code.

For refernce fields like Assigned to/Assignment group the sys_id's will be populated, You can simply dot-walk to name.

Updated code:

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

    // Add your code here
    var company = new GlideRecord('core_company');
    if (company.get('sys_id', current.company)) {



        var emailBody = "";

        emailBody += "<h2>Company Information</h2>";
        emailBody += "<p><strong>Company Name: </strong>" + company.name + "</p>";
        emailBody += "<p><strong>Company Address: </strong>" + company.street + "</p>";
        emailBody += "<p><strong>Phone: </strong>" + company.phone + "</p>";
        emailBody += "<p><strong>City:</strong>" + company.city + "</p>";
        emailBody += "<p><strong>Stock Price: </strong>" + company.stock_price + "</p>";

        emailBody += "<h2>Requisition Details</h2>";
        emailBody += "<table border='1' style='width:100%; border-collapse: collapse;'>";
        emailBody += "<tr><th>Requisition Number</th><th>Requestor Name</th><th>Assigned To</th><th>Assignment Group</th><th>Short Description</th><th>Delivery Location</th><th>Status</th><th>Description</th><th>Quantity</th><th>Department</th></tr>";
        emailBody += "<tr>";
        emailBody += "<td>" + current.requisition_no + "</td>";
        emailBody += "<td>" + current.requester_name + "</td>";
        emailBody += "<td>" + current.assigned_to.name + "</td>";
        emailBody += "<td>" + current.assignment_group.name + "</td>";
        emailBody += "<td>" + current.short_description + "</td>";
        emailBody += "<td>" + current.status + "</td>";
        emailBody += "<td> " + current.description + "</td>";
        emailBody += "<td>" + current.quantity + "</td>";
        emailBody += "<td>" + current.department + "</td>";
        emailBody += "</tr>";
        emailBody += "</table>";


        //email.setBody(emailBody);
        template.print(emailBody);
    }
})(current, template, email, email_action, event);

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Rohila V
2022-25 ServiceNow Community MVP

tghadage124 · ‎10-27-2024

Hi @Voona Rohila ,

thanks your reply , that issue is resolved but here what i trying to do is add rows to the notifications based on the selected vendor_count. if two is selected then two rows will be populated with the given information.

here is the script :

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

/* Optional EmailOutbound */

email, /* Optional GlideRecord */ email_action,

/* Optional GlideRecord */

event) {

// Initialize the company and requisition GlideRecords

var company = new GlideRecord('core_company');

company.get(current.company); // Get company record using the company's sys_id

var requisition = new GlideRecord('x_jade_procurement_it_purchase_requisition');

requisition.get(current.sys_id); // Get requisition record using the requisition's sys_id

// Initialize email body variable

var emailBody = "";

// Add company information in table format with blue headers

emailBody += "<h2>Company Information</h2>";

emailBody += "<table border='1' style='width:100%; border-collapse: collapse;'>";

// Add blue color to the company table headers

emailBody += "<tr style='background-color: #007bff; color: white;'>";

emailBody += "<th>Vendor Name</th>";

emailBody += "<th>Quotation</th>";

emailBody += "<th>Unit Quantity</th>";

emailBody += "<th>Price Per Unit</th>";

emailBody += "<th>Total</th>";

emailBody += "</tr>";

// Add rows for each vendor depending on vendor count

if (requisition.vendor_count >= 1) {

// First vendor

emailBody += "<tr>";

emailBody += "<td>" + requisition.vendor_1.getDisplayValue() + "</td>";

emailBody += "<td>" + requisition.quotation_1.getDisplayValue() + "</td>";

emailBody += "<td>" + requisition.unit_quantity_1 + "</td>";

emailBody += "<td>" + requisition.price_per_unit_1 + "</td>";

emailBody += "<td>" + requisition.total_1 + "</td>";

emailBody += "</tr>";

}

if (requisition.vendor_count >= 2) {

// Second vendor

emailBody += "<tr>";

emailBody += "<td>" + requisition.vendor_2.getDisplayValue() + "</td>";

emailBody += "<td>" + requisition.quotation_2.getDisplayValue() + "</td>";

emailBody += "<td>" + requisition.unit_quantity_2 + "</td>";

emailBody += "<td>" + requisition.price_per_unit_2 + "</td>";

emailBody += "<td>" + requisition.total_2 + "</td>";

emailBody += "</tr>";

}

if (requisition.vendor_count >= 3) {

// Third vendor

emailBody += "<tr>";

emailBody += "<td>" + requisition.vendor_3.getDisplayValue() + "</td>";

emailBody += "<td>" + requisition.quotation_3.getDisplayValue() + "</td>";

emailBody += "<td>" + requisition.unit_quantity_3 + "</td>";

emailBody += "<td>" + requisition.price_per_unit_3 + "</td>";

emailBody += "<td>" + requisition.total_3 + "</td>";

emailBody += "</tr>";

}

emailBody += "</table>";

// Add requisition details with blue headers

emailBody += "<h2>Requisition Details</h2>";

emailBody += "<table border='1' style='width:100%; border-collapse: collapse;'>";

// Add blue color to the requisition table headers

emailBody += "<tr style='background-color: #007bff; color: white;'>";

emailBody += "<th>Requisition Number</th>";

emailBody += "<th>Requestor Name</th>";

emailBody += "<th>Assigned To</th>";

emailBody += "<th>Short Description</th>";

emailBody += "<th>Delivery Location</th>";

emailBody += "<th>Status</th>";

emailBody += "<th>Description</th>";

emailBody += "<th>Quantity</th>";

emailBody += "<th>Department</th>";

emailBody += "</tr>";

// Ensure requisition record is retrieved successfully before accessing its fields

if (requisition.isValidRecord()) {

emailBody += "<tr>";

emailBody += "<td>" + requisition.requisition_no + "</td>"; // Requisition Number

emailBody += "<td>" + requisition.requester_name.getDisplayValue() + "</td>"; // Requestor Name

emailBody += "<td>" + requisition.assigned_to.getDisplayValue() + "</td>"; // Assigned To

emailBody += "<td>" + requisition.short_description + "</td>"; // Short Description

emailBody += "<td>" + requisition.delivery_location.getDisplayValue() + "</td>"; // Delivery Location

emailBody += "<td>" + requisition.status + "</td>"; // Status

emailBody += "<td>" + requisition.description + "</td>"; // Description

emailBody += "<td>" + requisition.quantity + "</td>"; // Quantity

emailBody += "<td>" + requisition.department.getDisplayValue() + "</td>"; // Department

emailBody += "</tr>";

} else {

emailBody += "<tr><td colspan='9'>No requisition details available.</td></tr>";

}

emailBody += "</table>";

// Print email body to template

template.print(emailBody);

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

here is the snipate of the email notification :

here in the company information there will be two rows in the company section if the vendor count is 2 .

can you please help me on that script is not working for the same.

thankss!!

Voona Rohila · ‎10-29-2024

I can see that you raised separate post for this new issue/requirement, I will review there and provide my inputs. https://www.servicenow.com/community/app-engine-forum/dynamic-population-of-the-rows-via-email-scrip...

Please close this thread by marking relevant answer as correct.

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Rohila V
2022-25 ServiceNow Community MVP