Learn how to create a customization to process a queue

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.

Step 4: Create a map/reduce script to process the Queue

This script starts by finding all “Queue Element” records of type “Sales Order” which are in “Queued” or “Working” status.  The next retry date must also be before or equal to the time the processor runs.  The script then loads the “Queue Element” record.  It then processes the payload which in our example creates a sales order.   It finally sets the “Queue Element” processed date to now, status to completed, and set the transaction to the sales order that was created.

If an error occurs and we are below the maximum retries (a script parameter), the “Queue Element” record status is set to “Working” and the next retry date is updated.   The next retry date will be progressively farther into the future each time it fails to process.

If an error occurs and we are equal to or above the maximum number of retries, the “Queue Element” record status is set to “Failed” and the next retry date is set to empty.  The map/reduce will not try to process this record again.

The script deployment can be scheduled to run as needed.  The retry logic can be modified to whatever makes sense for your customization.

Add and deploy the script (code shown below).  Reference my article “Quick Guide to Adding and Deploying a Script in NetSuite” if needed.  You will need to create a script parameter “Number of Maximum Retries” which will be an integer with internal id “custscript_max_retries”.

/**
 * @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

In summary, this NetSuite customization offers a streamlined approach for managing data errors by establishing an internal queue. Through the use of saved searches, you can easily identify ‘Queue Element’ records that have encountered errors. This enables not only quick error resolution but also allows for subsequent reprocessing through the map/reduce script. By observing the types of errors that occur, you can further refine your data processing techniques to minimize or eradicate these issues. Overall, this solution enhances your data integrity and operational efficiency within NetSuite.

 If you need help scripting or customizing NetSuite please contact Suite Tooth consulting here to set up a free consultation.

If you liked this article, please sign up for my newsletter to get these delivered to your inbox here.

Follow on
Jaime Requena Chief Executive Officer

Jaime Requena is a seasoned NetSuite Consultant and Solutions Architect, known for delivering WHITE GLOVE service to businesses. With 15+ years of experience and 3x certifications in ERP, Developer, and Admin, Jaime specializes in highly customized NetSuite accounts, transforming operations for 200+ satisfied customers all across the globe.

Get Connected

How can we help?


    Stay in the loop with Suite Tooth Consulting!

    We aim to bring unmatched expertise and professionalism to your NetSuite initiatives. Let’s talk about how our NetSuite consultancy can make a difference!

    Global Client Satisfaction

    Client Testimonials

    It’s Been 4+ Years Now And We Have Worked With Hundreds Of Clients, Building Our Way To The Top, One Happy Client After Another! Their Voices Of Satisfaction Serve As A Testament To Our Success –