Tooling BOM synchronization API creates duplicate part entries despite unique checks

PLM Windchill
, , , , , , , ,
1

We’re experiencing data integrity issues with our tooling BOM synchronization process using REST API v2 in Windchill 12.0. The API endpoint creates duplicate part entries even though we’re implementing part number normalization and duplicate detection logic.

Our integration syncs tooling BOMs from manufacturing execution system to Windchill. We query existing parts by number before creation, but still get duplicates with slight variations (trailing spaces, different cases). The BOM reconciliation process then fails because it can’t match parts correctly.

Here’s our current duplicate check approach:


GET /Windchill/servlet/odata/ProdMgmt/Parts?
  $filter=Number eq 'TOOL-12345'
// Returns no results for 'TOOL-12345 ' (with space)

We need fuzzy matching capabilities to catch these variations during part number normalization. The duplicate detection should handle case-insensitive comparison, whitespace trimming, and special character normalization.

How can we implement robust duplicate detection for BOM reconciliation? What’s the best approach for part number normalization in REST API queries?

2

The OData filter is doing exact string match. You need to normalize part numbers before querying. Trim whitespace and convert to uppercase in your integration code before making API calls. Also consider using $search instead of $filter for fuzzy matching capabilities.

3

Part number normalization should happen at creation time. Add custom validator in Windchill that normalizes part numbers automatically. This ensures consistent format across all part creation methods - UI, API, or bulk import. Prevents duplicates at source rather than trying to detect them later.

4

For fuzzy matching, you might need to retrieve all parts with similar prefixes and do matching in application code. OData doesn’t support advanced string similarity functions. We use Levenshtein distance calculation to detect near-duplicate part numbers with typos or variations.

5

Your duplicate detection issues stem from multiple factors that need comprehensive solution addressing part number normalization, fuzzy matching, and BOM reconciliation:

Part Number Normalization Strategy: Implement normalization at multiple layers. First, in your integration code before making REST API calls, apply consistent normalization rules:


function normalizePart(partNum) {
  return partNum.trim()
    .toUpperCase()
    .replace(/[^A-Z0-9-]/g, '');
}

This handles whitespace, case variations, and special characters. Second, implement custom validator in Windchill that applies same normalization on part creation, ensuring consistency regardless of creation method.

Enhanced Duplicate Detection: The OData $filter performs exact matching, which misses variations. Implement two-stage detection. First, query with normalized part number:


GET /Windchill/servlet/odata/ProdMgmt/Parts?
  $filter=toupper(Number) eq 'TOOL-12345'

Note: OData string functions may have limited support in Windchill, so normalize in client code instead. Second stage: if no exact match found, retrieve parts with similar prefixes and perform fuzzy matching in application code using Levenshtein distance or Jaro-Winkler similarity. Set threshold (e.g., 90% similarity) to catch typos while avoiding false positives.

Fuzzy Matching Implementation: For production use, implement caching layer that maintains normalized part number index. Use Redis or similar cache with normalized numbers as keys, part OIDs as values. When synchronizing tooling BOMs, first check cache with normalized part number. If cache miss, query Windchill REST API and update cache. This dramatically improves performance for BOM reconciliation with thousands of parts.

BOM Reconciliation Enhancement: Shift from part number-based matching to OID-based matching. Maintain mapping table in your integration database: source system tool ID → Windchill part OID. During BOM synchronization, look up OIDs from mapping table rather than querying by part number. This eliminates duplicate detection issues entirely. Update mapping table whenever new parts are created.

Preventing Future Duplicates: Implement part creation workflow that enforces uniqueness. Before creating part via REST API, always query with normalized number. If similar part exists (even with slight variation), prompt user to reuse existing part rather than creating duplicate. Add custom Windchill validator that checks normalized part numbers against existing parts and rejects creation if duplicate detected. This prevents duplicates at source.

Data Cleanup for Existing Duplicates: For existing duplicate tooling parts, implement cleanup process: identify duplicates using fuzzy matching on all parts, determine canonical part (usually oldest or most-used), update all BOM references to point to canonical part, and obsolete duplicate parts. Document cleanup in change order for audit trail.

This comprehensive approach addresses immediate duplicate detection needs while preventing future occurrences through normalization, caching, and validation at multiple layers.

6

We faced similar issues with BOM reconciliation. Implemented client-side normalization function that strips whitespace, converts to uppercase, and removes special characters before duplicate detection. Store normalized part numbers in custom attribute for faster lookup. Also added validation rules in Windchill to prevent parts with unnormalized numbers from being created.