This article is relevant if you need to retire or substitute a NetSuite item for another but you are concerned about preserving reporting.

Background

As many often learn when working with NetSuite after some time, defining your items carefully is important for good reporting and day-to-day use. Invariably, there may be times that we need to modify an item attribute and NetSuite indicates it is not possible as the system will not allow you to save your work.

The two classic situations are the following:

1. Item Type Change: an item needs to be modified from one type to another.
2. GL Account References: an item needs a different GL account pointer — but the accounting books are closed and NetSuite will not allow the change as all the related transaction references must be updated (yet, some references in 2021.2 release notes appear to indicate you can change values — I have experienced differently).

While there are other reasons (e.g., unit of measure changes come to mind), when encountering this change-item-structure concern, you are forced to make some decisions. It is easy enough to rename the old item — perhaps add the suffix “-old” to the item name and mark it inactive; then create the new item with the same name.  Yet, when you produce transaction-based item-level reporting, you now have two distinct item records which conceptually are the same.

NetSuite Best Practice for Preserving Item Level Reporting

When working with our clients that need to address this concern, we discuss the following enhancement to the NetSuite database and respective reporting practice.

Define New Custom “Item Reporting” Item Field

We first create an item-based new freeform text field in the NetSuite database. We apply this across all item types. The idea is that we need a bridge between the old-item name and the new-item name that shares the same information value.

We do not want the Item Reporting field to have a null value as we are going to use it for reporting. Thus, all new items created in the database will have the same item reporting name as the new name of the item. We can use a NetSuite default value definition to help us enforce this.  While you can set the item definition to indicate it is mandatory to contain values, you need to manually provide this information during edits whereas the sourcing value definition will take effect after the record is saved.

Click images to see the item definition suggestions.

Retire Old Item with Reference to New Item Name

We first need to retire the old item. To do so, we need to modify the name of the item to something that informs users not to use it going forward. This opens up the space so that we can create a new item (with the desired structure) with the old name. While there is no specific requirement here, our article example illustrates that we prefixed the old item to have “-old” in the name. Thus, item ABC becomes ABC-old.

You can mark the item as inactive so that it is not used in day-to-day operations going forward.

Click the image to see the item definition.

Create the Desired New Item

Now that the old item has made space for the new item, create the item with the property structure as desired. If you set up the system to automatically name the “Item Reporting” field to be the same as the item name as suggested, you are good to go.

Note, this approach assumes that you correctly set the item name right on Create. The formula field in the image above will not automatically update if you change the item name on an edit/save.  Exercise care in renaming the item going forward.

Update Existing Items with Item Reporting Values

With the new “Item Reporting” field in the database, we now need to update all the existing items to have an “Item Reporting” value. This effort is straightforward — you can use Mass Update tools, or utilize file-based CSV exports and imports to set the value of the “Item Reporting” field to be the same as the item name field.

You know you are complete when you run an item-based saved search and the value for “Item Reporting” is not null.

Leverage “Item Reporting” Field in Reports and Saved Searches

With the item database now updated, we can add the new “Item Reporting” field to reports and saved searches. The idea here is to use the “Item Reporting” in place of the standard “Name” field.  By referencing the new field versus the name field, the database will consolidate transactions to conceptually bridge transactions together as if they were the same item.

Click on the image to help convey the concept.

Appreciate NetSuite as an Adaptable Database Application

The great news about working with NetSuite is that you are never really stuck — the platform is designed to be enhanced to address business requirements. In this example, we used conventional database approaches to solve what appears to be a limitation. Yet, if you combine strong business operations and technology competency to your information management concerns, NetSuite allows you to innovate your way to solutions.

Our clients tell us that it is a joy to work with our team as we say “Yes” when others say “No”. 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 NetSuite professionals that are committed to high standards for care, 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