How to add new column in gstr-1 report

ERPNext Reports
1

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,

		company_gstin,
		customer_address,
		reverse_charge,
		invoice_type,
		return_against,
		is_return,
		invoice_type,
		export_type,
		port_code,
		shipping_bill_number,
		shipping_bill_date,
		reason_for_issuing_document,
		grand_total
		
		
	"""

when ever i am adding the child tables column then its giving error

gstr_1
gstr_11366×768 174 KB

i don’t know how to add here please help me

2

Hi @eliyaskhan18,

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”?

Hope this helps :wink:

3

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.

4

hi @lasalesi

Thankyou for reply

5

hi @root13F

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)
7

no need of using self here. self is used when a Class is involved.

Eliyas, also do you wish to calculate total discount for each invoice, or each item ? because first query needs probably a group by statement.

Also I think your two queries can be merged into one like this :

And I think no need of having a seperate list of invoice when you are going to evaluate all submitted invoices.

sales_invoice_item = frappe.db.sql("""SELECT
         parent,item_code,
         item_name,qty,
         (price_list_rate - rate) as total_disc
FROM
         `tabSales Invoice Item`
          INNER JOIN
`tabSales Invoice` ON parent = name
WHERE
`tabSales Invoice`.docstatus = 1""")

Does this query work for you ?