restsalesforcenetsuitesuitescriptsuitescript2.0

How can I track which fields have been edited in Netsuite when syncing NetSuite sales orders to Salesforce?


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
        }
    });

}

Solution

  • 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});
        });
      }