This article is relevant if you need a bulk or mass update mechanism to update NetSuite item fulfillments with delivery carrier tracking numbers.

Background

NetSuite offers a mechanism to use CSV files to create item fulfillment records. Yet, NetSuite’s CSV upload is not offered for update operations. There are many business models where item fulfillment records are created, such as to perform a pick / pack operation, and then the item fulfillment record is subsequently marked shipped. Meaning, the item fulfillment record needs frequent update.

For example, in a number of our custom third party logistics (3PL) systems integrations, we use the item fulfillment record to act as shipping instructions for the remote warehouse. Naturally, in those custom integrations, when the warehouse provides a ship confirmation that they indeed shipped, we update the item fulfillment record with carrier tracking information including the item quantities shipped.

We recently had a client in a situation where they needed to update existing item fulfillment records with tracking information independent from our integration. If it was just a few records, our client simply could edit the record to include tracking. But in this situation, they had hundreds of records to update.

Staging Table to Update NetSuite Tracking Information

To solve this challenge, we produced a simple staging table to make it easy to CSV upload tracking information. The table would provide a reference to the item fulfillment, the related sales order and the package weight. We then created a custom mass update utility that would allow our client to run a specialized script to read the staging record, lookup the item fulfillment, and if all was in order, update the shipment with tracking information. Simple enough but possibly beyond the reach of many.

Get the Bulk Ship Tracking Bundle

Anytime we see a situation that we think can benefit the NetSuite community, we package up the software into a bundle so that we can give it, without charge, to our clients. Naturally, we help our clients learn how to use the software and we will enhance our algorithms to adapt to specific situations.

In this case, we added the bulk shipment capacity to our Prolecto Staged Transaction Generator bundle. This bundle is useful for producing general integrations between ecommerce and other order management systems.

Update NetSuite Tracking Information Bulk Mass Update Script

For those that are technically oriented, we wrote a SuiteScript 1.0 program to read the staging table and update the item fulfillments. Below is the script which may be helpful to act as a code pattern for you to do the same thing.

//------------------------------------------------------------------
//Script: 	pri_stg_tracking_mu.js
//Description:  Mass update to insert tracking numbers on item fulfillments	
//Date:         20180111
//------------------------------------------------------------------

function loadTracking(rectype, recid){
	try {
		var stg = nlapiLoadRecord(rectype, recid);
		var so 		= stg.getFieldValue('custrecord_pri_stg_st_if_so_reference');
		var ifref 	= stg.getFieldValue('custrecord_pri_stg_st_if_reference')
		var w 		= stg.getFieldValue('custrecord_pri_stg_st_if_weight')
		var number 	= stg.getFieldValue('custrecord_pri_stg_st_if_tracking_number');

		//confirm this reference item fulfillment is found and references the sales order
		var s = nlapiSearchRecord('itemfulfillment',null,
            [
             	new nlobjSearchFilter('tranid', null,'is', ifref),
                new nlobjSearchFilter('tranid','created' + 'from','is', so),
                new nlobjSearchFilter('mainline',null,'is','T')
            ]
        );

        if(!s) {
        	nlapiLogExecution('Debug','loadTracking Search','No Item Fulfillment Found: (if|so) ' + itf + '|' + so);
        	return;
        }

        //load up the item fulfillment record now that it was found
        var itf = nlapiLoadRecord('itemfulfillment', s[0].id);
        var f = false;

        //check to see if there is already tracking numbers
        for(var i = 1; i < = itf.getLineItemCount('package'); i++) {
        	var t = itf.getLineItemValue('package','packagetrackingnumber',i);
        	if(number == t){
        		f = true;
        		break;
        	}
        }

        //if tracking exists, go ahead and update the staged table
        if(f){
        	nlapiLogExecution('Debug','loadTracking Tracking','Tracking already exists: '+ number);
        	nlapiSubmitField(rectype, recid, 'custrecord_pri_stg_st_if', s[0].id);
        	return;
        };
        

        //insert the package line
        itf.selectNewLineItem('package');
        itf.setCurrentLineItemValue('package','packageweight', (w) ? w : 0.001);
        itf.setCurrentLineItemValue('package','packagetrackingnumber',number);
        itf.setCurrentLineItemValue('package','trackingnumberkey',number);
        itf.commitLineItem('package');        

        //commit the staging record with a reference to the item fulfillment
        if(nlapiSubmitRecord(itf, null, true)){
        	nlapiLogExecution('Debug','loadTracking Success','Submitted: '+ number);
        	nlapiSubmitField(rectype, recid, 'custrecord_pri_stg_st_if', s[0].id);
        }

	} catch(e){
		nlapiLogExecution('Debug','loadTracking (recid | error):',recid + ' | ' + e.toString())
	}
}

Get Expert NetSuite Help

This article is an illustration of how you can use the tools of the NetSuite platform to unlock potential. If you are looking to work with a team of NetSuite professionals to take care of your concerns, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube