General Ledger Report with custom field

Need to add custom field on general Ledger Report ?
I added custom filed called “description” in document type (Journal Entry Account)
and I need to show this filed in General Ledger Report.
in general_ledger.py file

modify the following list by add last member (from line 90 to 96]
columns += [
_(“Voucher Type”) + “::120”, (“Voucher No”) + “:Dynamic Link/”+(“Voucher Type”)+":160",
_(“Against Account”) + “::120”, _(“Party Type”) + “::80”, _(“Party”) + “::150”,
_(“Project”) + “:Link/Project:100”, _(“Cost Center”) + “:Link/Cost Center:100”,
_(“Against Voucher Type”) + “::120”, (“Against Voucher”) + “:Dynamic Link/”+(“Against Voucher Type”)+":160",
(“Remarks”) + “::400” ,(“Description”) + “::400”

]

then in get_result_as_list function i amend it to get discription as the following :
def get_result_as_list(data, filters):
result = []
for d in data:
row = [d.get(“posting_date”), d.get(“account”), d.get(“debit”), d.get(“credit”)]

	if filters.get("show_in_account_currency"):
		row += [d.get("debit_in_account_currency"), d.get("credit_in_account_currency")]

	row += [d.get("voucher_type"), d.get("voucher_no"), d.get("against"),
		d.get("party_type"), d.get("party"), d.get("project"), d.get("cost_center"), d.get("against_voucher_type"), d.get("against_voucher"), d.get("remarks"), d.get("description")
	]

	result.append(row)

the filed appeared but without data.

Data is obtained by the following query:
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party,
sum(debit) as debit, sum(credit) as credit,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher,
remarks , against, is_opening {select_fields}
from tabGL Entry
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, account"""
.format(select_fields=select_fields, conditions=get_conditions(filters),
group_by_condition=group_by_condition), filters, as_dict=1)

To get data I noted that party_type and party fileds belong to “Journal Entry Account” document type so I ammend the query to include description after “party”
as the following :
gl_entries = frappe.db.sql("""
select
posting_date, account, party_type, party, description,
sum(debit) as debit, sum(credit) as credit,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher,
remarks , against, is_opening {select_fields}
from tabGL Entry
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, account"""
.format(select_fields=select_fields, conditions=get_conditions(filters),
group_by_condition=group_by_condition), filters, as_dict=1)

an error occured

Traceback (most recent call last):

File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 55, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 40, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 897, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 93, in run
res = frappe.get_attr(method_name)(frappe._dict(filters))
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/general_ledger/general_ledger.py”, line 23, in execute
res = get_result(filters, account_details)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/general_ledger/general_ledger.py”, line 101, in get_result
gl_entries = get_gl_entries(filters)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/general_ledger/general_ledger.py”, line 129, in get_gl_entries
group_by_condition=group_by_condition), filters, as_dict=1)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 137, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘description’ in ‘field list’”)

1 Like

The error shows that the column ‘description’ is not there in the table ‘tabGL Entry’. The GL Entry is created when journal entry is created by the user. You will need to make changes in the code in general_ledger.py file to assign the value of ‘description’ to GL Entry from Journal Entry. Take a look at “make_entry” method in general_ledger.

Hi Kxt5258,
I did not find fields name in make_entry method, how do I add my custom field (description) in it?

regards,
–Ashfaq

You will need to add a custom field “description” in GL Entry too. Then, when creating GLs from Journal Entry, you need to assign description from Journal Entry Account to GL in “make_entry” method in the file general_ledger.py. Basically, you have description in Journal Entry Account but you are looking for description in GL Entry.