Multi-Level Child Tables

← Frappe Cookbook

Problem: Model a document whose child tables themselves have child-like data — a ScoopJoy Franchise Agreement with Clauses, where each Clause has its own Sub-conditions. Frappe does not support nesting one child table inside another.

Solution: Put two child tables on the parent DocType and connect them with a linking field (clause_id). Each sub-condition row stores the clause_id of the clause it belongs to, so the two flat tables behave like a parent-child pair within the same document.

Step 1: Clause child table

The Clause table is a standard child table (istable: 1). Its clause_id is the key other rows will point at, so it is read_only — the controller fills it in.

scoopjoy/scoopjoy/doctype/sj_agreement_clause/sj_agreement_clause.json

{
  "name": "SJ Agreement Clause",
  "module": "ScoopJoy",
  "istable": 1,
  "fields": [
    {
      "fieldname": "clause_id",
      "fieldtype": "Data",
      "label": "Clause ID",
      "read_only": 1,
      "in_list_view": 1,
      "columns": 1
    },
    {
      "fieldname": "clause_title",
      "fieldtype": "Data",
      "label": "Clause Title",
      "reqd": 1,
      "in_list_view": 1,
      "columns": 3
    },
    {
      "fieldname": "clause_text",
      "fieldtype": "Text Editor",
      "label": "Clause Text",
      "reqd": 1
    },
    {
      "fieldname": "is_mandatory",
      "fieldtype": "Check",
      "label": "Is Mandatory",
      "default": "1",
      "in_list_view": 1,
      "columns": 1
    },
    {
      "fieldname": "penalty_amount",
      "fieldtype": "Currency",
      "label": "Penalty Amount",
      "in_list_view": 1,
      "columns": 2
    }
  ]
}

Step 2: Sub-condition child table

The Sub-condition table carries the same clause_id field — here it is a required foreign key pointing back to a clause row in the same document.

scoopjoy/scoopjoy/doctype/sj_clause_subcondition/sj_clause_subcondition.json

{
  "name": "SJ Clause Subcondition",
  "module": "ScoopJoy",
  "istable": 1,
  "fields": [
    {
      "fieldname": "clause_id",
      "fieldtype": "Data",
      "label": "Parent Clause ID",
      "reqd": 1,
      "in_list_view": 1,
      "columns": 2
    },
    {
      "fieldname": "subcondition_text",
      "fieldtype": "Small Text",
      "label": "Sub-condition",
      "reqd": 1,
      "in_list_view": 1,
      "columns": 5
    },
    {
      "fieldname": "compliance_type",
      "fieldtype": "Select",
      "label": "Compliance Type",
      "options": "\nMust Do\nMust Not Do\nRecommended",
      "in_list_view": 1,
      "columns": 2
    },
    {
      "fieldname": "review_frequency",
      "fieldtype": "Select",
      "label": "Review Frequency",
      "options": "\nMonthly\nQuarterly\nAnnually",
      "in_list_view": 1,
      "columns": 1
    }
  ]
}

Step 3: Parent DocType (Franchise Agreement)

The parent holds both tables side by side — clauses (options SJ Agreement Clause) and subconditions (options SJ Clause Subcondition) — under their own section breaks. Only the relevant fields are shown; add standard fields as needed.

scoopjoy/scoopjoy/doctype/sj_franchise_agreement/sj_franchise_agreement.json

{
  "name": "SJ Franchise Agreement",
  "module": "ScoopJoy",
  "naming_rule": "Expression",
  "autoname": "SJ-FA-.YYYY.-.#####",
  "fields": [
    {
      "fieldname": "franchise_outlet",
      "fieldtype": "Link",
      "label": "Franchise Outlet",
      "options": "SJ Franchise Outlet",
      "reqd": 1
    },
    {
      "fieldname": "agreement_date",
      "fieldtype": "Date",
      "label": "Agreement Date",
      "reqd": 1,
      "default": "Today"
    },
    {
      "fieldname": "valid_until",
      "fieldtype": "Date",
      "label": "Valid Until",
      "reqd": 1
    },
    {
      "fieldname": "section_clauses",
      "fieldtype": "Section Break",
      "label": "Clauses"
    },
    {
      "fieldname": "clauses",
      "fieldtype": "Table",
      "label": "Agreement Clauses",
      "options": "SJ Agreement Clause"
    },
    {
      "fieldname": "section_subconditions",
      "fieldtype": "Section Break",
      "label": "Sub-conditions"
    },
    {
      "fieldname": "subconditions",
      "fieldtype": "Table",
      "label": "Clause Sub-conditions",
      "options": "SJ Clause Subcondition"
    }
  ]
}

Step 4: Controller — linking and validation

assign_clause_ids stamps each clause row with a stable ID like CL-001. validate_subcondition_references then guarantees every sub-condition points at a clause that actually exists in this document — this is the referential integrity that a real nested table would give you for free. The get_full_structure helper reassembles the two flat tables into the nested shape an API consumer expects.

scoopjoy/scoopjoy/doctype/sj_franchise_agreement/sj_franchise_agreement.py

import frappe
from frappe import _
from frappe.model.document import Document


class SJFranchiseAgreement(Document):
    def validate(self):
        self.assign_clause_ids()
        self.validate_subcondition_references()
        self.compute_total_penalty()

    def assign_clause_ids(self):
        """Auto-assign a stable clause_id to each clause row."""
        for idx, clause in enumerate(self.clauses, start=1):
            if not clause.clause_id:
                clause.clause_id = f"CL-{idx:03d}"

    def validate_subcondition_references(self):
        """Ensure every subcondition points to a valid clause_id in the same document."""
        valid_ids = {c.clause_id for c in self.clauses if c.clause_id}
        for sc in self.subconditions:
            if sc.clause_id not in valid_ids:
                frappe.throw(
                    _("Sub-condition row {0}: Clause ID '{1}' does not exist in the Clauses table.").format(
                        sc.idx, sc.clause_id
                    )
                )

    def compute_total_penalty(self):
        """Sum penalty amounts across all mandatory clauses."""
        self.total_penalty = sum(
            c.penalty_amount or 0 for c in self.clauses if c.is_mandatory
        )

    def get_clause_with_subconditions(self, clause_id):
        """Return a clause dict with its nested subconditions."""
        clause = next((c for c in self.clauses if c.clause_id == clause_id), None)
        if not clause:
            frappe.throw(_("Clause {0} not found").format(clause_id))

        subs = [sc.as_dict() for sc in self.subconditions if sc.clause_id == clause_id]
        result = clause.as_dict()
        result["subconditions"] = subs
        return result

    def get_full_structure(self):
        """Return the entire agreement as a nested dict for API consumers."""
        structure = []
        for clause in self.clauses:
            c = clause.as_dict()
            c["subconditions"] = [
                sc.as_dict() for sc in self.subconditions if sc.clause_id == clause.clause_id
            ]
            structure.append(c)
        return structure

Step 5: Client-side — auto-fill the Clause ID

On the client, clauses_add stamps a provisional CL-NNN ID as soon as a clause row is added, so the dropdown on the sub-conditions grid can offer real clause IDs to pick from while the user is still editing.

scoopjoy/scoopjoy/doctype/sj_franchise_agreement/sj_franchise_agreement.js

frappe.ui.form.on("SJ Franchise Agreement", {
    refresh(frm) {
        frm.fields_dict.subconditions.grid.get_field("clause_id").get_query = function () {
            const clause_ids = (frm.doc.clauses || [])
                .filter((c) => c.clause_id)
                .map((c) => c.clause_id);
            return {
                // clause_id is a Data field, so we use a custom query
                // This sets it as a read-only select-like behavior
                filters: {},
            };
        };
        // Populate clause_id options as a virtual select
        frm.set_query("clause_id", "subconditions", function () {
            return {};
        });
    },

    clauses_add(frm, cdt, cdn) {
        const row = locals[cdt][cdn];
        const idx = frm.doc.clauses.length;
        row.clause_id = `CL-${String(idx).padStart(3, "0")}`;
        frm.refresh_field("clauses");
    },
});

frappe.ui.form.on("SJ Clause Subcondition", {
    before_subconditions_remove(frm, cdt, cdn) {
        // Nothing special needed, but hook is available for cleanup
    },
});

Why this works

The clause_id field acts as a foreign key within the same document, linking rows in the subconditions child table to rows in the clauses child table. The validate() hook enforces referential integrity before save, so you never persist a sub-condition that orphans itself from its clause.

Common mistake

Using idx (the row index) as the linking field. Row indices change when rows are reordered or deleted, silently breaking the links. Always use a stable, auto-generated ID like CL-001.