Introduction

NetSuite customers often rely on integrations to seamlessly transfer data both into and out of the system. Various integration platforms-as-a-service (iPaaS) solutions like Boomi and Celigo are commonly employed to achieve this. However, one recurring issue is the occurrence of errors during the data transfer process. Typically, diagnosing these errors involves a time-consuming examination of iPaaS logs on an individual basis to identify the root cause.

A more efficient approach to this challenge is to direct all incoming data to NetSuite and let the platform itself manage the data processing. This enables you to log and monitor errors directly within NetSuite, simplifying error tracking through the use of saved searches. Additionally, customized code within NetSuite can be implemented to proactively reduce or even eliminate these errors altogether. One effective method for this involves creating a ‘Queue’ custom record, which can then be processed using a map/reduce script. In this blog, we’ll guide you through the steps to set up this robust error-handling system within NetSuite. Let’s dive in!

Step 1: Create two Custom Lists

The first list will be a list of “Queue Types”. As an example, I have added “Sales Order” as a queue type.  You can have any number of “Queue Types”.  You would create one map/reduce script to process each “Queue Type”.

The second list will be a list of statuses for each element of the queue:

• “Queued”: The element is queued to be processed.
• “Completed” : The element has been processed successfully.
• “Working” : The element errored when it tried to process.  The map/reduce script will try to process this element again the next time it runs.
• “Failed” : The element failed to be processed. This means the number of times the processor tried to process the element equals or is greater than the maximum number of retries.  Someone will need to manually review these.

Step 2: Create a Custom Record to house a “Queue Element”

• Queue Type – List/Record STC Queue Type – The type of Queue for this element.
• Payload – Longtext –  The payload to be processed.
• Date Processed – Date/Time – The date/time the element was processed.
• Transaction created – List/Record Transaction – The resulting transaction that was created. Obviously, this only makes sense if you are creating a transaction.  You may be creating other types of records with your queue.
• Error – Textarea –  If the element failed to process, this is the detailed error.
• Number of Retries – Integer – The number of times the processor has tried to process this element.
• Next Retry – Date/Time – The date/time when the processor should try again to process this element.
• Status – List/Record – STC Queue Status – The current status of this element.

Refer to the image below to set the internal IDs of the fields.  Your custom record should look like this:

Step 3:  Create a “Queue Element” record

Our queue is going to create sales orders.  To accomplish this, set the “Queue Element” record as follows:

• Queue Type – “Sales Order”.
• Payload – A JSON string with the data to process.  This data could be anything but for our example it is a JSON string:  “{
  “customerid” : “317”,  // customer internal id – modify this to use a valid customer in your account
  “items” : [{
  “item” : “36”,  //  item internal id – modify this to use a valid internal id in your account
  “quantity” : “2”,
  “rate” : “100”
  }] }”
• Next Retry – Set to date/time when the record is created.  The map/reduce script will process this record when it runs because this date is in the past.
• Status – “Queued”.  This means our record is “Queued” to be processed.

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 * @NModuleScope SameAccount

*/
//------------------------------------------------------------------
//Script: 	    STC_ProcessQueue_MR.js
//------------------------------------------------------------------

define([ 'N/runtime', 'N/record', 'N/search'],
(runtime, record, search) => {

    pad = (value) => {
        if(value < 10) {
            return '0' + value;
        } else {
            return value;
        }
    }

	getInputData = (context) => {
        log.debug('==START==', '==START==');
        var now = new Date();
        var modifier = now.getUTCHours() > 12 ? 'pm' : 'am';
        var hourIn12 = now.getUTCHours() % 12 || 12;
        var dateString = (now.getMonth()+1) + '/' + now.getDate() + '/' + now.getFullYear() + ' ' + hourIn12 + ':' + pad(now.getMinutes()) + ' ' + modifier;

        var queueElementSearchObj = search.create({
            type: "customrecord_stc_queue_element",
            filters:
            [
               ["custrecord_stc_queue_element_next_retry", search.Operator.ONORBEFORE, dateString],   // only get records where the next retry date is in the past or now
               "AND",
               ["custrecord_stc_queue_element_status","anyof","1","2"],   // queued or working
               "AND",
               ["custrecord_stc_queue_element_type","anyof","1"]  // sales order
            ],
            columns:
            [
               'internalid'
            ]
         });

        return queueElementSearchObj;
    }

    reduce = (context) => {
        log.debug('context', context);

        var queueElementRecord = null;
        var error = null;
        try {
            // load the process request custom record
            queueElementRecord = record.load({
                type: 'customrecord_stc_queue_element',
                id: context.key,
                isDynamic: true
            });

            var payload = queueElementRecord.getValue('custrecord_stc_queue_element_payload');
            payload = JSON.parse(payload);
            /////////////////////////////////////////////////////////
            // CREATE YOUR OWN FUNCTION HERE TO PROCESS THE PAYLOAD
            /////////////////////////////////////////////////////////
            var salesOrderId = createSalesOrder(payload);
            ////////////////////////////////////////////////////////
            queueElementRecord.setValue('custrecord_stc_queue_element_date_proc', new Date());
            queueElementRecord.setValue('custrecord_stc_queue_element_transaction', salesOrderId);
            queueElementRecord.setValue('custrecord_stc_queue_element_error', '');
            queueElementRecord.setValue('custrecord_stc_queue_element_next_retry', '');
            queueElementRecord.setValue('custrecord_stc_queue_element_num_retries', '');
            queueElementRecord.setValue('custrecord_stc_queue_element_status', '3'); // completed
            queueElementRecord.save();
            return;
        }
        catch(e) {
            error = JSON.stringify(e);
            log.error('error', error);
        }

        try {
            // if an error occurred and there is a valid queueElementRecord
            if (error && queueElementRecord) {
                queueElementRecord.setValue('custrecord_stc_queue_element_error', error);
                var numRetries = queueElementRecord.getValue('custrecord_stc_queue_element_num_retries');
                var scriptObj = runtime.getCurrentScript();
                var maxRetries  = scriptObj.getParameter({name: 'custscript_max_retries'});
                // if we are at maxRetries or greater, mark the record as failed and do not retry
                if (numRetries+1 >= maxRetries) {
                    queueElementRecord.setValue('custrecord_stc_queue_element_status', 4); // failed
                    queueElementRecord.setValue('custrecord_stc_queue_element_next_retry', '');
                }
                else {
                    // otherwise set the the status to working and set next retry date
                    queueElementRecord.setValue('custrecord_stc_queue_element_status', 2); // working
                    // Create your own custom retry logic here as needed
                    // As an example, next retry date is next retry date + numRetries+1 hours
                    var nextRetryDate = new Date();
                    var dateToMilliseconds = nextRetryDate.getTime();
                    var addedHours = dateToMilliseconds + (3600000*(numRetries+1));
                    var newDate = new Date(addedHours);
                    queueElementRecord.setValue('custrecord_stc_queue_element_next_retry', newDate);
                }
                queueElementRecord.setValue('custrecord_stc_queue_element_num_retries', numRetries+1);
                queueElementRecord.save();
            }
        }
        catch(e) {
            log.error('Error trying to save record in errored state', JSON.stringify(e));
        }
    }

    createSalesOrder = (payload) => {
        var salesOrderRecord = record.create({
            type: record.Type.SALES_ORDER,
            isDynamic: true
        });
        salesOrderRecord.setValue('entity', payload.customerid);
        for (let i = 0; i < payload.items.length; i++) {
            var payloadItem = payload.items[i];
            var lineNum = salesOrderRecord.selectNewLine({sublistId: 'item'});
            salesOrderRecord.setCurrentSublistValue({sublistId: 'item',fieldId: 'item',value: payloadItem.item});
            salesOrderRecord.setCurrentSublistValue({sublistId: 'item',fieldId: 'quantity',value: payloadItem.quantity});
            salesOrderRecord.setCurrentSublistValue({sublistId: 'item',fieldId: 'rate',value: payloadItem.rate});
            salesOrderRecord.commitLine({sublistId: 'item'});
        }
        var recordId = salesOrderRecord.save();
        return recordId;
    }

    summarize = (summary) => {
        log.debug('==END==','==END==');
    }

    return {
        getInputData: getInputData,
        reduce: reduce,
        summarize: summarize
    };

});

Step 5 : Run the map/reduce script

After running the map/reduce script you will see the Date Processed, Transaction Created, and the status set to “Completed”.

Step 6 : Create a “Queue Element” record which will error

In order to see what a record looks like in an errored state, let’s create a “Queue Element” record which will error when processed.  Create another “Queue Element” record with the same data except set the item internal id to a non-existent item.  Then run the map/reduce script again.  You will see the error, number of retries, and next retry filled in.  The status is set to “Working”.  The map/reduce will keep trying to process this record until it is successful or the maximum number of retries is reached.  When that happens, the status will be set to “Failed” and there will be no further attempt to process the record.

Conclusion