i am trying to add Sales Invoice Item child table’s column
here
self.select_columns = “”"
name as invoice_number,
customer_name,
posting_date,
base_grand_total,
base_rounded_total,
customer_gstin,
place_of_supply,
the error says “unknown column: total_disc”. So probably you have selected “total_disc”, which is not a valid field. If you have added the field, check for typos. Otherwise, maybe you want to see “margin_rate_or_amount”?
you cannot directly use child tables columns, because the child doctype is a seperate table than its parent.
How are the child doctype rows and the parent document linked ?
each child table row, has a field called parent whose value matches the sales invoice number which is the value name. Join these two tables on these values, then you can use the matched column. Try writing a query and we’ll help you out.
i am using this query and invoice_id_list[i] has a invoice number
self.Sales_invoice_item = frappe.db.sql("""
select
parent, (sum(price_list_rate)-sum(rate)) as total_disc
from `tabSales Invoice Item`
where
parent in (%s)
""",(invoice_id_list[i]), as_dict=1)
self.Sales_invoice_item1 = frappe.db.sql("""
select
parent,item_name,qty
from `tabSales Invoice Item`
where
parent in (%s)
""",(invoice_id_list[i]), as_dict=1)