This article is relevant if you need to insert or update NetSuite tables, but the record (or a portion of it) is not supported natively.
Background
At the end of 2021, we needed to produce record updates to our professional service rates in our NetSuite Systems Integration Practice. Because we use NetSuite’s Service Resources Planning (SRP aka Advanced Projects), the pricing information we need to update is located in a Project Resource Assignment sublist of a Project Task record. While NetSuite supports a CSV update for a Project Task, it does not support getting at the Project Resource Assignment sublist.
As many know, the NetSuite platform has areas for which the native CSV tools will not reach. Not reaching certain record areas can be frustrating when you want to get a one-time job done. It might make sense to invest in a SuiteScript solution when updates are planned to be more frequent — but must we?
Solved Reaching Inaccessible NetSuite Records
The good news is that my firm has solved this challenge without scripting. In 2020, I wrote an article describing out to Fully Automate Complex NetSuite Data Imports. We call the framework the Record Import Export Manager (RIEM). It uses a NetSuite plug-in framework to map the logic from CSV (or XML / JSON) files to the nooks and crannies of any SuiteScript accessible record.
This framework required SuiteScript skills to program the plug-in — until now! Out of the necessity for many of our team’s Business Analysts to take care of client situations for updates without the need for SuiteScript programming, Boban D., senior technical analyst member, wrote a generic plug-in that intelligently updates NetSuite Records.
Identifying the NetSuite Fields on a Record
The key to the solution is to create a CSV (or XML/JSON) file with headers that map the field label name or the field ID. I like to work with field IDs as they are uniquely identified and avoid considerations where there may be a space in the label name. To identify the field IDs for a NetSuite field, see my 2012 article, Showing NetSuite Internal IDs and Field Names.
However, the field IDs in sublists are more tricky to figure out. Using the Project Task as our sample record in question, there are a couple of ways to get at the field names:
- XML=T: use the trick to append “&xml=t” to the end of any NetSuite URL to see an XML record representation. Study the structure to assess the sublist and field names.
- NetSuite Field Explorer Add-in: if you install Michoel Chaikin’s Chrome Add-in, you get a clean record representation to inspect the field names and structure. Thank you Michoel!
In the case of the Project Task structure, we explore the record and discover that we need to get to the “assignee” sublist and update the “price” record. To do this, all we need to do is create a CSV column heading called “assignee.price”. Since we also have to identify the correct row to update, we also reference the “assignee.id” field.
All the other commonly considered fields on the main (header) of the record are referenced. Thus, if we want to update the Start Date of the Project Task record, we can reference “startdate”. Indeed, we need to identify the project task record, so we reference “internalid.”
The good news is that you can even update fields that might otherwise be marked inline text on the form, which the standard CSV mechanism forces you to create another form definition that makes the field definition “normal” for edit.
Click on the images to better understand the field name discovery process.
Drop the CSV File in the Cabinet To Import
In the discussion of automated complex NetSuite data imports, I show how you set up a job with pointers to a NetSuite folder to pick up the data. We follow the same pattern here. Once set up, we drop the CSV file into the file cabinet and let the provided scheduled (or on-demand) scripts scoop up the data to start the work.
The CSV file is parsed and then placed into respective custom record structures allowing you to see the progress of the data imports.
Other Challenging NetSuite Records to Updates
The tool is designed to help you reach other records and substructures that may otherwise be difficult to insert or update data. A couple of records come to mind:
- Item Fulfillments: create or update item fulfillment records, including package sublists.
- Vendors and Customers: consider updating the currency and subsidiary sublists of vendors or customers.
The idea is that if we can see the sublist in the field explorer, we can create or update that data in the tool. Note, I should be more conservative — we should be able to update those fields; I have learned to be careful not to be 100% certain with NetSuite technology until we ground it out in tests.
Get the Record Import Export Manager to Solve NetSuite Update Challenges
Like all the algorithms we have created (many published here), we give these tools to our clients license-free. The tools are an illustration of our superior capacity to understand business system challenges and solve concerns to capture the promised value of your NetSuite investment.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you need to solve a specific NetSuite record update challenge, let’s have a conversation.