I'm working on a project where I need to sync sales orders between NetSuite and Salesforce. Specifically, I'm trying to track which fields have been updated in NetSuite and ensure these changes are accurately reflected in Salesforce.
I have a User Event script in NetSuite that triggers when a sales order is edited. This script updates the corresponding Salesforce order. However, I'm struggling with tracking which specific fields have changed during the update. Here's a simplified version of my code:
function afterSubmit(context) {
var accessToken = 'abcd';
var rec = context.newRecord;
try {
if (context.type === context.UserEventType.EDIT) {
var salesforceId = rec.getValue({ fieldId: 'custbody_salesforce_order_id' });
log.debug("SO salesforceId", salesforceId);
updateSalesforceOrder(accessToken, updateData, salesforceId)
if (salesforceId) {
updateSalesforceOrderItems(rec, accessToken, salesforceId);
} else {
log.error('Salesforce Order ID not found', 'Cannot update order in Salesforce without Salesforce Order ID.');
}
}
} catch (e) {
log.error('Error processing order', e.message);
}
}
function updateSalesforceOrder(accessToken, updateData, salesforceId) {
log.debug("updateData", updateData);
log.debug("salesforceId", salesforceId);
var response = https.post({
url: 'https://xyz-ed.develop.my.salesforce.com/services/data/v61.0/sobjects/Order/' + salesforceId + '?_HttpMethod=PATCH',
headers: {
Authorization: 'Bearer ' + accessToken,
'Content-Type': 'application/json'
},
body: JSON.stringify(updateData)
});
var responseBody = JSON.parse(response.body);
log.debug("response--->", response);
if (response.code === 204) {
return {
success: true
};
} else {
log.error('Error updating order in Salesforce', responseBody);
return {
success: false,
error: responseBody
};
}
}
function updateSalesforceOrderItems(rec, accessToken, salesforceOrderId) {
log.debug("updateSalesforceOrderItems function called", {
recId: rec.id,
salesforceOrderId: salesforceOrderId
});
// Fetch existing order items from Salesforce
var existingItems = getSalesforceOrderItems(accessToken, salesforceOrderId);
log.debug("existingItems", existingItems);
if (!existingItems.success) {
log.error('Error fetching Salesforce order items', existingItems.error);
return;
}
var existingItemRecords = existingItems.records;
log.debug("existingItemRecords", existingItemRecords);
// Prepare to process NetSuite order items
var itemCount = rec.getLineCount({ sublistId: 'item' });
log.debug("itemCount", itemCount);
for (var i = 0; i < itemCount; i++) {
var itemId = rec.getSublistValue({ sublistId: 'item', fieldId: 'item', line: i });
var quantity = rec.getSublistValue({ sublistId: 'item', fieldId: 'quantity', line: i });
var rate = rec.getSublistValue({ sublistId: 'item', fieldId: 'rate', line: i });
log.debug("Processing Item", {
itemId: itemId,
quantity: quantity,
rate: rate
});
var salesforceProductId = getSalesforceProductId(itemId);
log.debug("salesforceProductId-->", salesforceProductId);
var priceBookEntryId = getpriceBookProductId(accessToken, salesforceProductId);
log.debug("priceBookEntryId", priceBookEntryId);
var pbid = priceBookEntryId.records[0].Id;
log.debug("pbid", pbid);
if (salesforceProductId) {
var orderItemData = {
Quantity: quantity,
UnitPrice: rate
};
log.debug("orderItemData", orderItemData);
var itemFound = false;
for (var j = 0; j < existingItemRecords.length; j++) {
if (existingItemRecords[j].Product2Id === salesforceProductId) {
// Update existing item
var existingItemId = existingItemRecords[j].Id;
log.debug("Updating item", existingItemId);
var response = https.post({
url: 'https://xyz-ed.develop.my.salesforce.com/services/data/v61.0/sobjects/OrderItem/' + existingItemId + '?_HttpMethod=PATCH',
headers: {
'Authorization': 'Bearer ' + accessToken,
'Content-Type': 'application/json'
},
body: JSON.stringify(orderItemData)
});
log.debug("Response code", response.code);
log.debug("Response body", response.body);
if (response.code === 204) {
log.debug('Salesforce Order Item Updated', existingItemId);
} else {
var responseBody = JSON.parse(response.body);
log.error('Error updating order item in Salesforce', responseBody);
}
itemFound = true;
break;
}
}
if (!itemFound) {
// Create new item
var newOrderItemData = {
OrderId: salesforceOrderId,
Product2Id: salesforceProductId,
PricebookEntryId: pbid,
Quantity: quantity,
UnitPrice: rate
};
log.debug("Creating new item", salesforceProductId);
var response = https.post({
url: 'https://xyz-ed.develop.my.salesforce.com/services/data/v61.0/sobjects/OrderItem/',
headers: {
'Authorization': 'Bearer ' + accessToken,
'Content-Type': 'application/json'
},
body: JSON.stringify(newOrderItemData)
});
log.debug("Response code", response.code);
log.debug("Response body", response.body);
if (response.code === 201) {
var responseBody = JSON.parse(response.body);
log.debug('Salesforce Order Item Created', responseBody.id);
} else {
var responseBody = JSON.parse(response.body);
log.error('Error creating order item in Salesforce', responseBody);
}
}
} else {
log.error('Salesforce Product ID not found', 'Cannot process order item without Salesforce Product ID.');
}
}
}
function getSalesforceOrderItems(accessToken, salesforceOrderId) {
var response = https.get({
url: 'https://xyzxyz-ed.develop.my.salesforce.com/services/data/v61.0/query/?q=SELECT+Id,+Product2Id+FROM+OrderItem+WHERE+OrderId=\'' + salesforceOrderId + '\'',
headers: {
'Authorization': 'Bearer ' + accessToken
}
});
}
If you have a limited set of fields to check you can make use of the old record. Rough idea below:
function afterSubmit(context) {
const oldRec = context.oldRecord;
const newRec = context.newRecord;
const updateSpec = getBodyChanges(newRec, oldRec);
updateSpec.lines = [];
// similar idea for lines but you also have to make sure lines (checking lineuniqekey) have not been removed.
getLineChanges(updateSpec, newRec, oldRec);
if(!updateSpec.anyChanged){
// no fields of interest were changed
return;
}
// proceed with SalesForce update
}
function getBodyChanges(newRec, oldRec){
const updateSpec = {
anyChanged: false,
fields : {}
};
const bodyValueFields = ['tranid', 'memo', ...];
const bodyTextFields = ['location', 'department'...];
const cmpVals = (fld)=>{
if(!oldRec) return true;
return oldRec.getValue({fieldId:fld}) === newRec.getValue({fieldId:fld});
};
bodyValueFields.forEach(f=>{
if(!cmpVals(f)) return;
updateSpec.anyChanged = true;
updateSpec.fields[f] = newRec.getValue({fieldId:f});
});
bodyTextFields.forEach(f=>{
if(!cmpVals(f)) return; // don't need to compare texts to detect a change
updateSpec.anyChanged = true;
updateSpec.fields[f] = newRec.getText({fieldId:f});
});
}