Genealogy tracking: Batch records missing parent-child links

MES Rockwell FactoryTalk MES
, , , , , , , ,
1

We recently imported 6 months of historical batch data from our legacy system into FactoryTalk MES 10.0, but the genealogy tracking module shows incomplete parent-child relationships. About 40% of batch records appear as orphaned nodes with no material consumption links.

The import mapping looked correct during validation - we mapped source batch IDs to target batch numbers and confirmed material lot references matched our current inventory structure. However, when we check the genealogy tree view, many batches show consumed materials but the parent-child links are missing.


Batch B-2024-1156: Status=Complete
  Expected Parents: RM-LOT-8834, RM-LOT-8835
  Actual Parents: None found
  Materials consumed: 2 items in transaction log

This is blocking our traceability requirements for customer audits. Has anyone dealt with genealogy data repair after bulk imports? We need to establish these links without re-running production.

2

I’ve seen this before with legacy data imports. The genealogy engine requires specific transaction sequencing that might not be preserved in your import file. Check if your import included the MaterialConsumptionTransaction records with proper timestamps - the genealogy builder uses these to establish parent-child relationships.

3

Good catch Mike - I just checked and you’re absolutely right. Our legacy system used format “LOT-YYYY-####” but the import mapping converted it to “LOT-YYYYMMDD-###” for the batch records. The material consumption transactions still reference the old format. So the links are technically there but the ID mismatch prevents the genealogy engine from connecting them. How did you repair this? Did you have to re-import or is there a way to update the references in place?

4

We had a similar issue last year. The problem was that our batch IDs had leading zeros in the legacy system but the import stripped them during conversion. So batch “B-001234” became “B-1234” and the material consumption records still referenced the old format. The genealogy engine couldn’t match them. Did you verify that your Batch ID format is 100% consistent between the batch master records and the material transaction records? Even small formatting differences will break the links. Also check if any special characters got converted during import.

5

The built-in repair utility is good but won’t fix the root cause if your batch IDs are inconsistent. Here’s what worked for us in a similar situation.

First, run this validation query to confirm the ID mismatch pattern:

SELECT DISTINCT b.BatchNumber, mct.SourceBatchID
FROM Batch b
LEFT JOIN MaterialConsumptionTransaction mct
  ON b.BatchID = mct.ConsumingBatchID
WHERE mct.SourceBatchID IS NULL

If you see records where BatchNumber exists but SourceBatchID is null, that confirms the linking problem.

For the repair approach, you need to address all three focus areas systematically:

1. Import Mapping Validation: Create a mapping table that translates old format to new format. This becomes your reference for all corrections. Export your current batch master and material transactions to CSV, then build a lookup table showing old ID → new ID for every affected batch.

2. Batch ID Consistency: You have two options:

  • Option A: Update the batch master records to use the legacy ID format (simpler if legacy format meets your current standards)
  • Option B: Update all material consumption transaction references to use the new format (better if new format is your standard going forward)

We chose Option B. Created a stored procedure that updated MaterialConsumptionTransaction.SourceBatchID using our mapping table:

UPDATE MaterialConsumptionTransaction
SET SourceBatchID = mapping.NewBatchID
FROM BatchIDMapping mapping
WHERE MaterialConsumptionTransaction.SourceBatchID = mapping.OldBatchID

3. Genealogy Data Repair: After fixing the ID consistency, run the Genealogy Rebuild utility:

  • Go to System Administration → Data Management → Genealogy Rebuild
  • Select date range covering your imported batches
  • Choose “Rebuild parent-child relationships” option
  • Run in validation mode first to preview changes
  • Execute the rebuild (this can take 2-4 hours for 6 months of data)

The rebuild utility will reprocess all material consumption transactions in chronological order and reconstruct the genealogy tree based on the corrected batch ID references.

Critical steps:

  1. Back up your database before any updates
  2. Test the entire process in a development environment first
  3. Document your batch ID mapping table - you’ll need it for future reference
  4. After rebuild, spot-check 20-30 batches across different time periods to verify the genealogy links are correct
  5. Run the standard genealogy validation report to confirm no orphaned nodes remain

One gotcha we encountered: if any of your imported batches have status=“Archived”, the genealogy rebuild skips them. You need to temporarily change status to “Complete”, run the rebuild, then change back to “Archived”.

This approach fixed our 3,800+ orphaned batch records. The key is fixing the data consistency issue first, then letting the built-in tools do the genealogy reconstruction. Don’t try to manually recreate the parent-child links - the genealogy engine needs to build them from the transaction history to maintain proper audit trails.

6

We wrote a data correction script that ran through the MaterialConsumptionTransaction table and updated the batch ID references to match the new format. You’ll need database access and careful testing in a non-production environment first. The key is to maintain the original transaction timestamps and sequence numbers so the genealogy rebuild happens in the correct order.