Use a saved search to import historical TBs re-implementing NetSuite

Introduction

A NetSuite-to-NetSuite transition is the third-most common NetSuite data migration project I’ve seen. Here are a few scenarios where this ERP transition is relevant:

• A client has an older instance of NetSuite without OneWorld. For example, I had an Illionis-based IT reseller in this exact situation. They had expanded their business and now needed multiple subsidiaries with OneWorld.

• A client acquires another company that is running on NetSuite.

• A client wants to implement a new instance with additional functionality. For example, I had an Arizona-based solar client implement Blu Banyan’s SolarSuccess program. This required them to reimplement NetSuite.

When reimplementing NetSuite, the data migration strategy is the same as if you were moving from any other system. We must import master records, historical trial balances, and open transactions. This article discusses building a NetSuite saved search to prepare the historical trial balances during the data migration project.

Let's dive in.

Prerequisites before importing historical trial balances

Before we get started with loading the historical financials, you’ll want to complete the following:

• Create the master records in the new NetSuite instance. Ideally, set the new NetSuite instance's external ID value to the old NetSuite instance’s internal ID value when creating these master records. This simple trick will make the mapping exercise significantly easier.

• Generate the fictitious “AR Import Customer” and “AP Import Vendor” for tagging journal entry lines impacting Accounts Receivable and Accounts Payable accounts, respectively.

• Confirm that the accounting periods you are loading in the new system have been created and are open for the new subsidiary.

• Validate that the account periods in the old system are locked to avoid changes to the historical financial data.

Once these steps are complete, you are ready to prepare the journal entry import template.

How to create a saved search with the historical trial balances in NetSuite

1. Create a transaction saved search

Navigate to Reports → New Search. Scroll down to “Transaction.” From here, click “Create Saved Search.” In the search title, give the report a meaningful name like “Summary TBs by Month.”

2. Add the appropriate filters

You will need to add the appropriate filters in the Criteria subtab. Set the “Posting” criteria to “Yes” to exclude any non-posting transactions in the search.

NetSuite generates the financial statements based on the posting period, not the date. We will want to select the Accounting Period range. This step is unnecessary if you are importing financial data for all periods. There are a few options to add this filter:

• Select the “Period” criteria and manually select each fiscal year.

• Select the “Accounting Period…” criteria, select “Internal ID (Number), and select the range for accounting periods to be included. You can find this on the Manage Account Periods page. You’ll need to have the show internal ID settings checked. And, this only works if the accounting period internal IDs are sequential.

3. Add the appropriate columns

Now, set the appropriate columns in the “Results” subtab. At a minimum, you’ll need to include:

• Period

• Account

• Debit

• Credit

• Any other segment values you want to migrate, including Department, Class, Location, and Subsidiary

Again, because NetSuite prepares the financial statements based on the accounting period, we need to group the data by the period. To do this, find the “Summary Type” dropdown and set all the fields to “Group” except for the “Debit” and “Credit” fields. These fields should be set to “Sum.”

If you included the legacy NetSuite instance’s internal ID as the external ID in the new NetSuite instance, you’d also want to include the internal ID for the account and segments.

Once you’ve finalized the columns, click “Save & Run” on the search and export the results to Excel.

4. Prepare the NetSuite CSV import template in Excel

Begin to prepare the journal entry import file. There are several changes you’ll need to make in the file:

• Add a date column, based on the “Period” column.

• Map the segments. If the external ID was included in the new instance, you can skip this step because the legacy internal ID will match the new external ID.

• Add the “Import AR Customer” and “Import AP Vendor” names to the row for any line impacting an Accounts Receivable or Accounts Payable account type. This will prevent a “No Customer/Vendor” line on the subledger reports.

• Add a document number and an external ID for each journal entry. You can use a CONCATENATE formula in Excel to create this quickly. I like to use the nomenclature of “[MONTH]-[YEAR]-[SUBSIDIARY ID]-TB” to ensure that each entry is unique.

After making these changes, validate that the debits and credit columns sum to zero by the document number. Save the file as a CSV file.

5. Upload the entries into NetSuite

You are now ready to upload the journal entry import templates to NetSuite. You can navigate to the upload page at Setup → Import/Export → Import CSV Records and follow the steps. If you run into errors in the upload process, check out my article on solutions to common CSV import errors. Once all the entries are loaded, validate that the balances came over correctly by comparing the NetSuite trial balances in the two systems.

If you need additional assistance importing journal entries into NetSuite, consider enrolling in my saved search course for accountants.

At this point, you are ready to load the open accounts payable and receivable transactions. Click the links to see my guides on completing each of these processes.

Closing

Data migration can be a challenging component of a NetSuite implementation, even when moving from one NetSuite instance to another. Contact us today if you need an expert to provide an extra set of hands or want to hear about OptimalData’s service offering around importing detailed transactions.