Updating Sales Order New Tax Group on Line Level Using Map/Reduce Script

Overview

This article provides a step-by-step guide on how to update the tax group on the line level of Sales Orders using a Map/Reduce script in NetSuite. The provided script example demonstrates how to achieve this efficiently by leveraging caching and search functionalities.

Script Example

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 */
define(['N/runtime', 'N/record', 'N/cache', 'N/search'],
    /**
     * @param{runtime} runtime
     * @param{record} record
     * @param{cache} cache
     * @param{search} search
     */
    (runtime, record, cache, search) => {

        const getInputData = (inputContext) => {
            // Initialize the global array with an empty array
            let globalArray = [];
            let taxObj = {};

            // Store the global array in the cache
            let scriptCache = cache.getCache({ name: 'globalArrayCache' });
            scriptCache.put({
                key: 'globalArray',
                value: JSON.stringify(globalArray),
                ttl: 3600 // Time to live in seconds
            });

            // Search for tax groups and store in cache
            let taxgroupSearchObj = search.create({
                type: "taxgroup",
                columns: [
                    search.createColumn({ name: "internalid", label: "Name" }),
                    search.createColumn({ name: "rate", label: "Rate" })
                ]
            });
            taxgroupSearchObj.run().each(function (result) {
                let taxGroupId = result.getValue({ name: 'internalid' });
                let taxRate = result.getValue({ name: 'rate' });
                taxObj[taxGroupId] = taxRate;
                return true;
            });
            let taxgroupCache = cache.getCache({ name: 'taxgroupSearchObjCache' });
            taxgroupCache.put({
                key: 'taxgroupSearchObj',
                value: JSON.stringify(taxObj),
                ttl: 3600 // Time to live in seconds
            });

            // Define the input data for the Map/Reduce script
            let salesorderSearchObj = search.create({
                type: "salesorder",
                settings: [{ "name": "consolidationtype", "value": "ACCTTYPE" }],
                filters: [
                    ["type", "anyof", "SalesOrd"],
                    "AND",
                    ["status", "anyof", "SalesOrd:D", "SalesOrd:B"],
                    "AND",
                    ["mainline", "is", "T"]
                ],
                columns: [
                    search.createColumn({ name: "internalid", label: "Internal ID" })
                ]
            });
            let salesOrderIds = [];
            salesorderSearchObj.run().each(function (result) {
                let salesOrderId = result.getValue({ name: 'internalid' });
                salesOrderIds.push(salesOrderId);
                return true;
            });
            return salesOrderIds;
        }

        const reduce = (reduceContext) => {
            // Retrieve the global array from the cache
            let scriptCache = cache.getCache({ name: 'globalArrayCache' });
            let globalArray = JSON.parse(scriptCache.get({ key: 'globalArray' }));

            let salesOrder = JSON.parse(reduceContext.values[0]);
            let taxgroupCache = cache.getCache({ name: 'taxgroupSearchObjCache' });
            let taxObj = JSON.parse(taxgroupCache.get({ key: 'taxgroupSearchObj' }));
            let rec = record.load({
                type: 'salesorder',
                id: salesOrder,
            });
            let recId = rec.id;
            for (let i = 0; i < rec.getLineCount({ sublistId: 'item' }); i++) {
                let j = 0;
                let taxName = rec.getSublistValue({
                    sublistId: 'item',
                    fieldId: 'taxcode',
                    line: i
                });
                let taxRate = rec.getSublistValue({
                    sublistId: 'item',
                    fieldId: 'taxrate1',
                    line: i
                });
                if (taxObj[taxName]) {
                    if (taxObj[taxName] != taxRate) {
                        rec.setSublistValue({
                            sublistId: 'item',
                            fieldId: 'taxcode',
                            line: i,
                            value: ''
                        });
                        rec.setSublistValue({
                            sublistId: 'item',
                            fieldId: 'taxcode',
                            line: i,
                            value: taxName
                        });
                        j++;
                    }
                }
                if (j > 0) {
                    // Add the sales order ID to the global array
                    globalArray.push(recId);
                }
            }
            rec.save();

            // Store the updated global array in the cache
            scriptCache.put({
                key: 'globalArray',
                value: JSON.stringify(globalArray),
                ttl: 3600
            });
        }

        const summarize = (summaryContext) => {
            // Retrieve the global array from the cache
            let scriptCache = cache.getCache({ name: 'globalArrayCache' });
            let globalArray = JSON.parse(scriptCache.get({ key: 'globalArray' }));

            // Log the global array in the summarize stage
            log.audit({
                title: 'Global Array Contents',
                details: globalArray.join(', ')
            });

            // Retrieve the tax group search object from the cache
            let taxGroupCache = cache.getCache({ name: 'taxgroupSearchObjCache' });
            let taxGroupObj = JSON.parse(taxGroupCache.get({ key: 'taxgroupSearchObj' }));

            // Summarize the results
            log.audit({
                title: 'Summary',
                details: 'Map/Reduce script completed successfully'
            });
        }

        return { getInputData, reduce, summarize }
    });

Steps to Implement

1. Define Input Data: Use a search to identify Sales Orders that need updating.
2. Map Function: Process each Sales Order to identify lines that need updating.
3. Reduce Function: Update the necessary lines on each Sales Order.
4. Summarize Function: Log the results and any important information.

This script leverages caching to store and retrieve data efficiently, ensuring that the tax group updates are applied correctly to each Sales Order line.