Data Migration Sequence
If you are seeking to implement NetSuite from a legacy system, data conversion is a topic you will care about. While this article does not go into cutover strategies, the sequence you choose to push data into NetSuite is important due to interrelated dependencies. Also, often the discussion about parallel processing comes up. See my previous article, “All-Talk: Parallel Processing before Going Live!”, on what I have observed in the last 25 years of doing this kind of work.
This article assumes that you are seeking to get the general ERP system up first. This means the accounting / finance and inventory management capacities. It also assumes that your data is “clean” which is rarely ever the case. Data conversion is always risky to the implementation project plan and you should plan on many attempts before you get it right. As a NetSuite Systems Integrator, we will work with our clients in a development account to practice the data import without polluting the production account.
The basic approach is to use NetSuite’s built-in comma separated values (CSV) file import tool to push data in. Here is the suggested data conversion sequence:
- Chart of Accounts: Get clear on your future financial reporting structures leveraging class, department and location dimensions. Create a cross reference map, as needed, from old account number to new account number.
- Vendor Master: Get the basic address, account and other information organized. Generally, it is best to only bring over vendors for which you have active obligations.
- Item structure: Determine your item structure leveraging hierarchies, item groups, kits, and, where needed, assemblies. The design of this structure is important for your long-term utility and future analysis capacity. Map your items to your new general ledger accounts and relate vendor part number information. Cost and Price information can come later.
- Customer Master: Like the vendor master, bring over all the relevant elements, such as contact information, and address. Usually only active customers you intend to market to is important. You may need to map customers into categories to address special pricing schedules. These concerns though are not germane to this sequence and can come later.
- Inventory Balance: The main point is to get your inventory quantities correct. Ideally, all cost related item information will have already be added to the items so that you can constitute a total inventory cost valuation to check your work. Understand the general ledger impact as you true up your inventory. Generally, you will need to return to this effort during the actual cutover.
- Sales Orders: Add Sales Orders that for your backordered customer orders to allow you to start fulfillment when you go live. Again, these transactions need to be considered in your cutover strategy.
- Customer Invoices: Constitute open invoices for future cash receipts. Point the line item work (which credits the general ledger) to a non inventory account to minimize impact to your inventory balance. The invoices will constitute accounts receivable which can be checked for reasonableness. Anticipate that you will need to journal entry the resulting credits to a proper target account as you work on your trial balance.
- Vendor Bills: Enter vendor bills on a general ledger account that has no inventory effect. Here, line items produce debits that will need to be addressed in your trial balance work. Like the Customer Invoices before, you should be able to tie out the resulting accounts payable balance.
- Trial Balance: Enter the Trial balance information at cutover date. Some implementations perform this part of the sequence right after the Chart of Accounts is entered. That is fine. The primary concern is that the impact of populating subsidiary ledgers systems, such, AR, AP and Inventory has been accounted for.
- Prior Period Balances: Bring over prior period balances by period. This can happen after you go live but many like to perform it right after the Chart of Accounts is entered. Think through the impact to your cutover Trial Balance. NetSuite will roll forward old balances into the future — hence consider this as you import journal entries to constitute balances.
- Other Sub Ledger Accounts: Constitute other sub ledger balances, such as equipment, prepaid, and other accruals assuming you have separate sub ledger systems in NetSuite or elsewhere.
Data Sequence Considerations
How can I customize or setup a item master hierarchy? as my client is asking to setup a 3 to 4 level of product hierarchy and I don’t know how to do it in Netsuite.
Hello Arthur,
The hierarchy is controlled via the parent item / subitem reference points. You will need to have the parents in place first. Then you need to import the children with references to the parent’s internal ID.
Marty
I want to get the vendor opening balance in suitescript. How can I get that?
We are using currently custom vendor form. It does have a opening balance field enabled(‘view’ is ticked) when i customised the form but I cannot see it on the form when it is in view or edit mode? I dont know why this happens?
Currently the vendor opening balance can be seen on one of the Netsuite reports but we cannot take values from there, can we?
Hello Rahul,
Might there be a permission issue? Can you see it as an administrator? Try adding &cf=-20 at the end of your URL to get to the standard NetSuite out-of-the-box format. Does that reveal the information?
Marty
Hi Marty,
Can we upload images to netsuite from suitelet or RESTlet ?
Yes, this can be done through programming.
Hi,
I observed your sequence of Data Import. It looks good, but i am thinking that we couldn’t cover the Budgets,Revenues in the importing part. is that any other way to cover all those things?
Please tell me the migration steps with including Imports and Manual creatings!!
Thanks.
Hello Kumar,
Budgets must come after the chart of accounts and surely depends if there are other dimensions needed, such as item or customer. It usually is very late to the game.
Revenues are all part of the story of bringing over historical data. This can be achieved via transactional data import (more difficult) or only the balances at the end of the period (more common).
Marty
Hello Kumar,
If you can’t use the built-in NetSuite CSV tools, we have a tool called RIEM (Record Import Export Manager) that can get any data imported that is accessible via script.
https://blog.prolecto.com/netsuite-bundles/?creid=16&rid=126
The actual work all depends on your planning and record import attack strategy.
Marty
Thanks Marty.
We have made many imports succesful through CSV Files. when implement the new project, the data needs to be migrated from existing system to Netsuite sandbox (for testing) through CSV imports and Manual creations. here, we need to get correct Order/Sequence for data at both the levels i.e. CSV & Manual creations.
We observed some sites and they listed as COA,Masters,Items,Transactions. but we didn’t get clear picture on this. Could you please explain the Recommended data sequence in both the CSV & Manual creations one by one.
It is really very helpful us.
Thanks,
Kumar.
Kumar,
Every implementation should have a thoughtful plan for the order of dependencies. The outline I gave here in this article should help kick start the effort. We generally have a planning working session with our clients when we are engaged to help them. We then develop a document for the actual sequence. I would not be able to comment intelligently unless we did the real work to understand your entire plan.
Marty
hello,
I’m researching the best way to extract data from NetSuite for our Data Warehouse. I have come across the Rest Web Services, however, a large number of the objects we need are listed under Beta, but it appears those are fully available via the query service.
With that being said, when submitting a simple “select * from” query to the API I get a response which appears to exclude any of the fields that are null. I’d like all columns from said object to return.
Am I missing something simple?
Thanks!
We can shape these templates to return any type of response you want. If you want a query service that will allow a SQL statement to return, that is easy enough. This article is relevant to turn this into an ongoing operation:
https://blog.prolecto.com/2020/07/05/solve-for-high-performance-netsuite-data-exports/
Marty