Changing transaction coding via a CSV import template is critical when the volume of transactions is too high to do this task manually. Two use cases for this skill are updating the department coding for all transactions with a specific vendor for comparative financials or adding a class code for transactions migrated from QuickBooks during a NetSuite implementation. This blog post will walk through the steps to complete this process.
We need to build a transaction saved search with the transactions we want to modify. To do this, navigate to Reports->New Search and select ‘Transactions.’ On the criteria subtab, select the subset of transactions you need to clarify. If you aren’t sure or just want to look at everything, just leave this section blank. In my example, let's review all of the Q1 2021 transactions with the vendor ‘Charles River Lab.’
On the results subtab, you’ll want to include, at a minimum, the Internal ID, Line ID, and the Transaction Type. The line ID is included because this dictates the line number on the journal entry or vendor bill being modified. The type is included because this will determine the type of CSV import template is used when uploading the file into NetSuite. It would be best if you also considered any other fields that might be relevant for your update. In my example, I’m including the Vendor Name, Date, Account, Memo, and Department fields.
Export your saved search to Excel. In Excel, we can manipulate the data easily. For example, in my file, I’ve added the new department I want to reclass my transactions. Notice that I am using the department’s Internal ID for the import. I find that Internal ID is the best way to ensure the import file works correctly.
The next step is to split the new data by transaction type. This is important because each transaction type has a unique CSV import page. I’d recommend creating a separate tab for each transaction type, along with a single tab with all the transaction types. See the screenshot below. You only need to keep Internal ID, Line ID, and the new segment value in the upload tabs. Save each tab as a separate CSV file. I’d also recommend splitting the files again with only one or two records as a test version first. This validates that the load worked and didn’t result in any unintended side effects, like triggering a journal entry approval workflow.
Finally, navigate to Setup->Import/Export->Import CSV Records to load the CSV files. Two other reminders:
- On the ‘Import Options’ page, be sure and select ‘Update’ instead of ‘Add.’
- If you are using Internal IDs to map to segment values, you’ll need to select the pencil icon and map to ‘Internal ID’ instead of ‘Name.’
You can rerun your search results and confirm that the transactions reflect the new data.
If your team is cleaning up imported data from a data migration with a CSV template, contact intheBlk consulting. We can help ensure your migrated data came over as expected and ties out correctly.