Requirement:
Need to fetch all deal in ‘Closed Won’ status from HubSpot and create a corresponding sales order in NetSuite in ‘Pending Fulfillment’ status.
Library used : Library file containing API’s to connect with HubSpot CRM. – Jobin & Jismi IT Services – Knowledge Base (jobinandjismi.in)
Solution:
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/**
* Script Description
* This Map Reduce to create sales order in netsuite for each deal in closed won stage
*
/*******************************************************************************
* VCPP-43 HubSpot Integration
* *******************************************************************************
* $Author: Jobin & Jismi IT Services LLP $
*
* Date: 17-06-2022
* DESCRIPTION
* This Map Reduce to create sales order in netsuite for each deal in HubSpot in closed won stage
*
******************************************************************************/
define(['N/email', 'N/error', 'N/file', 'N/https', 'N/record', 'N/runtime', 'N/search', 'N/task', './Hubspot Integration-Library.js'],
/**
* @param{email} email
* @param{error} error
* @param{file} file
* @param{https} https
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{task} task
*/
(email, error, file, https, record, runtime, search, task, HubLibrary) => {
let library = HubLibrary.Library;
let apiKey = 'f5e7d06f-8261-4837-b716-926476f8ce3a';
/**
* getInput stage of Map/Reduce script
* @param inputContext
* @returns {*[]|Any|boolean}
*/
const getInputData = (inputContext) => {
try {
let body = {
"filterGroups": [{
"filters": [{
"value": "closedwon",
"propertyName": "dealstage",
"operator": "EQ"
},
{
"propertyName": "order_created",
"operator": "NOT_HAS_PROPERTY"
}
]
}]
}
let dealResponse = apiCreation('POST_DEALS', body, "", "", "POST")
log.debug("dealResponse", dealResponse)
return dealResponse
} catch (e) {
log.error("Error@getInputData", e)
return [];
}
}
/**
* Reduce stage of Map/Reduce script
* @param reduceContext
*/
const reduce = (reduceContext) => {
try {
let dealID, dealName, dealPipeLine, dealAmount, associatedCompany, companyID, customer, custRec;
let dataObj = JSON.parse(reduceContext.values)
dealID = dataObj.id;
dealName = dataObj.properties.dealname;
dealPipeLine = dataObj.properties.pipeline;
dealAmount = dataObj.properties.amount;
let uniqueDeal = dealID + "_" + dealPipeLine;
let dealDetails = [dealID, dealName, dealPipeLine, dealAmount]
//check if a salesorder for this deal already exists or not in netsuite
let orderExist = searchRecordEntry(uniqueDeal, "externalid", 'salesorder')
log.audit("orderExist " + dealName, orderExist)
if (!checkForParameter(orderExist)) {
//check if custom record entry for this deal already exists, if not then create.
let customDeal = searchRecordEntry(dealID, 'externalid', 'customrecord_jj_hs_deal_intgration')
log.audit("customDeal "+dealName, customDeal)
if (checkForParameter(customDeal)) //load custom record entry
custRec = record.load({
type: 'customrecord_jj_hs_deal_intgration',
id: customDeal,
isDynamic: true
});
else {
custRec = record.create({ //create a custom record entry
type: 'customrecord_jj_hs_deal_intgration',
isDynamic: true
});
// custRec.setValue({fieldId: 'custrecord_jj_hs_salesorder', value: salesOrder})
custRec.setValue({fieldId: 'name', value: dealName})
custRec.setValue({fieldId: 'externalid', value: dealID})
custRec.setValue({fieldId: 'custrecord_jj_hs_dealname', value: dealName})
custRec.setValue({fieldId: 'custrecord_jj_hs_dealid', value: dealID})
custRec.setValue({fieldId: 'custrecord_jj_hs_pipeline', value: dealPipeLine})
custRec.setValue({fieldId: 'custrecord_jj_hs_stage', value: "Closed Won"})
}
//get the company associated with the deal
associatedCompany = apiCreation("GET_DEAL_ASSOCIATIONS_COMPANY", "", "{dealId}", dealID, "GET")
if (associatedCompany.length <= 0) {
log.error("Empty response", "No Company is associated with this deal")
custRec.setValue({
fieldId: 'custrecord_jj_customer_error',
value: "No Company is associated with this deal"
})
saveRecord(custRec)
return;
}
log.debug("associatedCompany", associatedCompany)
companyID = associatedCompany[0].id
//check if the customer exists in netsuite.
let customerExist = searchRecordEntry(companyID, 'custentity_jj_hs_companyid', 'customer')
if (checkForParameter(customerExist)) {
customer = customerExist;
} else {
//create the customer in netsuite, set the companyid as external id of the customer record
customer = createCustomer(companyID, custRec)
if (!checkForParameter(customer))
return;
}
custRec.setValue({fieldId: 'custrecord_hs_companyid', value: companyID})
custRec.setValue({fieldId: 'custrecord_jj_hs_customer', value: customer})
let associatedItem = apiCreation("GET_DEAL_ASSOCIATIONS_ITEM", "", "{dealId}", dealID, "GET")
if (associatedItem.length <= 0) {
log.error("Empty Item", "No line items are associated with this deal")
custRec.setValue({
fieldId: 'custrecord_jj_item_error',
value: "No line item is associated with this deal"
})
saveRecord(custRec)
return;
}
let itemArray = [], arr = [], errorOccured = false;
//title for csv file
var titleArray = ["PRODUCT NAME", "OBJECT ID", "DEAL NAME", "DEAL ID", "REASON"];
var csvFileData = titleArray.toString() + '\r\n';
for (let i = 0; i < associatedItem.length; i++) {
let itemObj = {};
let itemID = associatedItem[i].id
//get product details
let productDetails = apiCreation("GET_LINE_ITEM", "", "{line_item}", itemID, "GET")
log.debug("productDetails", productDetails)
var customlineItem = false, emailSent = false, itemExist;
var itemSKU = productDetails.properties['hs_sku'];
var itemName = productDetails.properties.name;
var productID = productDetails.properties['hs_product_id'];
if (checkForParameter(productID)) {
//check if the item exists in netsuite.
itemExist = searchRecordEntry([productID, itemSKU], 'custitem_jj_hubspot_id', 'item')
if (checkForParameter(itemExist)){
itemObj.item = itemExist;
itemObj.objId = itemID;
itemObj.name = itemName;
itemObj.sku = itemSKU;
itemObj.amount = productDetails.properties.amount;
itemObj.price = productDetails.properties.price;
itemObj.quantity = productDetails.properties.quantity;
itemObj.description = productDetails.properties.description;
itemArray.push(itemObj);
arr.push(itemExist)
} else {
errorOccured = true;
//append the error reason to the csv file
csvFileData += itemName + ',' + itemID + ',' + dealName + ',' + dealID + ',' + "This product is not yet created in NetSuite";
csvFileData += '\r\n';
// //if the item does not exist in netsuite, send an error message to an employee
// emailSent = sendErrorEmail(itemName, itemSKU, dealName, '')
// //create a csv file showing the items and store the file in CSV
// createErrorFile(itemName, itemID)
}
// customlineItem = sendErrorEmail(itemName, itemSKU, dealName, 'Not Available')
}
else{
errorOccured = true;
csvFileData += itemName + ',' + itemID + ',' + dealName + ',' + dealID + ',' + "No product id is associated with this product which means this is a custom item line";
csvFileData += '\r\n';
}
//log.audit("condition", customlineItem +" " +emailSent )
}
if (errorOccured){
//file creation:
let errorFile = createErrorFile(csvFileData, dealID)
//if the item does not exist in netsuite, send an error message to an employee
sendErrorEmail(itemName, itemSKU, dealName, errorFile[0])
custRec.setValue({
fieldId: 'custrecord_jj_item_error',
value: "One or more items in this deal are not available in NetSuite"
})
//set the file in a custom field in custom record
custRec.setValue({
fieldId: 'custrecord_jj_hubspot_itemerror',
value: errorFile[1]
})
saveRecord(custRec)
return;
}
//if the count of item lines in deal and length of itemArray are equal, then only create the SO
if (itemArray.length === associatedItem.length) {
//create SO
let salesOrder = createSalesorderForDeal(customer, itemArray, dealDetails, custRec)
if (checkForParameter(salesOrder)) {
custRec.setValue({fieldId: 'custrecord_jj_hs_salesorder', value: salesOrder})
custRec.setValue({fieldId: 'custrecord_jj_hs_items', value: arr})
custRec.setValue({fieldId: 'custrecord_jj_customer_error', value: null})
custRec.setValue({fieldId: 'custrecord_jj_item_error', value: null})
custRec.setValue({fieldId: 'custrecord_jj_hubspot_itemerror', value: null})
custRec.setValue({fieldId: 'custrecord_jj_order_error', value: null})
custRec.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
//set the check box Order Created's value to 'Yes' to exclude this deal from the further executions
let dealUpdateBody = {
"properties": [
{
"name": "order_created",
"value": "T"
}
]
}
let updateDeal = apiCreation("UPDATE_DEAL_PROPERTY", dealUpdateBody, "{dealId}", dealID, "PUT")
}
}
}
} catch (e) {
log.error("Error@reduce", e)
}
}
const summarize = (summaryContext) => {
}
/**
* Function that checks if a customer or item exists in netsuite or not
* @param id
* @param criteria
* @param type
* @returns {boolean|*}
*/
function searchRecordEntry(id, criteria, type) {
let filters;
try {
if (type == 'item') {
filters = [
[criteria, "is", id[0]], //objid
"AND",
["name", "is", id[1]], //sku
"AND",
["isinactive","is","F"]
]
} else if (type == 'customer') {
filters = [
[criteria, "is", id], //custentity_jj_hs_companyid
"AND",
["isinactive","is","F"]
// "AND",
// ['internalid', "is", id[1]]
]
} else
filters = [
[criteria, "is", id]
]
var record_SearchObj = search.create({
type: type,
filters: filters,
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"})
]
});
var internalID;
var searchResultCount = record_SearchObj.runPaged().count;
log.debug("record_SearchObj result count: " + type, searchResultCount);
if (searchResultCount > 0) {
record_SearchObj.run().each(function (result) {
internalID = result.getValue({name: "internalid", label: "Internal ID"})
return false;
});
return internalID;
} else
return false;
} catch (e) {
log.error("Error@searchRecordEntry" + filters, e)
return false;
}
}
/**
* Function to check if a customer with the given customer ID already exists or not in NetSuite
* @param name
* @returns {boolean|*}
*/
function searchCustomerName(name){
try{
var customerSearchObj = search.create({
type: "customer",
filters:
[
["entityid","is",name],
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"})
]
});
var searchResultCount = customerSearchObj.runPaged().count;
var id;
if (searchResultCount>0){
customerSearchObj.run().each(function(result){
id = result.getValue({name: "internalid", label: "Internal ID"})
return true;
});
return id;
}
else
return false;
}catch (e) {
log.error("Error@searchCustomerName", e)
return false
}
}
/**
* Function to create a customer in netsuite for a company in hubspot
* @param companyId
* @param custRec
*/
function createCustomer(companyId, custRec) {
try {
//fetch company details
let companyResponse = apiCreation('GET_COMPANY', "", '{companyid}', companyId, 'GET');
let custName = companyResponse.properties.name;
//search if a customer with this name already exists or not in netsuite
let name = searchCustomerName(custName)
if (checkForParameter(name))
custName = custName+"_"+companyId
var custRec = record.create({
type: record.Type.CUSTOMER,
isDynamic: true
})
// let companyResponse = library.getRequestResults(URL, "", "GET")
log.debug("companyResponse", companyResponse)
custRec.setValue({fieldId: 'companyname', value: custName})
custRec.setValue({fieldId: 'isperson', value: 'F'})
custRec.setValue({
fieldId: 'custentity_jj_hs_companyid',
value: companyResponse.properties["hs_object_id"]
})
custRec.setValue({fieldId: 'subsidiary', value: 11})
if (checkForParameter(companyResponse.properties.phone))
custRec.setValue({fieldId: 'phone', value: companyResponse.properties.phone})
if (checkForParameter(companyResponse.properties.country)) {
if (companyResponse.properties.address)
mapAddressValues(custRec, companyResponse, companyResponse.properties.address)
if (companyResponse.properties["address2"])
mapAddressValues(custRec, companyResponse, companyResponse.properties["address2"])
}
let customer = custRec.save({
enableSourcing: true,
ignoreMandatoryFields: true
})
//set the customer internal id in corresponding HS company record
let body = {
"properties": [
{
"name": "netsuite_customer_id",
"value": customer
}
]
}
apiCreation("PUT_COMPANY", body, "{companyid}", companyResponse.properties["hs_object_id"], "PUT")
return customer;
} catch (e) {
log.error("Error@createCustomer", e)
custRec.setValue({fieldId: 'custrecord_jj_customer_error', value: e.message})
saveRecord(custRec)
return false;
}
}
/**
* Function to map values to address book fields
* @param custRec
* @param companyResponse
* @param address
*/
function mapAddressValues(custRec, companyResponse, address){
try{
//map company address to address subrecord
custRec.selectNewLine({
sublistId: "addressbook"
});
var addressSubrecord = custRec.getCurrentSublistSubrecord({
sublistId: "addressbook",
fieldId: "addressbookaddress"
});
addressSubrecord.setValue({
fieldId: "country",
value: "PK"
});
addressSubrecord.setValue({
fieldId: "addressee",
value: companyResponse.properties.name
});
if (checkForParameter(companyResponse.properties.phone))
addressSubrecord.setValue({
fieldId: "addrphone",
value: companyResponse.properties.phone
});
if (checkForParameter(companyResponse.properties.address)) {
addressSubrecord.setValue({
fieldId: "addr1",
value: address
});
}
if (checkForParameter(companyResponse.properties.city)) {
addressSubrecord.setValue({
fieldId: "city",
value: companyResponse.properties.city
});
}
if (checkForParameter(companyResponse.properties.state)) {
addressSubrecord.setValue({
fieldId: "state",
value: companyResponse.properties.state
});
}
if (checkForParameter(companyResponse.properties.zip)) {
addressSubrecord.setValue({
fieldId: "zip",
value: companyResponse.properties.zip
});
}
custRec.commitLine({
sublistId: 'addressbook'
});
}catch (e) {
log.error("Error@address", e.message)
}
}
/**
* Function that checks if the passed parameter has value or not
* @param parameter
* @param parameterName
* @returns {boolean}
*/
function checkForParameter(parameter, parameterName) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
} else {
if (parameterName)
log.debug('Empty Value found', 'Empty Value for parameter ' + parameterName);
return false;
}
}
/**
* function to create API
* @param API
* @param body
* @param type
* @param typeVal
* @param method
*/
function apiCreation(API, body, type, typeVal, method) {
try {
let response
let url = library.HUBSPOT_API_REQUESTS[API]
if (checkForParameter(type) && checkForParameter(typeVal))
url = url.replace(type, typeVal)
// url += apiKey;
//API request call
if (checkForParameter(body))
response = library.getRequestResults(url, JSON.stringify(body), method)
else
response = library.getRequestResults(url, "", method)
return response[0];
} catch (e) {
log.error("Error@apiCreation", e)
return false;
}
}
/**
* Function to create sales order in NetSuite
* @param customer
* @param itemArray
* @param dealDetails
*/
function createSalesorderForDeal(customer, itemArray, dealDetails, custRec) {
try {
var salesOrd = record.create({
type: record.Type.SALES_ORDER,
isDynamic: true
})
salesOrd.setValue({fieldId: "entity", value: customer})
salesOrd.setValue({fieldId: "externalid", value: dealDetails[0] + "_" + dealDetails[2]})
salesOrd.setValue({fieldId: "status", value: "Pending Fulfillment"})
salesOrd.setValue({fieldId: "total", value: dealDetails[3]})
//set item sublist
let itemSublist = salesOrd.getSublist({
sublistId: 'item'
});
for (let i = 0; i < itemArray.length; i++) {
salesOrd.selectNewLine({sublistId: 'item', line: i});
salesOrd.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
line: i,
value: itemArray[i].item
});
salesOrd.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'quantity',
line: i,
value: itemArray[i].quantity
});
salesOrd.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'description',
line: i,
value: itemArray[i].description
});
salesOrd.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'rate',
line: i,
value: itemArray[i].price
});
salesOrd.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'amount',
line: i,
value: itemArray[i].amount
});
salesOrd.commitLine({sublistId: 'item'});
}
let salesOrder = salesOrd.save({
ignoreMandatoryFields: true,
enableSourcing: true
})
return salesOrder;
} catch (e) {
log.error("Error@createSalesorderForDeal", e)
custRec.setValue({fieldId: 'custrecord_jj_order_error', value: e.message})
saveRecord(custRec)
return false;
}
}
/**
* Function to send error email to an employee
* @param itemName
* @param itemSKU
* @param dealName
* @param type
*/
function sendErrorEmail(itemName, itemSKU, dealName, file) {
try {
let recipient, author, body, subject;
recipient = 15
author = -5
body = "Hi <br>This email is to notify you that some of the items in the deal "+dealName+ " are not available in Netsuite <br>Because of this, we are " +
"unable to create a sales order in Netsuite for this deal. The Sales order will be created successfully once this item is created in Netsuite.<br><br> Please find the attached file listing the missing items.<br>Thank You";
subject = "Item not available in Netsuite"
email.send({
author: author,
recipients: recipient,
subject: subject,
body: body,
attachments: [file],
relatedRecords: {
entityId: recipient,
}
});
return true;
} catch (e) {
log.error("Error@sendErrorEmail", e)
return false;
}
}
function createErrorFile(content, dealID){
try{
//create file
let fileObj = file.create({
name: 'Item_Error_'+dealID+'.csv',
fileType: file.Type.CSV,
contents: content,
encoding: file.Encoding.UTF8,
folder: 215
});
let fileSaved = fileObj.save()
return [fileObj, fileSaved];
}catch (e) {
log.error("Error@createErrorFile", e)
}
}
/**
* Function to save a record
* @param record
*/
function saveRecord(record) {
record.save({
ignoreMandatoryFields: true,
enableSourcing: true
})
}
return {getInputData, reduce, summarize}
});