Transaction Line Item Numbering in NetSuite

Line-item-by-line-item matching across linked transactions in NetSuite can be a little unclear, so I wrote a script that should hopefully help make things a bit easier for end users.

Here’s the problem, in a nut shell… Let’s say you have a drop ship Sales Order that automatically gets converted into more than one Purchase Order – perhaps you have items on the sales order that are sourced from different vendors. You have 3 items total; two items go to vendor A, and the third item goes to vendor B. NetSuite will number the lines on the sales order – no problem. But take a look at the line numbers on the purchase orders. Each purchase order will number their lines staring with 1.

In the scenario above, you’ll have a SO with line numbers 1, 2 and 3, and a purchase order for vendor A will have line numbers 1 and 2. However, those line numbers don’t necessarily correspond to sales order line 1 and 2. They could be SO line numbers 1 and 3 or 2 and 3, while the purchase order for vendor B will have the remaining sales order line, but it will always have a line number of 1. This makes reconciliation between sales orders and associated purchase orders difficult. To make matters worse, let’s say you have EDI connections between NetSuite and vendors A and B. When they send you an ASN (EDI 855) or a vendor bill (EDI 810), tracing line items on these transactions back to the sales order line item can be a difficult task if you don’t send the original sales order line numbers to your vendors to begin with.

The way to solve this is to create a custom transaction column that is available to both sales items and purchase items, plus a few lines of code in a User Event script on the sales order approval event to create the line numbers:

Now, when the sales order is approved, the custom field will automatically flow downstream to the purchase order line items.

UPDATE:

We’ve modified this a bit to pass along the NetSuite-generated line numbers downstream to the purchase order. EDI enabled vendors will then use the sales order line numbers that copied from the sales order to the purchase order. When the ASN (fulfillment) comes back from the vendor, it will reference those same sale order line numbers so that we can line item match between sales order and fulfillment.

As line items get added and removed from the sales order prior to approval and purchase order creation, you can easily end up with line items that are not sequential at all – perhaps lines 1, 2, 5, 8, 11 or whatever the case may be. Now, all the associated documents can easily be reconciled.

Line #6 in the code reflects this change.

Setting Fields to NULL with NetSuite Web Services

I really like the new version of the NetSuite php Toolkit. It’s so much easier to use, and the syntax for working with various objects is streamlined and intuitive.

However, one issue I’ve run into is that all of the code examples and user discussions found in the NetSuite User Group web site refer to the older version of the toolkit. When I’ve run into cases where the new Toolkit differs from older versions, I have to really dig for the answers to my problems. One such example is setting fields to NULL in NetSuite SuiteTalk web services.

Setting fields to NULL using SuiteTalk is not a terribly well documented feature. For most objects in NetSuite, you can simply take a look at the Web Services Schema Browser for the object you are interested in, and you’ll find all the information you need for the various fields and child-record lists. One list you’ll never see mentioned is one called NullFieldList, probably because its not a child-record list like the other lists.

The NullFieldList is appropriately named – it’s a list of fields within a record you want to set to NULL. The NullFieldList is part of every Record object in NetSuite – it’s global across all Record Types. After a bit if trial and error, I finally have the NullFieldList figured out. Using the Inventory Item object, here’s how you use it with the new version of the php Toolkit:

1. Create a NullField object to hold your list (array) of fields you want to set to null.

$nfl = new NullField();

2. The NullField object takes an array of field internal IDs as its only property, called “name”.

$nfl->name = array(‘field1′, ‘field2′, ‘field3′);

3. Set the NullFieldList property of your record to be equal to the NullField object.

$item = new InventoryItem();
$item->internalId = "xxxxxx";
$item->nullFieldList = $nfl;

After that, you just perform an update on your record like you would any other record in NetSuite.

 

NetSuite Customization Dos and Don’ts

One of the best, most awesome features of the NetSuite platform is the ability to easily customize it to meet your business needs. The customization features go far beyond simply adding a custom field here and there and updating custom data entry forms. You can also create entirely new add-on business applications that are integrated into the base ERP system. It’s truly amazing.

It’s also one of the most misused features of NetSuite.

I say this because the base, out-of-the-box functionality is very deep and well designed for several verticals – many, many businesses should be able to realize efficiencies and benefits with little or no customization at all. Of course, there are also many businesses that have unique business models and/or processes that add value to their organization where it makes sense to customize.

With this in mind, here are some dos and don’ts when customizing NetSuite.

DO – make an effort to thoroughly understand the functionality you are trying to create with a NetSuite customization. Similarly, you must thoroughly understand the base NetSuite features and functions. Can standard NetSuite support your business need? Or, can your business process be tweaked to make it a better fit within NetSuite?

DON’T – jump to a false conclusion that NetSuite does not support a perceived functional gap between your business requirements and proceed to embark on a complicated customization project. Perhaps a feature is not enabled within NetSuite, and once enabled, your functionality gap will be filled.

DO – conduct a cost/benefit analysis that compares the benefit of customization to the costs of customization (hint: costs can be measured in more ways than just out of pocket consulting fees). Even if it’s just a back of the napkin analysis, you may realize the “must have” functionality has costs that outweigh the benefits.

DON’T – hire an inexperienced consultant or programmer who hasn’t worked with NetSuite should you decide your functionality gap must be filled by customization. I’ve heard one person in the NetSuite development and consulting community call NetSuite the Ferrari of ERP systems. You wouldn’t take your Ferrari to Jiffy Lube for an oil change, would you? No. You take it to a Ferrari specialist.

DO – spend the money to purchase a NetSuite sandbox account for creating and testing your customizations. Developing in a production environment is a recipe for disaster. Furthermore, your sandbox is a perfect environment for training end users.

NetSuite Web Services Performance – Upsert vs UpsertList

I’m in the middle of a 400,000+ SKU data load in preparation for a NetSuite launch. I’ve chosen to use web services rather than CSV data imports due to the large number of items I need to get into NetSuite. Additionally, I have several custom record types with even more than 400,000 records to import. CSV just isn’t going to work for me.

I coded up some data imports using the phpToolkit, and used the upsert method for each record. Performance with the 1-record-per-call was adequate – anywhere from 1-3 seconds for an upsert. Out of curiosity around improving the upsert performance, I decided to batch up requests with an array of item records and call the upsertList method.

There is a limit of 100 records you can batch with any of the List methods, so I started there at the upper limit. Performance was better, but I had expected a bigger improvement than I was seeing, so I went to the other extreme – 10 records in a batch. This seemed to perform better than 100, with 2 upserts per second.  After trying 20, 30, then 40 records in a batch, I settled on 40 as the Suite spot, so to speak. I was able to finally get 4-6 records to process in 1 second.

My experiment is certainly not scientific – there are all sorts of variables that account for variances – the time of day the tests were run (nights are always better than days), varying networks the requests come from (my home office broadband isn’t shared by 100 other people in an office), etc.

Also, performing upserts on inventory items is much different than performing them on custom records, as custom records have no built-in logic to perform. Standard NetSuite objects may have all sorts of functionality to perform, so performance increases quite a bit on custom record types that have no scripting attached to them.

But basically, I’m sticking with my 40 records in a batch because the anecdotal evidence says that performs the best. If you see results that are different than mine, I’d love to hear about them.

Over the Air HDTV with Antennas Direct ClearStream 2V

I’ve written a bit about my cord cutting experience – ridding myself of $100+ DirecTV bill.  Part of my strategy included installing an HD antenna so that I could receive over the air HD programming for local channels.

I am happy to say that the Antennas Direct’s ClearStream 2V has been great.  I’m about 45 miles from downtown Chicago, and I receive all local channels well.  I even split the signal between 3 HD televisions in the house without an issue.

The only real issue I have has nothing to do with the antenna itself.  My original plan was to mount the antenna on the existing mast of the DirecTV dish on my roof.  The problem is that my roof line is very, very high – I don’t have a ladder tall enough to get up there! Plan B was to mount the antenna in the attic and connect to a run of coax that home runs into my structured wiring cabinet in the basement, where I would redistribute the signal to the 3 rooms that require it.  However, the coax was in an area I could not get to…

I settled on installing the antenna directly to the back of a TV on the upper level of my house, splitting the signal between the TV and running the other output back through coax in the wall. I was able to redistribute the signal as planned from the wiring cabinet in the basement.

I’m looking forward to watching the Chicago Bears in HD tomorrow without the use of a satellite box.

Roku Off to a Great Start

My cord cutting experiment to replace DirecTv with Roku is off to a nice start.  I ordered 2 Roku XD boxes over the weekend, and they arrived from California today!  Not too shabby. I thought I had a couple of HDMI cables laying around, but I thought wrong.  Will have to go buy a couple of short cables to get all boxes hooked up.

In my planning to cut the DirecTv cord, I also determined I needed to get local digital TV channels so I can watch the Chicago Bears games on Sunday afternoon.  I’ll be up on a ladder next weekend taking down my dish and attaching an HD antenna.  We’re almost there…

Next task is to find the programming the family wants and get all our iPhones/iPads loaded up with the Roku remote control app.  I think there is a significant opportunity to blog about finding content – what to watch, where to find it, sharing reviews and recommendations, etc.

I’m optimistic so far.

Cutting the DirecTv Cord – a Roku Experiment

OK. Gonna try to cut the DirecTV cord and get rid of $150/mo bill.

Step 1: Get Roku devices for each TV. I already have 1 for the family room – a good place to start.  There is a special this weekend for $20 off.

Step 2: Get an HD antenna and hook it up to the old mast that supported the satellite dish.

There’s structured wiring in my house for cable and internet, so no running cables to each room or using wi-fi. That’s a huge bonus.  The last thing I want is 3 TVs trying to stream via wi-fi while I’m trying to work.

Making the change to Roku/Over-the-air-HD should pay for itself in month #3.  This is the best solution for being able to watch local news, Bears games, and Bulls/Cubs/Sox games that are on WGN or WCIU.

If MLBtv ever gets its online act together so I can watch “local” games, maybe I buy an MLB subscription on the Roku if I get desperate. Since the NHL is kaput, I feel better about not having Comcast Sports Net this winter.

After 6 months of trying this, if it sucks, DirecTV can have me back by bribing me with a better deal on service.

To ToolKit or Not To ToolKit…

That is the question. Whether it’s nobler in your mind to struggle with the learning curve of SuiteTalk and the SuiteTalk Schema Browser, or to suffer the slings and arrows of those who say, “You’re using C#?!?”.

To REST. To REST.  SOAP no more. And by REST we end the heartache.

Everyone knows I’m a huge supporter and champion of the NetSuite platform.  There really isn’t anything you can’t do with it, whether you want to create custom functionality or integrate it with 3rd party systems, which I’ve done a ton of.  My method of choice for integrating other systems with NetSuite used to be SuiteTalk, their web services API.  And my programming language of choice was php using NetSuite’s php ToolKit.  I was self-taught, and had a great time pushing and pulling data all over the place.

However, it’s not easy to learn and deploy SuiteTalk applications.  This is not a complaint or criticism – every system of the size and complexity of NetSuite is going to require a significant learning curve. You’d expect the same from SAP, Oracle, or any other major ERP provider.

But that was then, before the days of NetSuite RESTLets.  It’s all so easy now.

RESTLets are custom REST interfaces to and from NetSuite, and they’re super slick and super easy to create.  If you know JavaScript and the SuiteScript library of NetSuite functions, you’re pretty much set to go.  And if you’re a php kind of person, throw in some standard and simple cURL functions, and you’ll be off to the races.  JSON or XML input and output – not a problem.

RESTLets are now my preferred integration option.

Two Level Deep Join in NetSuite Saved Searches

I love the saved search capabilities in NetSuite – there’s just a ton of functionality in this part of the platform, and you can do incredible things with it.

Generally, when you specify search criteria or results columns, the NetSuite saved search UI provides you with the database fields for the table you are searching, plus the database fields for directly joined tables.  But what do you do when you want to join another database table to the first joined table and use fields in that table for not only your result set, but as criteria as well? Using the fields in the result set is standard functionality, I had not really found a way to do this for search criteria until today.  Below is the basic data model I was working from.

The answer to the problem starts with creating additional custom fields on the Location record that reference fields in the Custom Record. I’m going to assume you already know how to join the Custom Record in the data model to the Location record – that’s fairly straight forward. But those fields in the Custom Record… how do you return those in your saved search?

Define custom field(s) on the Location record for the field(s) you’d like to access and make sure the “Store Value” check box is left unchecked.  You don’t want to store the field data along with the Location record, you only want to reference it. By utilizing the Source List and Source From fields on the Sourcing and Filtering tab on each custom field, you can define which field you want to reference.

Let’s say your newly created referenced custom field from the Custom Record is called “foo”.

Now, if and when the joined Custom Record is changed, the value of “foo” changes as well, and you can easily access this field in the saved search’s results, just like you can access any of the joined table fields.

However, using “foo” in the saved search’s criteria is another matter… Unlike in the search results tab, where the custom field is presented to you to choose from in the list of available join fields, you won’t find “foo” available for selection.  To get at that field, use a Formula field and cheat a bit.

The standard syntax for accessing joined fields in a formula looks like this – {table-name.fieldname}.  All you have to do is specify your custom field. So in our case, the field “foo” would be referenced as {location.foo}. You can then easily supply criteria as necessary.

In my use case, “foo” is a date, and I want to see all transactions where the Transaction Date (called “trandate” in the database) is less than the date in “foo”. I chose a Numeric Formula field and subtracted the Transaction Date from “foo” using “{location.foo}-{trandate}”, then specified “greather than 0″ for the result.

Worked like a charm.

 

A Vision for NetSuite eCommerce

Much has been made of NetSuite’s Suite Commerce, their new eCommerce offering.  My understanding is that the new framework addresses many of the issues that their eCommerce customers have raised over the years.  I say “my understanding” because I really can’t get my hands on the actual Suite Commerce product.  I can only repeat what NetSuite tells me (and everyone else) is in the new product offering:

  • Complete control of every pixel on every page
  • Completely customizable checkout – anything from a 1 page checkout to an AJAX powered checkout
  • Server-side scripting
  • Blazing fast page loads with help from the NetSuite CDN
  • Ability to design for all devices – iPhone, Android, iPad, etc.

But there’s still this black hole in my personal NetSuite knowledge base, a knowledge base that I think is probably one of the more robust here in Chicago, but maybe in the entire USA. I’m usually a fairly modest person, but I haven’t seen too many people that know more about NetSuite than I do (those working internally at NetSuite excluded, of course).  Anyway…  that black hole is Suite Commerce.

Perhaps NetSuite is playing their Suite Commerce cards very close to the vest while getting the product offering off the ground.  I can only speculate on that matter.  I can certainly see how the product would work if there was an architecture in place that allowed for creation of item category and item detail templates driven by a combination of HTML/JavaScript and a server-side pre-processor for embedded SuiteScript, much like you’d see in php, ASP, and other dynamic web languages.

Really, any sort of page could be created via this mechanism – it doesn’t have to be item or category related.  For example, multiple brick and mortar location retailers could generate a page that pulls locations from NetSuite and displays them on a map. For mobile users, it would be great if a store locator could access your current location and direct you to the nearest physical location.

It’s been almost a year and a half since I sat down with the NetSuite eCommerce folks and shared my wants and vision for my ideal NetSuite-enabled eCommerce platform, and it appears to be nearly within my grasp.