Building Lineage Trees with Self-Referencing Lookups

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.