This script sample is to send emails to customers on a quarterly basis.
This sample uses the saved search result to get the 10 number of items that were purchased by the customers in the past
define(['N/search', 'N/email', 'N/render', 'N/log', 'N/format'],
(search, email, render, log, format) => {
const EMAIL_TEMPLATE_ID = 155; // Replace with the correct Email Template ID
const MAX_ITEMS_PER_CUSTOMER = 10;
const SUITELET_URL = 'https://687719.extforms.netsuite.com/app/site/hosting/scriptlet.nl?script=2821&deploy=1&compid=687719&ns-at=AAEJ7tMQSQ5UCEb__1ZPcipQnmktKeyYz0jAJeDHF_zqTqI6NGk'; // Replace with your Suitelet script and deployment ID
const BASECURRENCY = 'US DOLLAR'
const AUTHOR_ID = 586154;
const getInputData = () => {
try {
const customerSearch = search.create({
type: "customer",
filters: [
["custentity_90_day_reminder_email","is","T"], // Replace with your test IDs
"AND",
["isinactive","is","F"],
"AND",
["email","isnotempty",""],
"AND",
["custentity_jj_email_unsubscribe","is","F"],
"AND",
// ["lastsaledate","within","yearsago1","monthsago3"],
// "AND",
["stage","anyof","CUSTOMER"]
],
columns: ["internalid"]
});
const resultsCount = customerSearch.runPaged().count;
log.audit('Search Results Count', `Found ${resultsCount} customer(s)`);
return customerSearch;
} catch (e) {
log.error('Error@getInputData', e);
}
};
const map = (context) => {
const customerId = JSON.parse(context.value).id;
try {
log.audit('Processing Customer ID', customerId);
const items = getItems(customerId);
if (items.length > 0) {
const customerDetails = getCustomerDetails(customerId);
if (customerDetails?.id && customerDetails?.email) {
sendEmail(customerDetails, items);
log.audit('Email Sent', `Customer ID: ${customerId}, Email: ${customerDetails.email}`);
} else {
log.error('Invalid Customer Details', `Customer ID: ${customerId}`);
context.write(customerId, 'Invalid Customer Details');
}
} else {
log.audit('No Items Found', `Customer ID: ${customerId} has no items.`);
context.write(customerId, 'No Items Found');
}
} catch (e) {
log.error(`Error@map - Customer ID: ${customerId}`, e);
context.write(customerId, e.message);
}
};
const getItems = (customerId) => {
let items = [];
try {
const transactionSearch = search.create({
type: "transaction",
filters: [
["type", "anyof", "CashSale", "CustInvc"],
"AND", ["taxline", "is", "F"],
"AND", ["cogs", "is", "F"],
"AND", ["shipping", "is", "F"],
"AND", ["mainline", "is", "F"],
"AND", ["customermain.internalid", "anyof", customerId],
// "AND", ["trandate","within","yearsago1","monthsago3"]
],
columns: [
search.createColumn({ name: "item", summary: "GROUP" }),
search.createColumn({ name: "quantity", summary: "SUM" }),
search.createColumn({ name: "amount", summary: "SUM", sort: search.Sort.DESC }),
search.createColumn({ name: "rate",summary: "MAX" })
]
});
transactionSearch.run().each(result => {
items.push({
itemName: result.getText({ name: "item", summary: "GROUP" }),
quantity: result.getValue({ name: "quantity", summary: "SUM" }),
amount: format.format({ value: result.getValue({ name: "amount", summary: "SUM" }), type: format.Type.CURRENCY }),
itemRate: format.format({value: result.getValue({ name: "rate", summary: "MAX" }),type: format.Type.CURRENCY })
});
return items.length < MAX_ITEMS_PER_CUSTOMER;
});
log.audit('Items Found', JSON.stringify(items));
return items;
} catch (e) {
log.error('Error@getItems', e);
}
};
const getCustomerDetails = (customerId) => {
try {
const customerData = search.lookupFields({
type: "customer",
id: customerId,
columns: ["companyname", "email", "isperson", "firstname", "lastname", "middlename"]
});
let customerName;
let isPerson = customerData.isperson;
if (isPerson) {
let firstName = customerData.firstname || "";
let middleName = customerData.middlename || "";
let lastName = customerData.lastname || "";
customerName = `${firstName} ${middleName} ${lastName}`.trim();
if (!customerName) {
customerName = "Valued Customer";
}
} else {
customerName = customerData.companyname || "Valued Customer";
}
return {
id: customerId,
customerName,
email: customerData.email || ""
};
} catch (e) {
log.error('Error@getCustomerDetails', `Customer ID: ${customerId}, Error: ${e.message}`);
return null;
}
};
const sendEmail = (customer, items) => {
try {
// Generate table HTML only if items exist
const tableHTML = items && items.length > 0
? `
<table style="border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; margin-top: 20px;">
<thead style="background-color: #f2f2f2;">
<tr>
<th style="padding: 10px; border: 1px solid #ddd; text-align: left;">Item</th>
<th style="padding: 10px; border: 1px solid #ddd; text-align: center;">Quantity</th>
<th style="padding: 10px; border: 1px solid #ddd; text-align: center;">Item Rate</th>
<th style="padding: 10px; border: 1px solid #ddd; text-align: right;">Amount</th>
<th style="padding: 10px; border: 1px solid #ddd; text-align: center;">Currency</th>
</tr>
</thead>
<tbody>
${items.map(item => `
<tr style="background-color: #fff;">
<td style="padding: 10px; border: 1px solid #ddd;">${item.itemName}</td>
<td style="padding: 10px; border: 1px solid #ddd; text-align: center;">${item.quantity}</td>
<td style="padding: 10px; border: 1px solid #ddd; text-align: right;">${item.itemRate}</td>
<td style="padding: 10px; border: 1px solid #ddd; text-align: right;">${item.amount}</td>
<td style="padding: 10px; border: 1px solid #ddd; text-align: center;">${BASECURRENCY}</td>
</tr>`).join('')}
</tbody>
</table>`
: "<p>No items to display in the order summary.</p>";
// Generate unsubscribe link HTML
const unsubscribeLink = `${SUITELET_URL}&customerId=${customer.id}`;
const linkHtml = `
<p style="margin-top: 20px; font-size: 12px; font-family: Arial, sans-serif; text-align: center; color: #666;">
<a href="${unsubscribeLink}" style="color: #007bff; text-decoration: none;">
Click here to unsubscribe.
</a>
</p>`;
// Merge the email template
const mergeResult = render.mergeEmail({
templateId: EMAIL_TEMPLATE_ID,
entity: { type: 'customer', id: parseInt(customer.id) },
});
// Replace placeholders in the email body
let emailBody = mergeResult.body;
emailBody = emailBody.replace('#customerName#', customer.customerName);
emailBody = emailBody.replace('#table#', tableHTML);
emailBody = emailBody.replace('#link#', linkHtml);
// Send the email
email.send({
author: AUTHOR_ID,
recipients: customer.email,
subject: mergeResult.subject,
body: emailBody,
relatedRecords: {
entityId: customer.id,
},
});
log.audit('Email Sent', `Customer: ${customer.customerName}, Email: ${customer.email}`);
} catch (e) {
log.error(`Error@sendEmail - Customer ID: ${customer?.id || 'Unknown'}`, e.message);
throw e; // To log in the summarize phase
}
};
const summarize = (summary) => {
try {
let totalProcessed = 0;
let failedCustomers = [];
summary.mapSummary.keys.iterator().each((key) => {
totalProcessed += 1;
return true;
});
summary.mapSummary.errors.iterator().each((key, error) => {
failedCustomers.push({ customerId: key, error });
log.error(`Error in Map Stage for Customer ID: ${key}`, error);
return true;
});
log.audit('Summary', `Total Customers Processed: ${totalProcessed}`);
log.audit('Failed Customers', JSON.stringify(failedCustomers));
log.audit('Summarize Completed', 'All stages processed.');
} catch (e) {
log.error('Error@summarize', e.message);
}
};
return {
getInputData,
map,
summarize
};
});