Building Lineage Trees with Self-Referencing Lookups

Overview

When records are cloned, split, or versioned over multiple generations, organizations need to trace both the complete ancestry ("where did this originally come from?") and the immediate provenance ("what was this created directly from?"). This article presents a dual self-referencing lookup pattern that answers both questions without recursive queries.

• Why it matters: A single parent reference cannot distinguish between the original genesis record and the immediate source of a clone. Without this distinction, reporting across record families requires complex recursive logic that Salesforce SOQL does not natively support.
• What you will learn: How to design Original_Record__c and Direct_Parent__c fields for complete lineage tracking, the propagation rules that maintain consistency at any depth, and efficient SOQL patterns for querying entire lineage trees in a single query.

When records are cloned, split, or versioned, organizations need to answer two distinct questions: "Where did this originally come from?" and "What was this created directly from?" A single parent reference cannot answer both. This pattern uses two self-referencing lookup fields to build complete lineage trees.

The Two-Field Model

Field	Points To	Purpose
Original_Record__c	The root/genesis record	Enables reporting across all generations
Direct_Parent__c	The immediate source of the clone/split	Provides context for what triggered the creation

Consider a bid package that goes through multiple rounds of splitting:

Bid Package A (genesis)
  |-- Bid Package B (split from A)
  |     |-- Bid Package D (split from B)
  |     |-- Bid Package E (split from B)
  |-- Bid Package C (split from A)

The field values for each record:

Record	Original_Record__c	Direct_Parent__c
A	null (it is the root)	null (no parent)
B	A	A
C	A	A
D	A	B
E	A	B

Why One Field Is Not Enough

With only Direct_Parent__c, finding all descendants of Record A requires recursive queries -- traversing B to find D and E, then checking if D or E have children, and so on. Salesforce SOQL does not support recursive queries, making this impractical beyond two levels.

With only Original_Record__c, you can find all records in the family tree instantly, but you cannot determine the structure. You cannot tell whether D was split from A or from B.

Together, the two fields provide both the flat "all descendants" query and the structural "parent-child" relationship.

Propagation Logic During Clone/Split

When cloning or splitting a record, the propagation rules are simple and consistent:

public class LineageService {

    public static void setLineage(SObject newRecord, SObject sourceRecord) {
        // Original always points to the root
        // If the source has an Original, carry it forward
        // If the source IS the original, point to the source
        Id originalId = (Id) sourceRecord.get('Original_Record__c');
        if (originalId == null) {
            originalId = sourceRecord.Id;
        }
        newRecord.put('Original_Record__c', originalId);

        // Direct Parent always points to the immediate source
        newRecord.put('Direct_Parent__c', sourceRecord.Id);
    }

    public static Bid_Package__c cloneBidPackage(Id sourceBidPackageId) {
        Bid_Package__c source = [
            SELECT Id, Name, Original_Record__c, Direct_Parent__c,
                   Status__c, Description__c
            FROM Bid_Package__c
            WHERE Id = :sourceBidPackageId
        ];

        Bid_Package__c clone = source.clone(false, true, false, false);
        setLineage(clone, source);
        clone.Status__c = 'Draft';

        insert clone;
        return clone;
    }
}

The rule is invariant regardless of depth: Original_Record__c always carries the root's ID forward, and Direct_Parent__c always points to the record that was the source of the operation.

SOQL Patterns for Querying Lineage

All Descendants of a Root

Find every record that traces back to a specific genesis record:

SELECT Id, Name, Direct_Parent__c, CreatedDate
FROM Bid_Package__c
WHERE Original_Record__c = :rootBidPackageId
ORDER BY CreatedDate ASC

This returns the entire family tree in a single, non-recursive query.

Immediate Children of a Specific Record

Find records that were directly cloned or split from a given record:

SELECT Id, Name, CreatedDate
FROM Bid_Package__c
WHERE Direct_Parent__c = :parentBidPackageId
ORDER BY CreatedDate ASC

The Complete Lineage Tree (Root + All Descendants)

Include the root record itself alongside its descendants:

SELECT Id, Name, Original_Record__c, Direct_Parent__c, CreatedDate
FROM Bid_Package__c
WHERE Id = :rootBidPackageId
   OR Original_Record__c = :rootBidPackageId
ORDER BY CreatedDate ASC

Building a Tree Structure in Apex

To reconstruct the hierarchy programmatically:

public static Map<Id, List<Bid_Package__c>> buildChildMap(Id rootId) {
    List<Bid_Package__c> allRecords = [
        SELECT Id, Name, Direct_Parent__c
        FROM Bid_Package__c
        WHERE Original_Record__c = :rootId
    ];

    Map<Id, List<Bid_Package__c>> childMap = new Map<Id, List<Bid_Package__c>>();

    // Initialize the root
    childMap.put(rootId, new List<Bid_Package__c>());

    for (Bid_Package__c bp : allRecords) {
        Id parentId = bp.Direct_Parent__c;
        if (!childMap.containsKey(parentId)) {
            childMap.put(parentId, new List<Bid_Package__c>());
        }
        childMap.get(parentId).add(bp);
    }

    return childMap;
}

Practical Use Cases

• Bid package splits: Track which original request spawned derivative packages across multiple negotiation rounds
• Quote versioning: Maintain a chain from the original quote through each revision, while always being able to pull the full history
• Multi-tiered approvals: Track approval chains where each approval stage generates a new record derived from the previous stage
• Contract amendments: Link amended contracts back to the original agreement while preserving the amendment-of-amendment chain

Key Takeaway

The dual self-referencing lookup pattern -- Original_Record__c for the root and Direct_Parent__c for the immediate source -- provides O(1) query access to both the full lineage tree and the direct parent-child relationship. It avoids recursive queries, works within Salesforce's SOQL constraints, and scales to arbitrary depth without additional fields or objects.