This article is relevant if you need to update the line items on a single NetSuite transaction.
Background
As a good business practice, my firm needs to review existing clients and determine where we need to modify our rate schedules each new year. Our normal practice is to invoice our clients weekly. However, some clients seek to be invoiced on a different interval.
Our team submits timesheets weekly. When those timesheets are approved, the time entries are effectively stamped with the price ({rate}) for each time entry. When we produce our invoicing, NetSuite takes the unbilled time entries and uses each timesheet entry as input to the native Invoice “Billable Time” record sublist.
When we produced invoices out of step from our weekly billing operations, many of the older “to be billed” timesheet entries had old prices. When we generated our client invoices, we noticed that many of the entries had the wrong prices. We were then confronted with the challenge of updating the invoices with the correctly updated prices.
Different Approaches to Update Transactions
In our case, one approach would have been to manipulate the time entry records to update the {rate} amount and then craft new invoices. However, we elected to work directly with the transactions versus indirectly with the time entries for reasons relative to already issued invoices and respective credit memos.
The general NetSuite Administrator approach is to export the data via a saved search, manipulate the data in a spreadsheet, and then use a CSV file to update the transactions. But as many NetSuite Administrators know, working with CSV can sometimes be troublesome, especially when working with the line elements.
In the case of the Billable Time sublist, there is an undocumented **doc field. Trying to update and reference the field returns the “Unable to find a matching line for sublist apply with key: [doc,line] and value” error message. Naturally, after more trial and error, one begins to look up SuiteAnswers and/or search the Internet to get to answers. Meanwhile, the clock is ticking…
Get Control and Use Client Side Script
In our case, it would be faster to craft a client script to get the job done. The client-side code pattern below can help you manipulate the lines on a transaction in edit mode right in the browser. Here is how it is done (click on images to help explain the practice).
- Edit Mode: in your browser, put the transaction in Edit, versus View, mode.
- Open Browser Console: in your browser, there should be a Developer Tools menu. We need to get to the “Console” area where you can interactively work with the browser objects.
- Cut-and-Paste Code: after modifying the code below for the pattern you need, cut-and-paste the code and hit Enter to have it execute.
- Review and Save: after the client-side code executes, view the transaction to assess if the updates are correct. If so, just Save the record. Done!
NetSuite Interactive Client Side Script Pattern to Edit Transaction Line Values
The following pattern can be used for inspiration for you to craft your transaction script. Use the NetSuite Records Browser to help you get the sublist and field references. Sometimes, you need to mouse over the tab on the sublist to discover the name of the list to reference in your client-side script (click on the image for an illustrative explanation).
//modify the SuiteScript 1.0 code snippet, copy and paste in console and hit enter to watch it work
// the standard sublist name is 'item'; here we use 'time'
var line = 'time';
//our transaction had 286 lines to review and edit
var x = 1
while (x < 287) {
nlapiSelectLineItem(line, x);
var r = nlapiGetCurrentLineItemValue(line, 'rate', x);
var n = r;
switch(r) {
case '100.00':
n = '105.00'
break;
case '150.00':
n = '155.00'
break;
default:
// add any value here if don't get a match
}
console.log(x + ': current value:' + r)
console.log(x + ': new value:' + n)
nlapiSetCurrentLineItemValue(line, 'rate', n);
nlapiCommitLineItem(line);
x++;
}
With the code pattern above, you can manipulate values such as item references, quantities, descriptions or other. You have no risk to the record until you click the Save button.
Tips and Tricks for Modifying NetSuite Values
In practice, when the general common sense approach (i.e., NetSuite CSV file imports) is simply taking too long and presents too many challenges, we need to remember that NetSuite is just software — we can take matters into our own hands. Some readers know we have a framework for handling complex NetSuite file imports which can be very nice when we are regularly updating the same data areas.
This discussion is similar to my 2016 article, How To: Quickly Update a NetSuite Read Only Field, which comes in handy when you just need to get the job done.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like to work with a team of innovative NetSuite professionals, 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
