OT Excel SGC-Import Task Fails Validation with "Duplicate serial number found" for Batches > ~105 Re

[媛宫]

Affected Versions: Yokohama, Australia (and likely others)

Description: 
When using the OT Excel SGC-Import Task within the Industrial Workspace to upload OT control module data, the validation process incorrectly fails for files containing more than approximately 105 records. The system reports a "Duplicate serial number found" error for all records, marking the validation status as invalid and preventing the import.

However, the serial numbers in the test data are all unique and do not contain duplicates.

This issue has been reproduced in both Yokohama and Australia instances.

Steps to Reproduce:
1, Navigate to Workspaces > Industrial Workspace > OT Excel - SGC Import Task.

2,Click New to create a new import task.

3,Upload an Excel file containing approximately 105+ records for OT control modules.

4,Click the Validate UI Action.

5,Observe that the validation status becomes invalid.

6,Check the validation messages; each OT control modules record is marked with Duplicate serial number found. Even when all serial numbers are unique. The import process cannot proceed.

Note:  The same process works successfully when the file contains fewer records (e.g., < 100).

Expected Result: 
The validation should pass if all serial numbers are unique, regardless of the total number of records being imported.

Root Cause Analysis:
Upon investigation, the issue was traced to the Script Include: SGOTExcelStagingAssetValidationProcessor.

Within the functions isUniqueControlModuleField and isSlotAndRackUnique, the following logic is used to determine if a duplicate exists:

javascript
if (records.getRowCount() > 1) {
// Treat as duplicate
}
The method GlideRecord.getRowCount() returns the total number of records being imported (e.g., 120) instead of the count of records returned by the specific query (e.g., the number of records that share a serial number).

Test Fix:
A test was performed by modifying the logic to check for the existence of a subsequent record after the first, which is the correct way to test for duplicates in a GlideRecord query:

javascript
// Proposed Fix
if (records.next() && records.next()) {
// Treat as duplicate
}
With this change, the validation error disappears.

Request for Assistance:
1, Confirmation of the Bug: Can the ServiceNow development team please confirm this as a known product bug?

2, Imported Data Limit: What is the intended maximum number of records that can be processed in a single import via the OT Excel SGC-Import Task?

3, Solution Strategy: The current workaround (modifying the SGOTExcelStagingAssetValidationProcessor) is a direct customization of an OOTB script include. Directly modifying OOTB artifacts is not recommended as it will lead to Skipped Updates during future instance upgrades, potentially causing the fix to be lost or resulting in merge conflicts.

4, Is there an alternative, upgrade-safe method to resolve this issue? For example, could this be addressed via a business rule, a UI Action override, or an extension of the script include that would not be overwritten during an upgrade? We are looking for a solution that allows us to fix this bug without creating future maintenance overhead for our customer's instance.

Additional Information:
Test Data: An Excel file containing the test data with unique serial numbers will be attached to this ticket.

Screenshots: Screenshots of the validation error and the code section in the Script Include are provided.

Thank you for your support. We look forward to your guidance on resolving this issue.

[Tanushree Maiti]

Hi @媛宫 

You can Raise a case with your ServiceNow vendor.

On the backend, configure the Serial Number field as a Coalesce field in the Transform Map. During the import, ServiceNow will search the target table for a matching Serial Number.

If a matching record is found, the existing record will be updated; if no match exists, a new record will be created. This ensures that duplicate records are avoided while allowing new records to be inserted as needed.

Refer: Updating records using coalesce