Proper way to link a DocType to multiple other documents of Different DocTypes?

Frappe Framework
1

I have a custom app with a DocType called Signature Request. A Signature Request can be linked to multiple different DocTypes (Contract, Purchase Order, etc). Each Contract/Purchase Order/Whatever can have mulitple Signature Requests.

I’d like to create a Table field in the Contract/Purchase Order DocTypes to display the Signature Requests, however when I go to a Contract that I’ve linked to a Signature Request, I get the error message pymysql.err.OperationalError: (1054, “Unknown column ‘parent’ in ‘where clause’”)

2

Make sure the field name in the table link is correct.

Share a screenshot of the process so the community can assist you.

If you need to link multiple Signature Requests in a purchase order, you can use a table multiselect field or a table for it.

Thank You!

3

I’m not sure what you mean by Make sure the field name in the table link is correct.

To make testing this easier, I made a whitelisted function to create the child DocType and add a Table field to Contract.

def create_contract_signature_requests():
    # Check if the Child DocType "Contract GS Requests" already exists
    contract_signature_requests_dt = frappe.get_all("DocType", filters={"name": "Contract GS Requests"}, limit=1)

    if not contract_signature_requests_dt:
        # Define the DocType structure as a dictionary
        doc_type_definition = {
            "doctype": "DocType",
            "module": "Get Signatures",
            "name": "Contract GS Requests",
            "ischild": 1,
            "istable" : 1,
            "fields": [
                {
                    "label": "Signature Request",
                    "fieldname": "signature_request",
                    "fieldtype": "Link",
                    "options": "Signature Request",
                    "ischild": 1,
                    "in_list_view": 1
                }
                # Add more fields as needed
            ]
        }

        # Create a new DocType object using frappe.get_doc
        doc_type = frappe.get_doc(doc_type_definition)

        # Insert the DocType into the database
        doc_type.insert()

    # Check if the "Contract" DocType exists
    contract_dt = frappe.get_meta("Contract")
    
    # Check if the field already exists to avoid duplicates
    if not contract_dt.get_field("gs_requests"):
        # Define the field properties
        field_properties = {
            "label": "GS Requests",
            "fieldname": "gs_requests",
            "fieldtype": "Table",
            "options": "Contract GS Requests",
            "insert_after": "",
            "in_list_view": 0  # Set in_list_view to 1 to make this field visible in list view
            # Add more field properties as needed
        }

        # Append the new field to the "Contract" DocType
        contract_dt.append("fields", field_properties)
        contract_dt.save()

        # Update the database schema
        frappe.db.commit()
        frappe.msgprint("Field 'GS Requests' added to the 'Contract' DocType successfully")

    else:
        frappe.msgprint("Field 'GS Requests' already exists in the 'Contract' DocType")
4

Hi @oguruma,

when you create a child table to link to another DocType, you need to ensure that the link field in the child table points to the correct parent DocType. In this case, your child table “Contract GS Requests” should have a link field named “parent” that links to the “Contract” DocType.

try it.

doc_type_definition = {
    "doctype": "DocType",
    "module": "Get Signatures",
    "name": "Contract GS Requests",
    "ischild": 1,
    "istable": 1,
    "fields": [
        {
            "label": "Signature Request",
            "fieldname": "signature_request",
            "fieldtype": "Link",
            "options": "Signature Request",
            "ischild": 1,
            "in_list_view": 1
        },
        {
            "fieldname": "parent",
            "fieldtype": "Link",
            "options": "Contract",
            "hidden": 1  # Hide the parent field
        }
        # Add more fields as needed
    ]
}

Thank You!