How to Find Duplicate SKUs in NetSuite by Excluding Parent Prefixes?

While NetSuite's behavior regarding duplicate SKUs across different item record types can vary, current NetSuite documentation strongly advises maintaining unique SKUs across all item types to prevent potential data integrity issues.To identify items with duplicate SKUs, particularly when item names include parent prefixes, a NetSuite saved search can be configured as follows:

1. Create a new Item Saved Search.

2. Define Columns:

   • Add a 'Formula (Text)' column. This column will strip any parent prefixes from the item name, allowing for a clean comparison of the base SKU.
   • Set the 'Summary Type' for this column to 'GROUP'.
   • Enter the following formula:

REGEXP_REPLACE({name}, '^((.)+ : )', '')

• Add an 'Internal ID' column. This column will be used to count the occurrences of each unique stripped SKU.
• Set the 'Summary Type' for this column to 'COUNT'.

1. Apply Filters:

   • The search filters should target specific item types and identify groups with more than one internal ID. The filter criteria would be:

[["type","anyof","Assembly","InvtPart"],"AND",["count(internalid)","greaterthan","1"]]

This translates to:

• Filter by 'Type' to include 'Assembly' and 'Inventory Item'.
• Apply a summary filter on the 'COUNT(Internal ID)' to show only results where the count is 'greater than 1'.This saved search configuration effectively groups items by their base SKU (after removing parent prefixes) and highlights any instances where that base SKU is used by multiple items.For programmatic implementation of such a search, the logic involves defining search columns and filters. The provided example uses SuiteScript 1.0 functions, which are considered legacy. For new development, SuiteScript 2.x is the current best practice. The conceptual definition of the search in SuiteScript 1.0 would look like this:

var cols = [];cols[0] = new nlobjSearchColumn('formulatext', null, 'GROUP');cols[0].setFormula('REGEXP_REPLACE({name}, '^((.)+ : )', '')');cols[1] = new nlobjSearchColumn('internalid', null, 'COUNT');var filters = [    ["type","anyof","Assembly","InvtPart"],    "AND",    ["count(internalid)","greaterthan","1"]];var results = nlapiSearchRecord('item', null, filters, cols);

This script snippet illustrates the underlying search definition logic, where nlobjSearchColumn defines the columns and their summary types, and nlapiSearchRecord executes the search with the specified filters and columns.