在NetSuite销售订单同步至Salesforce时，如何追踪已编辑字段？

核心问题分析 #

当前脚本直接使用newRecord同步全量数据，未对比oldRecord（更新前记录）与newRecord（更新后记录）的差异，导致无法追踪具体变更字段，还可能产生冗余的Salesforce API调用。

实现字段变更追踪的关键步骤 #

• 利用NetSuite User Event脚本的context.oldRecord与newRecord做字段对比，筛选出实际变更的字段
• 仅将变更字段打包成updateData同步到Salesforce，减少无效API请求
• 针对订单明细（item子列表），区分新增、修改、删除的行数据，分别执行对应操作

修改后的完整代码 #

function afterSubmit(context) {
    var accessToken = 'abcd';
    var newRec = context.newRecord;
    var oldRec = context.oldRecord;
         
    try {
        if (context.type === context.UserEventType.EDIT) {
            var salesforceId = newRec.getValue({ fieldId: 'custbody_salesforce_order_id' });
            log.debug("SO salesforceId", salesforceId);
            
            if (salesforceId) {
                // 提取主记录变更字段
                var updateData = getChangedFields(newRec, oldRec);
                if (Object.keys(updateData).length > 0) {
                    updateSalesforceOrder(accessToken, updateData, salesforceId);
                } else {
                    log.debug("No main record fields changed", "Skipping order header update");
                }
                
                // 处理订单明细变更
                updateChangedSalesforceOrderItems(newRec, oldRec, 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 getChangedFields(newRec, oldRec) {
    var changedFields = {};
    // 按需添加需要追踪的主记录字段
    var trackedFields = [
        'trandate', 'entity', 'total', 'status', 
        'custbody_custom_field1', 'custbody_custom_field2'
    ];
    
    trackedFields.forEach(function(fieldId) {
        var oldValue = oldRec.getValue({ fieldId: fieldId });
        var newValue = newRec.getValue({ fieldId: fieldId });
        
        // 处理日期、数字类型的对比，避免类型差异导致误判
        if (typeof oldValue === 'date' && typeof newValue === 'date') {
            if (oldValue.getTime() !== newValue.getTime()) {
                changedFields[mapNetsuiteToSalesforceField(fieldId)] = newValue;
            }
        } else if (oldValue !== newValue) {
            changedFields[mapNetsuiteToSalesforceField(fieldId)] = newValue;
        }
    });
    
    return changedFields;
}

// NetSuite字段到Salesforce字段的映射（根据实际配置调整）
function mapNetsuiteToSalesforceField(netsuiteFieldId) {
    var fieldMap = {
        'trandate': 'EffectiveDate',
        'entity': 'AccountId',
        'total': 'TotalAmount',
        'status': 'Status',
        'custbody_custom_field1': 'Custom_Field_1__c',
        'custbody_custom_field2': 'Custom_Field_2__c'
    };
    return fieldMap[netsuiteFieldId] || netsuiteFieldId;
}

// 处理订单明细的变更（新增、修改、删除）
function updateChangedSalesforceOrderItems(newRec, oldRec, accessToken, salesforceOrderId) {
    var newItems = getSublistItems(newRec);
    var oldItems = getSublistItems(oldRec);
    
    // 获取Salesforce现有订单明细
    var existingItemsResult = getSalesforceOrderItems(accessToken, salesforceOrderId);
    if (!existingItemsResult.success) {
        log.error('Error fetching Salesforce order items', existingItemsResult.error);
        return;
    }
    var existingItems = existingItemsResult.records;
    
    var updatedItems = [];
    var newAddedItems = [];
    var deletedItemIds = [];
    
    // 找出修改和新增的明细项
    newItems.forEach(function(newItem) {
        var matchedOldItem = oldItems.find(function(oldItem) {
            return oldItem.itemId === newItem.itemId;
        });
        
        if (matchedOldItem) {
            if (matchedOldItem.quantity !== newItem.quantity || matchedOldItem.rate !== newItem.rate) {
                updatedItems.push(newItem);
            }
        } else {
            newAddedItems.push(newItem);
        }
    });
    
    // 找出被删除的明细项
    oldItems.forEach(function(oldItem) {
        var matchedNewItem = newItems.find(function(newItem) {
            return newItem.itemId === oldItem.itemId;
        });
        if (!matchedNewItem) {
            var sfItem = existingItems.find(function(item) {
                return item.Product2Id === getSalesforceProductId(oldItem.itemId);
            });
            if (sfItem) {
                deletedItemIds.push(sfItem.Id);
            }
        }
    });
    
    // 执行更新、新增、删除操作
    updatedItems.forEach(function(item) {
        updateSalesforceOrderItem(item, accessToken, existingItems);
    });
    newAddedItems.forEach(function(item) {
        createSalesforceOrderItem(item, accessToken, salesforceOrderId);
    });
    deletedItemIds.forEach(function(itemId) {
        deleteSalesforceOrderItem(itemId, accessToken);
    });
}

// 提取子列表数据
function getSublistItems(rec) {
    var items = [];
    var lineCount = rec.getLineCount({ sublistId: 'item' });
    
    for (var i = 0; i < lineCount; i++) {
        items.push({
            itemId: rec.getSublistValue({ sublistId: 'item', fieldId: 'item', line: i }),
            quantity: rec.getSublistValue({ sublistId: 'item', fieldId: 'quantity', line: i }),
            rate: rec.getSublistValue({ sublistId: 'item', fieldId: 'rate', line: i })
        });
    }
    return items;
}

// 更新单个Salesforce订单明细
function updateSalesforceOrderItem(item, accessToken, existingItems) {
    var salesforceProductId = getSalesforceProductId(item.itemId);
    if (!salesforceProductId) {
        log.error('Salesforce Product ID not found', item.itemId);
        return;
    }
    
    var sfItem = existingItems.find(function(sfItem) {
        return sfItem.Product2Id === salesforceProductId;
    });
    if (!sfItem) return;
    
    var orderItemData = {
        Quantity: item.quantity,
        UnitPrice: item.rate
    };
    
    var response = https.post({
        url: 'https://xyz-ed.develop.my.salesforce.com/services/data/v61.0/sobjects/OrderItem/' + sfItem.Id + '?_HttpMethod=PATCH',
        headers: {
            'Authorization': 'Bearer ' + accessToken,
            'Content-Type': 'application/json'
        },
        body: JSON.stringify(orderItemData)
    });
    
    if (response.code === 204) {
        log.debug('Salesforce Order Item Updated', sfItem.Id);
    } else {
        var responseBody = JSON.parse(response.body);
        log.error('Error updating order item in Salesforce', responseBody);
    }
}

// 创建新的Salesforce订单明细
function createSalesforceOrderItem(item, accessToken, salesforceOrderId) {
    var salesforceProductId = getSalesforceProductId(item.itemId);
    if (!salesforceProductId) {
        log.error('Salesforce Product ID not found', item.itemId);
        return;
    }
    
    var priceBookEntryIdResult = getpriceBookProductId(accessToken, salesforceProductId);
    if (!priceBookEntryIdResult || !priceBookEntryIdResult.records[0]) {
        log.error('Pricebook Entry ID not found', salesforceProductId);
        return;
    }
    var pbid = priceBookEntryIdResult.records[0].Id;
    
    var newOrderItemData = {
        OrderId: salesforceOrderId,
        Product2Id: salesforceProductId,
        PricebookEntryId: pbid,
        Quantity: item.quantity,
        UnitPrice: item.rate
    };
    
    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)
    });
    
    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);
    }
}

// 删除Salesforce订单明细
function deleteSalesforceOrderItem(itemId, accessToken) {
    var response = https.post({
        url: 'https://xyz-ed.develop.my.salesforce.com/services/data/v61.0/sobjects/OrderItem/' + itemId + '?_HttpMethod=DELETE',
        headers: {
            'Authorization': 'Bearer ' + accessToken
        }
    });
    
    if (response.code === 204) {
        log.debug('Salesforce Order Item Deleted', itemId);
    } else {
        var responseBody = JSON.parse(response.body);
        log.error('Error deleting order item in Salesforce', responseBody);
    }
}

// 原有的updateSalesforceOrder函数（仅同步变更字段）
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
        };
    }
}

// 完善getSalesforceOrderItems函数，返回正确格式的结果
function getSalesforceOrderItems(accessToken, salesforceOrderId) {
    try {
        var response = https.get({
            url: 'https://xyzxyz-ed.develop.my.salesforce.com/services/data/v61.0/query/?q=SELECT+Id,+Product2Id,+Quantity,+UnitPrice+FROM+OrderItem+WHERE+OrderId=\'' + salesforceOrderId + '\'',
            headers: {
                'Authorization': 'Bearer ' + accessToken
            }
        });
        
        var responseBody = JSON.parse(response.body);
        return {
            success: true,
            records: responseBody.records
        };
    } catch (e) {
        return {
            success: false,
            error: e.message
        };
    }
}

// 保留原有的getSalesforceProductId和getpriceBookProductId函数（需确保已实现）
function getSalesforceProductId(itemId) {
    // 你的现有实现逻辑
}

function getpriceBookProductId(accessToken, salesforceProductId) {
    // 你的现有实现逻辑
}

关键改进点说明 #

• 精准字段追踪：通过getChangedFields函数对比新旧记录，仅同步实际变更的字段，避免全量更新
• 明细变更区分：针对订单明细的新增、修改、删除操作分别处理，减少冗余API调用
• 字段映射管理：统一维护NetSuite与Salesforce的字段映射关系，便于后续维护
• 错误处理增强：完善getSalesforceOrderItems函数的返回格式，确保异常情况被正确捕获

使用注意事项 #

1. 根据业务需求，在trackedFields数组中添加需要追踪的主记录字段
2. 调整mapNetsuiteToSalesforceField函数中的字段映射关系，匹配实际系统配置
3. 确保getSalesforceProductId和getpriceBookProductId函数已正确实现，能返回有效ID
4. 测试时可通过log.debug输出变更字段集合，验证追踪逻辑正确性

内容的提问来源于stack exchange，提问作者Maira S